Which is better: DataSet or DataReader?

Question

I just saw this thread: Datatable vs Dataset but that didn’t solve my doubts. Let me explain better, I was doing a database connection and had to show the results in a GridView. (I used RecordSet when I worked with VB6 even earlier, and the DataSet is very similar to it, so it was much easier to use a DataSet.) Then the guy told me that a DataSet is not the best way to do it.

So, should I study the DataReader or continue to use the DataSet? Data table? What are the pros and cons?

Answer 1

It depends on your needs. One of the most important differences is that the DataReader will keep an open connection to your database until you are done with it, while the DataSet will be an object in memory. If you bind a control to a DataReader, it still opens. In addition, DataReader is a direct approach to reading data that cannot be manipulated. With the DataSet, you can move forward and backward and manipulate the data as you like.

Some additional features: DataSets can be serialized and represented in XML and therefore easily passed to other levels. DataReaders cannot be serialized.

On the other hand, if you have a large number of rows to read from a database that you pass to a process for a business rule, a DataReader may make more sense than loading a DataSet with all the rows, taking up memory and possibly affect scalability.

Here's a link, a bit dated but still useful: Contrast ADO.NET DataReader and DataSet .

Answer 2

This is essentially: "which is better: a bucket or a hose?"

A DataSet is a bucket; it allows you to transfer an unrelated data set and work with it - but you will incur the cost of maintaining the bucket (so it’s best to keep it in a size convenient for you).

A data reader is a hose: it provides one-way access to data one way or once when it flies past you; You do not need to transfer all available water at the same time, but it must be connected to a tap / database.

And just as you can fill the bucket with a hose, you can fill the DataSet with a data reader.

What I'm trying to do is that they do different things ...

I do not use the DataSet very often, but some people love them. However, I use data readers to access BLOBs, etc.

Answer 3

In addition to Brand: you can use a DataSet without any database.

You can fill it out from an XML file or only from a program. Fill it with rows from one database, then expand them and write to another database.

DataSet is a fully integrated representation of the relational schema. Regardless of whether you ever use it with a real relational database, it is up to you.

Answer 4

Different needs, different solutions.

As you said, the dataset is more like a VB6 Recordset. That is, pull out the necessary data, transfer it, do what you want with it. Oh, and then, in the end, get rid of it when you're done.

Datareader is more limited, but it gives MUCH better performance when you only need to read data once. For example, if you yourself fill the grid - i.e. Pull out the data, skip it, fill in the grid for each row, and then throw out the data - the datareader is much better than a data set. On the other hand, do not even try to use the datareader if you intend to update the data ...

So yes, study it, but use it when necessary. A dataset gives you much more flexibility.

Answer 5

To answer the second question - yes, you should learn about DataReaders. In any case, you understand how to use them.

I think that you are better off using DataSets in this situation - since you are doing data binding and that’s it (I think CPU cycles are against human effort).

Regarding what you get the best performance. It really depends on your situation. For example, if you edit the data that you bind and modify the changes, you will be better off with DataSets

Answer 6

Datareader

The DataReader is used to read data from the database and is oriented only to read and redirect architecture when retrieving data from the database. DataReader will receive data very quickly compared to a data set. Usually we will use the ExecuteReader object to bind data to the datareader.

To bind DataReader data to a GridView, we need to write code, as shown below:

 using(SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test")) { con.Open(); SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn); SqlDataReader sdr = cmd.ExecuteReader(); gvUserInfo.DataSource = sdr; gvUserInfo.DataBind(); conn.Close(); 

Dataset

A DataSet is an unrelated oriented architecture, which means that when working with datasets there is no need for active connections, and this is a collection of DataTables and relationships between tables. It is used to store several data tables. You can select data form tables, create views based on the table, and set child lines above relationships. The DataSet also provides you with rich features such as saving XML data and loading XML data.

 SqlConnection conn = new SqlConnection("Data Source=abc;Integrated Security=true;Initial Catalog=Test"); conn.Open(); SqlCommand cmd = new SqlCommand("Select UserName, First Name,LastName,Location FROM Users", conn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); gvUserInfo.DataSource = ds; gvUserInfo.DataBind();