DataReader Link Close FAQ

I have seen some posts: questions about DataReader's closure, which may seem confusing to everyone. I will explain it here:

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: whether or not System. Data. CommandBehavior. CloseConnection is added by default, 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:

Required 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.