The difference between DataReader and datasets in C #

Source: Internet
Author: User

1. How to get Data
[1] DataReader to operate the data online, DataReader will always occupy the SqlConnection connection, and other operations can no longer use SqlConnection connection objects in the process of obtaining data.

The code is as follows:
 while (Datareader.read ()) {...}...} Dataview.datasource=datareader;dataview.databind ();

[2] The dataset operates data offline, the dataset reads the data into memory one at a time, then disconnects, and other operations can use the SqlConnection connection object.
The background code is as follows:

    Public stringTest =""; protected voidPage_Load (Objectsender, EventArgs e) {DataSet DS=NewDataSet ();//This is your data, and I'm not going to write it.Test ="<table>";  for(inti =0; I < DS. tables[0]. Rows; i++) {Test+="<tr><td>"+ds. tables[0]. rows[i]["the field you want"]. ToString () +"</td></tr>"} Test+="</table>"; }


The page code is as follows:

 <  form  id  = "Form1"   runat  = "Server"     >  <%  =  test  %>  </ form  >  

Because DataReader only reads one row of data at a time, it consumes less memory. But DataReader is read-only, that is, it can only go forward in one direction, and if you want to look back at the last piece of data is not allowed, and it is not allowed to modify the data.
Because the dataset reads all of the data at once, it consumes resources, but also improves flexibility, and you can change any additions or deletions to the data when disconnected from the database, read the data in any order, and write it back to the database.
It is important to note that DataReader reading a row at a time does not mean that the data in the database is modified to read the new data, which is the protection at the database level.

2. Mechanisms to get data
DataReader is to read data through Idbcommand.executereader.
The dataset populates the data with Dbdataadapter.fill.
So DataReader cannot close the connection when fetching data. A dataset can, because DbDataAdapter has read the data to the application server, so be sure to close the connection in a timely manner when using DataReader.

3. Other differences
DataReader reads faster than a dataset.
DataReader is the data provider class, and the DataSet is a generic class, with the help of DbDataAdapter to populate the data.
Because the dataset is manipulating data offline, it is important to note that when you use a lock in a transaction, the lock is freed because the dataset is disconnected after it fills the data.

4. Using DataReader can improve execution efficiency, there are two ways to improve the performance of the code: one is based on ordinal lookup, and the other is to use the appropriate get method to find.

Because the results of the query are generally not changed, unless you change the query statement again, you can locate the record by locating the column's location. One problem with this approach is that you might know the name of a column without knowing where it is, the solution to this problem is to call the GetOrdinal () method of the DataReader object, which receives a column name and returns the column number where the column name is located. Cases:

The code is as follows:
int id = reader. GetOrdinal ("categoryname");  while (reader. Read ()) {Response.Write (reader. GetInt32 (0" + reader.  GetString (1"<br/>"); Reader. Close ();}


DataReader's GetInt32 () and GetString () return the value of a column by receiving a column number, two of which are most commonly used, and there are many other types.
  

Attention:

The DataReader object closes the connection to the database when the close () method is called, and if the second connection is reopened before shutting down, an exception is generated, such as a concurrency problem.

The difference between DataReader and datasets in C #

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.