Can multiple datasets be used with one SQL connection?

[ViperPit]

I am looking to convert a project from ADO to ADO.Net as the data connection method.
I decided to replace the ADO Recordsets with ADO.Net Datareaders, and went through the exercise of modifying the code.
Of course, when I went to run in, it died opening the second recordset because it said the SQL connection was already in use.
I went back and reread the documentation and found that only one DataReader can be used per SQL connection at a time.
Now this seems incredibly stupid to me. I have used ADO for years, and always have multiple nests of Recordset that are open based off data in another Recordset.
Although MS says in their documentaion that Datareaders are faaster than Datasets for simple data retrieval, it seem this object is basically worthless if you want to do nesting, unless you want to have a seperate SQL connection for each recordset, which also doesn' make any sense.

So, I am going to ask here before I bother trying to work with Datasets: Can you open multiple Datasets under one SQL connection at the same time?

I didn't find anything in the documentation indicating it couldn't b done, like with Datareaders, but I don't want to waste anymore time if I am going to discover it can't be done.
If MS has for some reason decided nesting and referencing one recordset from antoher is no longer used in programming, I guess I will have to stick with using the old ADO conneciton method.

 

[ThatRickGuy]

Yes, infact even if you create multiple connections with the same connection string ADO.Net will do something called "Connection Pooling" which will vastly improve your data connection performance.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ViperPit]

Everything I read is telling my Datareader have better performance than Datasets.
But I need to be able to perform nested loops, basing one recordset off another recordset.
Since this can not be done with mutliple datareaders using the same connection, would I be better to open a new connection for each datareader, or to stick with a single connection and use datasets?

And does anyone have a good resource for the BASICS of datsets.
I have been reading through the MS help, but it seems complicated and busy.
I just want a simple tutorial to do the very basic things:
1.) Perform a SELECT query and loop through the results set
2.) Perform action queries (INSERT, UPDATE, DELETE) against the SQL server.

 

[RiverGuy]

DataSets may contain DataRelations. This enables one to fill up multiple DataTables in a DataSet, define relations, and get the related data from memory. However, if you are pulling a very large number of rows, you may not want to use DataSets.

 

[ThatRickGuy]

If it's absolute performance you need on a series of independant record comparisons, then this is the thread for you: thread796-891072

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ViperPit]

RiverGuy:
DataSets may contain DataRelations. This enables one to fill up multiple DataTables in a DataSet, define relations, and get the related data from memory. However, if you are pulling a very large number of rows, you may not want to use DataSets.

The data I am dealing with is ENORMOUS!
Tables that contain BILLIONS of rows!
I was just doing some reading on Datasets, and if I am reading it correctly, you can't just bring back the results of a query into a single recordset, if the source is from multiple tables.
Is this right?!
Say I have a query:
"SELECT customers.last_name, taxes.tax_bracket, genders.gender_name
FROM customers, taxes, genders
WHERE customers.tax_id = taxes.tax_id
AND customers.gender_id = genders.gender_id

In ADO, I would open a recordset, and it would bring me back a single set of data conatining 3 columns and X numbers of rows.

If what I am reading about Datasets is correct, I would have to have 3 DataTables in my Dataset, one for each being used by the QUERY, and populate each of them.
Can that be right?

I am thouroughly confused with how ADO.Net handles all this.
I try not to jump to the conclusion that anything new sucks, and what I am used to is better, but so far Datareaders and Datasets in ADO.Net seem a lot more confusing and cumbersome that the old ADO recordsets.

All I want to do is open a recordset of data based on a query that contains multiple tables. Then loop through that recordset and for each row, open another recordset based on that data. Close the second recordset, move to the next record in the original recordset, until I am done, and then close the original recordset.

Can't seem to do this with Datareaders unless I have a new connection for each Datareader.
And I am really stumped on how to do this with Datasets, when it appears that you are creating a whole copy of each table in the dataset, rather than a subset based on your query.

 

[ViperPit]

Well, I think I figured out how to get the results of a SQL SELECT query into a dataset and then iterate through the result set.
Although it is a complete mess!
Below I will show the actual code I used in ADO and the new code in ADO.Net and if anyone can supply a better way to do it, I would appreciate the imput.

In both these scenarios I have a databse connection named "gDB" that I didn't bother copying the code in here for.

Using ADO:
***********************************************************
Dim MySQL As String
Dim adoRS As ADODB.Recordset

Set adoRS = New Recordset

MySQL = ""
MySQL = MySQL & " SELECT channel_id, pick_template, locked_by_server"
MySQL = MySQL & " FROM tbl_channel_bin_groups"
MySQL = MySQL & " WHERE pick_template <> 0"
MySQL = MySQL & " ORDER BY channel_id"

