1. Ways to obtain data
DataReader for online operation of data, DataReader will always occupy the SqlConnection connection, in the process of obtaining data other operations can no longer use the SqlConnection connection object.
Copy Code code as follows:
while (Datareader.read ())
{
..............
}
Dataview.datasource=datareader;
Dataview.databind ();
When the dataset is off-line, the dataset reads the data into memory at once and then disconnects, and other operations can use the SqlConnection connection object.
Background code
Copy Code code as follows:
public string test = "";
protected void Page_Load (object sender, EventArgs e)
{
DataSet ds=new DataSet ();//This is your data, and I'm not going to write it.
Test = "<table>";
for (int i = 0; I < ds. Tables[0]. Rows; i++)
{
test+= "<tr><td>" +ds. Tables[0]. Rows[i]["The fields you want"]. ToString () + "</td></tr>"
}
test+= "</table>";
}
Page code
Copy Code code as follows:
<form id= "Form1" runat= "Server" >
<%=test%>
</form>
Because DataReader reads only one row of data at a time, it takes up less memory. But DataReader is only forward and read-only, that is, can only be read in a single direction, if you want to go back to read the previous data is not allowed, and do not allow it to modify the data.
Because the dataset reads all the data at once, it is more resource-intensive, but it also increases flexibility, and you can make any additions and deletions to the data, read it in any order, and write it back to the database, in the event of a disconnected database connection.
One thing to note is that DataReader one row at a time does not mean that the data in the database is modified to read the new data, which is protected at the database level.
2. Mechanisms for obtaining data
DataReader read data through Idbcommand.executereader.
The dataset is populated with data by Dbdataadapter.fill
So DataReader cannot close the connection while fetching the data. The dataset can, because DbDataAdapter has read the data to the application server, so in the use of DataReader must pay attention to close the connection in a timely manner.
3. Other differences
DataReader read faster than dataset
DataReader is the data provider class, which is a generic class that populates data with the help of DbDataAdapter.
Because the dataset is offline operational data, it is necessary to be aware that locks are used in a transaction because the dataset is disconnected after the data is populated and the lock is released.
4. Using DataReader can improve execution efficiency, and there are two ways to improve the performance of your code: one is an ordinal lookup, and the other is to use the appropriate get method to find it. Because the results of the query are generally unchanged, unless you change the query statement again, you can locate the record by locating the location of the column. The problem with this approach is that you might know the name of a column without knowing where it is, and the solution is to call the DataReader object's GetOrdinal () method, which receives a column name and returns the column number of the column name. Cases:
Copy Code code as follows:
int id = reader. GetOrdinal ("CategoryName");
while (reader. Read ())
{
Response.Write (reader. GetInt32 (0). ToString () + "" + Reader. GetString (1). ToString () + "<br/>");
Reader. Close ();
}
DataReader's GetInt32 () and GetString () return a column's value by receiving a column number, two of which are most commonly used, and many other types.
(Note: The DataReader object closes the connection to the database by calling the close () method, and if the second connection is reopened before it is closed, an exception message is generated)
