SqlDataReader Shutdown Issues

Original: SqlDataReader closure problem

Yesterday when a friend uses repeater to bind a data source, it always appears that the attempt to call FieldCount is invalid when the reader shuts down. " error.

I looked at his code, using the ExecuteReader method under the SqlHelper class, and returned a SqlDataReader to bind.

 Public StaticSqlDataReader ExecuteReader (CommandType cmdtype,stringCmdtext,paramsSqlparameter[] cmdparms)
{
SqlCommand cmd= NewSqlCommand ();
SqlConnection Conn= NewSqlConnection (conn_string);

        //we use a try/catch here because if the method throws an exception we want to
        //Close the connection throw code, because no DataReader would exist, hence the
        //Commandbehaviour.closeconnection won't work
        Try
{
PrepareCommand (CMD, conn,NULL, Cmdtype, Cmdtext, cmdparms);
SqlDataReader RDR=cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
            returnRDR;
}
        Catch
{
Conn. Close ();
            Throw;
}
}

He then wrote a class, GetData, using one of the methods to invoke SqlHelper.

 Public SqlDataReader Getreader ()
    {
        Using= "getalluser" null ))
        {
            return reader;
        }
        
    }

Where Getalluser is a stored procedure that queries all users without parameters, and finally data source bindings are made through the returned reader.

The mistake he made was obviously that reader had been turned off (using the reason) before reading the reader. So if you can't read the data, you will report that error. So, change his calling method:

  PublicSqlDataReader Getreader ()
{
        Try
{
SqlDataReader Reader=Sqlhelper.executereader (CommandType.StoredProcedure,"Getalluser", NULL);
            returnreader;

}
        Catch(Exception)
{

            return NULL;
}
        
}

This way, there is no error, and the UI layer does not have any problems with data binding. However, a new problem arises:SqlDataReader does not close manually, will it cause the connection pool to reach the maximum value?

We note that in SqlHelper, the use of commandbehavior.closeconnection, the role of "close SqlDataReader will automatically turn off SqlConnection." That means , closing the SqlConnection is done on the premise of shutting down the SqlDataReader, or it is necessary to manually close the SqlDataReader. and return to the SqlDataReader, and close it again, how to do? Some netizens put forward: before return reader, first close reader, that is, reader. Close ();

In fact, this is not possible, will report the same error as before, before reading the data, has been closed.

CommandBehavior.CloseConnection is used to close the database connection, which is for sure, but will it not together put SqlDataReader also closed together. In other words, the CommandBehavior.CloseConnection, is it not necessary to manually close the

we use SqlHelper and then test it in the foreground web page

  protected voidbind ()
{
        
SqlConnection Conn= NewSqlConnection (configurationmanager.connectionstrings["Constr"]. ToString ());
Conn. Open ();
SqlCommand cmd= NewSqlCommand ("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 of the output is

?Closed

Note that both SqlConnection and SqlDataReader have been shut down.

If you remove the commandbehavior.closeconnection, the result of the output is:

False

Open