adoRS.Open MySQL, gDB, adOpenDynamic, adLockOptimistic

Do While Not adoRS.EOF
If adoRS.Fields("pick_template") <> 0 Then
'Do other code action here
End If

adoRS.MoveNext
Loop
adoRS.Close
***********************************************************

Same logic using ADO.Net Datasets
***********************************************************
Dim MySQL As String
Dim MyCommand As SqlCommand
Dim MyAdaptor As SqlDataAdapter
Dim MyDataset As DataSet
Dim i as Integer

MySQL = ""
MySQL = MySQL & " SELECT channel_id, pick_template, locked_by_server"
MySQL = MySQL & " FROM tbl_channel_bin_groups"
MySQL = MySQL & " WHERE pick_template <> 0"
MySQL = MySQL & " ORDER BY channel_id"

MyCommand = New SqlCommand(MySQL, gDBCon)
MyAdaptor = New SqlDataAdapter(MyCommand)
MyDataset = New DataSet
MyAdaptor.Fill(MyDataset)

For i = 0 to MyDataset.Tables(0).Rows.Count
If MyDataset.Tables(0).Rows(i).Item(0) <> 0 Then
'Do Other Code Action Here
End If
Next i
***********************************************************

I couldn't find an EOF equivalent, so I looped for the recordcount. Is there an EOF test?
I referenced the field I wanted by the Rows/Item number.
Is there a better way to loop through the recordset than using an index like I did with "i"?
Is there a way to reference the fiels by it's name, rather than the "Item" number?
It would make the code more readable to reference:
adoRS.Fields("pick_template")
instead of:
MyDataset.Tables(0).Rows(i).Item(0)

I know I would hate to go back later and try to figure out what the heck MyDataset.Tables(0).Rows(i).Item(0) is referring to!!

Thanks all.

 

[ThatRickGuy]

For i = 0 to MyDataset.Tables(0).Rows.Count [b]-1[/b]
  'Do Other Code Action Here
Next i

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ViperPit]

Thanks Rick.
I also found something that said it can be done like this:

Dim MyDataRow As DataRow
For Each MyDataRow In MyDataset.Tables("DataTable").Rows
'Do other code here
'And you can reference fields by their name here using:
MyDataRow("field_name")
Next MyDataRow

That answered both quesiton about EOF type testing and column names for readabliltiy.

Now as far as setting up dataadaptors and datasets, should I use a new adaptor for each dataset, or add the datsets as a new table under the same adaptor when doing these nested loopings?

 

[ThatRickGuy]

You should be able to use the same data adapter. Just make sure you change table names

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ViperPit]

Sounds good Rick.
I saw how to Dispose of individual datatables as well as the whole dataadaptor after you are done with them too.

My only other question now, is how do you see if another user has made changes to data, after your initial Fill of the dataset?
In old ADO, you would use adOpenDynamic to see changes made by other users as you move through the recordset.
How is this done wiht a dataset?

Say I do:
***********************************************************
MySQL = ""
MySQL = MySQL & " SELECT channel_id, pick_template, locked_by_server"
MySQL = MySQL & " FROM tbl_channel_bin_groups"
MySQL = MySQL & " WHERE pick_template <> 0"
MySQL = MySQL & " ORDER BY channel_id"

MyCommand = New SqlCommand(MySQL, gDBCon)
MyAdaptor = New SqlDataAdapter(MyCommand)
MyDataset = New DataSet
MyAdaptor.Fill(MyDataset)

For Each MyDataRow In MyDataset.Tables("DataTable").Rows
MsgBox(MyDataRow("pick_template"))
Next MyDataRow
***********************************************************

When I filled the dataset, I queried for rows where pick_template <> 0, however, as I step through the records, another user may have changed one of those rows after my initial fill.
I assume there is some way to "requery" the dataset before doing the "Next MyDataRow" statement to get the newest data.

Thanks

 

[ThatRickGuy]

My recommendation is to break away from the direct databinding. how often do users actually need to see 5000 records at a time? in most of those cases it's easier for the user to get a quick search functionality, then look at the one record they actually need. This way you don't have the same problems with update checking. If you want to be causious that way, then you just need to check for changes before saving the data.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ViperPit]

We have a bank of 20 servers that are constantly processing records. Tables that deal with billions of rows.
The servers work at the saem time to process batches for hundreds of client machines.
One server needs to know if another server has worked on a record while it was processing another.
So I have a client table with a flag that indicates processing, and field that indicates which server is currently working on it. When the flag gets sets for a bunch of clients, all the servers begin processing, each taking one client at a time.
I will give an example of the "locking code" below, to illustrate what we did using ADO.Net:
Each server is running this code.

