SqlDataReader vs. DataSet

  • Thread starter Thread starter lakshmi
  • Start date Start date
L

lakshmi

Hi all,
My requirement is I need to have three different
recordsets open at the same time. Traversing through the 3
recordsets, I would check on the data and based on some
rules, return an object from my function.

Will it be efficient to use a SQLDataReader or a DataSet
for this situation? I understand from the documentation
that there can be only one SQLDataReader associated with a
connection open at a time. So, it seems like I can't have
3 SqlDataReaders open at the same time for my connection.
is SqlDataReader usable for these type of conditions
(unless I combine my 3 recordsets into a single recordset
by some means?)

Or can a DataSet be used? But I wouldn't be doing any
updates or deletes to any of those recordsets? Is it
useful to use a dataset at all?

I'm a beginner in C# and ADO.NET. Thanks for all your help.
 
Hi,

From the information you've given the DataSet sounds like the best choice.

The DataReader is like the forward-only/read-only Recordset in Classic ADO,
it is meant to be a "firehose". You get the data, you traverse it once, and
then you close it. It's great for filling a UI control like a listbox or
getting the data to be stored in custom business objects.

The DataSet is an in-memory database. You can get multiple resultsets (even
from different databases) and store them as DataTables inside the DataSet.

I hope this is enough to get you started. There are lots of resources online
that will give you more information on ADO.NET.
 
There are two options here, one of which works. The preferable one is to
open three different connections (which will be pooled by ADO.NET) and
associate a dataReader with each one. I doubt that will work, and I'm too
lazy to test it. The other option is to fill a DataTable for each of your
different recordsets, like this:

void somefunc() {
DataTable t1 = new DataTable();
DataTable t2 = new DataTable();
DataTable t3 = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM TABLE1",
someConnection);
da.Fill(t1);
da.SelectCommand.CommandText = "SELECT * FROM TABLE2";
da.Fill(t2);
da.SelectCommand.CommandText = "SELECT * FROM TABLE3";
da.Fill(t3);
}

Note that there's no need to use a DataSet here. A DataSet is more like a
collection of tables and rules relating those tables. Personally, I'd much
rather do my relations manually than mess with the crummy design support for
strongly typed DataKlotches. Sometimes (read usually) DataSets really just
aren't worth it.

Chris
 
You can have three connections which would work. You can only traverse
forward with a DataReader. On the other hand, it sounds like you probably
want to sit all three queries in DataTables in a DataSet and use a
DataRelation to relate them if applicable.

Record size is also a big consderation. If you are talking about 1000
records, you probably will hardly be able to notice the performance
difference on most machiens. However, this depends on Table szie. but the
larger your query, the more pronounced the performance difference. Just
yesterday I was benchmarking queries and for pull 186,000 records from a
given table, it took almost half a minute with a DataAdapter. Using a
Reader it took just under 12 seconds. .

Also, joining tables server side in a query probably isn't the way to go.
You can use a datarelation object to relate tables and then you don't have
to pull over as much data. The differences here can be profound as well.

Hopefully this helps.

Bill
 
Thanks. I'm talking about each recordset containing a
maximum of 60 records at a time. All the three recordsets
are basically retrieved from the same table for 3
different items. I thought I could create a SQL pivot view
that would return a single recordset (which would be a
combination) of all the 3 recordsets I've been mentioning.
(and then use a data reader to read from this recordset.)
This way I can use a single connection. Also, I don't know
if there is any overheads involved in using a dataset.
Kindly let me know if I am wrong.
Thanks for all of your replies.
 
Back
Top