See a post:SqlDataReader ShutdownEveryone seems confused about the link closure problem. Here we will explain it:
No matter what xxDataReader is, It inherits the implementation of DataReader, so there are commonalities, so the title is titled DataReader.
Case 1: The default link of DataReader is not closed.
Sample Code:
Static void Main (string [] args)
{
SqlConnection con = new SqlConnection ("server =.; database = MySpace; uid = sa; pwd = 123456 ");
Con. Open ();
SqlCommand com = new SqlCommand ("select top 1 id from blog_user", con );
SqlDataReader sdr = com. ExecuteReader (System. Data. CommandBehavior. CloseConnection );
While (sdr. Read ())
{
}
Console. WriteLine (sdr. IsClosed );
Console. WriteLine (con. State. ToString ());
Console. ReadLine ();
}
Conclusion:
False
Open
Note:By default, whether or not System. Data. CommandBehavior. CloseConnection is added, the database link will not be disabled during reading.
Scenario 2: The DataReader link is closed.
Sample Code: [the original code]
Protected void bind ()
{
SqlConnection conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["constr"]. ToString ());
Conn. Open ();
SqlCommand cmd = new SqlCommand ("GetAllUser", conn );
SqlDataReader sdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Repeater1.DataSource = sdr;
Repeater1.DataBind ();
Response. Write (sdr. IsClosed. ToString () + "<br/> ");
Response. Write (conn. State. ToString ());
}
The result is:
True
Closed
Situation: After System. Data. CommandBehavior. CloseConnection is added, the link is closed for you. Why? See the cause of the analysis below.
Iii. Cause Analysis
1: from the two examples above, what is the difference?
A: The difference is that one is read-only data and the other is bound with a data list control.
2: Why does the link automatically close when a data list control is bound?
A: This involves the Data Control binding mechanism. Here is a brief introduction:
A: To bind the data control list, an interface is required: IEnumerable
B: code that implements DataReader to implement this interface [the base class is an abstract method, so it can only be viewed by the subclass SqlDataReader]:
Public override IEnumerator GetEnumerator ()
{
Return new DbEnumerator (this, (this. _ commandBehavior & CommandBehavior. CloseConnection) = CommandBehavior. CloseConnection );
}
From this code, we can see that it has passed CloseConnection into DbEnumerator, and then we can see it again:
Public DbEnumerator (IDataReader reader, bool closeReader)
{
If (reader = null)
{
Throw ADP. ArgumentNull ("reader ");
}
This. _ reader = reader;
This. closeReader = closeReader; // The flag is set for this row.
}
Click it to view the constructor and assign it to this. closeReader attribute. Because DataReader is a forward read method, the focus is to look at MoveNext:
Public bool MoveNext ()
{
If (this. _ schemaInfo = null)
{
This. BuildSchemaInfo ();
}
This. _ current = null;
If (this. _ reader. Read () // this method is called once and Read once
{
Object [] values = new object [this. _ schemaInfo. Length];
This. _ reader. GetValues (values );
This. _ current = new DataRecordInternal (this. _ schemaInfo, values, this. _ descriptors, this. _ fieldNameLookup );
Return true; // return directly if data exists. The following close link is not executed.
}
If (this. closeReader) // well, you can proceed here. It indicates that no data has been read. In short, the data has been read.
{
This. _ reader. Close (); // Close the link.
}
Return false;
}
The above code is described in my comments.
C: Why is it difficult to bind a list control with DataReader?
A: Because the server control list usually takes a long time to render the table, the last row of data is read only when you see the final result list.
Therefore, the link is open for a long time. Therefore, when there are many concurrent web connections, an error is returned when the connection pool is full.
4. What is the final conclusion?
1: When a list control is bound, the link is automatically closed once the data row is read.
2: When you directly read data, it does not trigger binding related reads, so the link is not automatically closed.
3.
Okay. I hope it will help you.