MySQL = "SELECT client_id, process_flag, locked_by_server
FROM tbl_clients
WHERE process_flag <> 0
AND locked_by_server Is Null"

adoRS.Open MySQL, gDB, adOpenDynamic, adLockOptimistic
'The initial query checks for clients that currently have some process to perform based on the flag, and are not currently locked by another server (query is OpenDynamic to see changes by other users)

MySQL = "UPDATE tbl_clients
SET locked_by_server = " & varServerID
WHERE process_flag <> 0
AND locked_by_server Is Null"

MyRSTExec.Open MySQL, gDB, adOpenStatic, adLockOptimistic
'The update query sets the server_lock field for the current row, but only if the flag is still on, and another server hasn't already locked it

MySQL = "SELECT client_id
FROM tbl_clients
WHERE process_flag <> 0
AND locked_by_server = " & varServerID

MyRSTLocked.Open MySQL, gDB, adOpenStatic, adLockOptimistic
'Now we do another select to see if the update we performed went through and we are actually the server that got the lock

If Not MyRSTLocked.EOF Then
'Perform appropriate processing
End If
MyRSTLocked.Close
MyRST.MoveNext
Loop 'Go back to the next client in the original process

'Now, when I go back to the next record, I need to see if the process_id has been changed by another server
If my recordset was static, I would still see the process flag as <> 0, when in fact another server may have processed it and set it to 0

Since ADO.Net does not have the Dynamic option, I was trying to figure out if there is a way to update the dataset with the most current info from the server.
I saw the DataAdapter has an "Update" method, but that seems to be if you make changes to the data in the local DataSet, then you can commit those changes back to the server tables with with that method.
I saw the DataSet has an AcceptChanges method, but again that looks like changes locally get committed back to the server.
I see the DataSet and DataTable both have a method called "Reset", but there isn't much help on it, aside from saying "Resets the DataSet/Table to its original state". Not sure if that means it requerys the data from the server, or just turns off all the change flags and restores it locally from memory.
Last thing I noticed was that the Fill method of the DataAdaper says, "Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table"."
The "refreshes" keyword stood out to me. Does this mean that a subsequent call to Fill will requery the database and get any changes made on the server? If so, do I have to close it before issuing a subsequent Fill?

Thanks so much for your help. I am learning quickly, but this is a lot of new stuff to take in quickly.

 

[ViperPit]

I messed up something in the above post, and don't see a way to edit it, so here is the change...

Where it says:
************************************************************
MySQL = "UPDATE tbl_clients
SET locked_by_server = " & varServerID
WHERE process_flag <> 0
AND locked_by_server Is Null"

MyRSTExec.Open MySQL, gDB, adOpenStatic, adLockOptimistic
'The update query sets the server_lock field for the current row, but only if the flag is still on, and another server hasn't already locked it

MySQL = "SELECT client_id
FROM tbl_clients
WHERE process_flag <> 0
AND locked_by_server = " & varServerID
***********************************************************

It should say:
***********************************************************
MySQL = "UPDATE tbl_clients
SET locked_by_server = " & varServerID
WHERE client_id = " & adoRS.Fields("client_id")
AND process_flag <> 0
AND locked_by_server Is Null"

MyRSTExec.Open MySQL, gDB, adOpenStatic, adLockOptimistic
'The update query sets the server_lock field for the current row, but only if the flag is still on, and another server hasn't already locked it

MySQL = "SELECT client_id
FROM tbl_clients
WHERE client_id = " & adoRS.Fields("client_id")
AND process_flag <> 0
AND locked_by_server = " & varServerID
***********************************************************

I left out the criteria to link the client_id from the original recordset in the queries that attempt to lock the record for the server.

 

[chrissie1]

If so, do I have to close it before issuing a subsequent Fill?

nope but you will have to clear the records that are already there via .clear of the datatable in question

reset does not get the new data it just undoes the made changes.

ADO.net is an unlinked (or whatever you want to call it) kind of datathingie. Wich means that you get the data. and change them locally without changing the real data untill you commit the changes. Unlike ADO wich is linked directly to the data.

So it's more of an optimistic kind of locking you do, no more pessimisme in ADO.NET. If this is a good thing for you I don't know. But it will take some rethinking.


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

 

[ThatRickGuy]

ADO.net is an unlinked (or whatever you want to call it) kind of datathingie.

That would show up as "Disconnected Data Source" on your resume'

Datathingie... Some times Chrissie, heh.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

WHAT. I do my best with the brains I was given. All two cells of them.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ThatRickGuy]

I have a feeling that if a doc ever took a peek in your head, they'd see a bunch of Umpa-Loompa's drinking it up at a beer garden.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

At least they would have fun.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]