Differences between datareader and dataset in asp.net

1. How to obtain data
DataReader is the online operation data, and DataReader will always occupy the SqlConnection connection. Other operations during the data acquisition process cannot use SqlConnection to connect to the object.
Copy codeThe Code is as follows:
While (datareader. read ())
{
..............
}
Dataview. datasource = datareader;
Dataview. databind ();

DataSet is offline operation data. DataSet reads data into the memory at a time and disconnects the connection. Other operations can use SqlConnection to connect objects.
Background code
Copy codeThe Code is as follows:
Public string test = "";
Protected void Page_Load (object sender, EventArgs e)
{
DataSet ds = new DataSet (); // here is your data, so I will not write it.
Test = "<table> ";
For (int I = 0; I <ds. Tables [0]. Rows; I ++)
{
Test + = "<tr> <td>" + ds. tables [0]. rows [I] ["fields you want"]. toString () + "</td> </tr>"
}
Test + = "</table> ";
}

Page code
Copy codeThe Code is as follows:
<Form id = "form1" runat = "server">
<% = Test %>
</Form>

Because DataReader reads only one row of data at a time, it occupies less memory. However, DataReader is read-only, that is, it can only read forward in one direction. If you want to read the previous data back, it is not allowed to modify the data.
Because DataSet reads all data at a time, it consumes resources, but it also improves flexibility. When the database is disconnected, you can add, delete, modify, and query data, read data in any order and write it back to the database.
Note that reading a row at a time by DataReader does not mean that the data in the database is modified and new data can be read. This is the protection of the database.
2. Data Retrieval Mechanism
DataReader reads data through IDbCommand. ExecuteReader.
DataSet uses DbDataAdapter. Fill to Fill in data.
Therefore, DataReader cannot close the connection when obtaining data. DataSet can, because DbDataAdapter has read data to the application server, you must pay attention when using DataReader and close the connection in time.
3. Other differences
DataReader reads faster than DataSet
DataReader is a data provider class and DataSet is a general class. Data is filled with DbDataAdapter.
Because DataSet is used to operate data offline, pay attention to the use of locks in transactions. Because DataSet will be disconnected after data is filled in, the locks will be released.
4. DataReader can improve the execution efficiency. There are two ways to improve the Code Performance: one is serial number-based search, and the other is to use the appropriate Get Method for search. Because the query results are generally not changed, unless the query statement is modified again, you can locate the column to find the record. One problem with this method is that you may know the name of a column without knowing its location. The solution to this problem is to call the GetOrdinal () method of the DataReader object, this method receives a column name and returns the column number of the column name. Example:
Copy codeThe Code is as follows:
Int id = reader. GetOrdinal ("CategoryName ");
While (reader. Read ())
{
Response. Write (reader. GetInt32 (0). ToString () + "" + reader. GetString (1). ToString () + "<br/> ");
Reader. Close ();
}

GetInt32 () and GetString () of DataReader return the value of a column by receiving a column number. These two types are the most commonly used, and there are many other types.
(Note: The DataReader object closes the connection to the database by calling the Close () method. If the second connection is re-opened before it is closed, an exception message is generated)