Running queries on data returned from OleDbDataAdapter 4

[Guggly]

Hi! I'm new to ADO.Net and am using VB .Net to generate a report based on an Access database. Using code (not controls) I can connect to the database fine and pull out data as I need to, but since I've got to calculate data from various tables, I'm wondering what is the best way to run multiple SQL Select statements on the data. Right now I get the data as follows:

objConn.Open()
Dim objAdapter As New OleDbDataAdapter("SELECT * FROM tblMain", objConn)
Dim objDataSet As New DataSet

objAdapter.Fill(objDataSet)

This gives me the contents of "tblMain" in objDataSet, which is good and what I need for some things. But what if I want to query specific parts of what's in objDataSet, or what if I want to access a different table?
Do I need to close objAdapter, open it again and then fill objData set for each new SQL query I run? It seems to me it would be pretty slow to do this multiple times, and there must be some way to work with the data that's there already (from "SELECT * FROM tblMain"), but maybe there isn't. Any advice?

Please note, I don't want to use any data access controls. I can connect to the data fine like this, I'm just not sure that opening and closing the DataAdapter (or having a whole bunch of them) would be the most efficient way to run multiple queries.

Thanks! -- Mike

 

[Korach]

You can use one data set to several tables, but I think you need to set the query of the adapter each time.
I use a dll that do it all for me and I only creates the connection and call the function ExecuteDataSet.

You can download it from:

http://www.microsoft.com/downloads/...familyid=76fe2b16-3271-42c2-b138-2891102590ad

If they moved it search for: "Data Access Application Block".

This includes the source code. Open the solution and compile it.

 

[Guggly]

Okay, if requerying the data adapter is the best way to do it as described above, then that's what I'll do.

By the way, how do you fill a data set from more than one table. I know it's possible, like you said, but I've never really needed to do so. Do you use an SQL command for that? If I had two tables, "tblMain" and "tblSecondary" how could I get them both into one data set?

Thanks for the "Data Access Application Block" info, the link is still valid and it looks interesting. This is only for SQL Server however, does anyone know of something similar for MSAccess (.mdb) data?

Thanks! -- Mike

 

[chrissie1]

you can query a dataset for example by using .select.

dim ds as new dataset
ds.Tables(0).Select("field = 'me'")

or by using a dataview

dim dsdataview as new dataview
dsDataview.Table = ds
dsDataview.RowFilter = "field = 'me'"
dsDataview.Sort = "field"

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Guggly]

Thanks chrissie1! A filter can be useful, but what if I want to do something more than filter certain values, for instance return the sum of a colum full of numbers:

"SELECT Sum(tblMain.Value1) AS TotalValue1 FROM tblStuffingData;"

Is there anyway I can do this on an existing dataset, or would I need to make a new one like korach is saying?

Thanks! -- Mike

 

[RiverGuy]

For that example, why not just loop through the DataTable?

Dim MyDouble As Double = 0
Dim r As DataRow
Dim dt As DataTable = MyDataSet.Tables("tblMain")

For Each r In dt.Rows
     MyDouble += r.Item("Value1")
Next

MsgBox(MyDouble)

 

[Korach]

As far as I know, the filter for data table is like "Where" in sql. You can't perform sql statement on a data table since you don't have sql engine in .NET.
About holding multiple tables in one dataset, the dataset can be a mirror of your db, and hold several tables with constraints and more. In dataset you have a property called Tables, that refers to a tables collection. If you use Fill on a dataset several times, it doesn't erase the table on each fill, but insert it to the collection.
You can also specify a table name in the fill method and after that to write ds.Tables("MyTable").
That's true that data access application block is for sql server, but you can learn from reading its code, or even change it to work with oledb. Just change the Sql objects to oledb objects, and you will save a lot of code.

 

[Guggly]

Thanks korach, I didn't realize that using Fill wouldn't overwrite the contents of a dataset. That's good to know! Yes, I'll check out the Data Access Application Block, and I'm sure it will be a help.

RiverGuy, I'll run some tests, but I'm pretty sure that it's faster to use an SQL statement than to loop through records like that, especially if there's a lot of records. At least this was the case with VB6 and ADO when I tested last, but perhaps things have changed with .Net.
That aside, there are some things which are certainly faster and less complex using SQL, such as GROUP BY ordering and the likes, which would require a lot more work to implement through VB code.

 

[RiverGuy]

It may have changed because you are using disconnected data in .Net all of the time now except when using .ExecuteReader(). You won't be using an open cursor with your DataTables.

I believe that some of this can be attributed to the limitations of Access. For example, in SQL Server, you could create a stored procedure that would return multiple resultsets with both detail and aggregated data. You could fill multiple tables from one Command object.

And you wouldn't have to worry about sending a new SQL statement to aggregatge data that could have changed since you retrieved your detail information--if the sproc was constructed correctly.

 

[Guggly]

RiverGuy, I think you might be right. Looping through the records to gather data is very fast, at least it is for simple things such as summing a column.

I'm still pretty new to the .Net platform, and could do this project much quicker in VB6, but I figure I need to changeover sooner than later. Hopefully I won't run into too many snags .

BTW, is it just me, or has the site been having trouble all day? It seems that almost every-other time I try to load it, the site is down.

Thanks all! -- Mike

 

[Korach]

Ado.Net is faster than vb6, and the classes are much mre powerful.
The site did some problems to me lately.