CODEWITHSUNDEEP
c#datasetsqldatareader
Dave
Dave

Reputation: 7379

Differences between SqlDataReader or Dataset to query data from table

I have a table with millions of rows of data, and I would like to know which is the best way to query my data - using .ExecuteReader() or using a Dataset.

Using SqlDataReader like this:

myReader = cmd.ExecuteReader();

And after fill a list with the result

Or using DataSet

using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
    a.Fill(ds);
}

Which are the best method?

Upvotes: 1

Views: 809

Answers (3)

Steve
Steve

Reputation: 216353

The two objects are to be used in contexts fundamentally different.

A DataReader instance returned by the ExecuteReader doesn't return anything until you loop over it using the Read() method. It is a connected object that has a pointer to a current record on the backend database. You read the content of the record using the various GetXXXXX methods provided by the reader or simply using the indexer. When you have done with the current record you orderly pass to the following one using the Read() method. No way to go back or jump to record N + 100.

A DataSet instead is a disconnected object. It uses internally a DataReader to fill its local memory buffer with all the records returned by the command text query. It is handy if you need to work randomly on the data returned or show them on video or print them. But of course, waiting to have millions of records returned by the internal reader could be time consuming and the consuming of the local memory probably will kill your process before the end.

So, which is the best? Neither, if you have millions of records in your table, you need to put in place an appropriate WHERE condition to reduce the amount of records returned. Said that, it depends on what you need to do with the returned data. To display them on a grid probably you could use a DataSet. Instead a DataReader is better if you need to execute operations on the records one by one.

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460288

The question is what you want to fill

  • If you want to fill a DataSet/DataTable use DataAdapter.Fill(ds)
  • If you want to fill a list/array use a DataReader and a loop

The DataAdapter also uses a DataReader behind the scenes, but it loops all records. You could add a different logic to loop only part of the resultset.

"I have a table with million of rows": You should almost never need to return so many records. So don't filter in memory but in the database.

Upvotes: 1

yash
yash

Reputation: 812

Both Are good Methods. But if you use SqlDataReader than you have to close it. its is must. Otherwise you will not able to execute any other query until SqlDataReader is not closed.

Upvotes: -1

Related Questions