ExecuteReader closed connection

14 Feb 2006 3:01 PM

Gregor

Hi,
I am using the SQL data reader to access SQL2000. I am opening the
connection and executing the reader then closing reader and connection.
There is an example code:
    SqlDataReader sqlReader = null;

    tring storedprocName = "procName";
    SqlConnection sqlConn = new SqlConnection(connString);

            try
            {
                SqlCommand sqlCmd = new SqlCommand(storedprocName);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@someParam", someVal);
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();

                sqlReader = sqlCmd.ExecuteReader();
                while (sqlReader.Read())
                {
                    xmlFactData = sqlReader.GetString(0);
                }
            }
            catch(Exception ex)
            {
                Logger.AddMessage("Error ........... ", ex);
            }
            finally
            {
                sqlReader.Close();
                sqlConn.Close();
            }

>From time to time I receive errors like: "ExecuteReader requires an

open and available Connection. The connection's current state is
Closed.,System.Data." A couple of time I had got errors like:
"Internal connection fatal error.,System.Data."
Any idea what is going on? I have SP2 installed on .Net Framework. The
most strange thing is that this behaviour is completely
nondeterministic. It sometimes happens but generally works fine and
error is difficult to reproduce.

Regards:
Greg

14 Feb 2006 4:31 PM

Cowboy (Gregory A. Beamer) - MVP

The issue, most likely, can be solved in one of two ways.

1. Adjust the pooling
2. Adjust your pattern

#1 is declarative and not the best first option. #2 is good whether or not
you have to visit #1. The main thing to change is Dispose() on your
connection objects. The pattern below illustrates this.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;

try
{
    conn.Open();
    //fill and work with Reader here
}
finally
{
   conn.Dispose();
}

NOTE: the "using" keyword follows the same pattern. Also, doing most of the
work prior to Open() is your best option, regardless of anything else. Open
connections as late as possible and close as quickly as possible.

ANOTHER NOTE: You are not doing much work here, so the reader is a great
option. If you are ever going to churn around a bit, DataSets, while not the
perf giants, offer the ability to close down the connection rather than
churning with expensive open resources. Not an option in all apps, but
something to consider if you have a lot of calculations to do outside of the
DB.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"Gregor" wrote:

> Hi,
> I am using the SQL data reader to access SQL2000. I am opening the
> connection and executing the reader then closing reader and connection.
> There is an example code:
>     SqlDataReader sqlReader = null;
>
>     tring storedprocName = "procName";
>     SqlConnection sqlConn = new SqlConnection(connString);
>
>             try
>             {
>                 SqlCommand sqlCmd = new SqlCommand(storedprocName);
>                 sqlCmd.CommandType = CommandType.StoredProcedure;
>                 sqlCmd.Parameters.Add("@someParam", someVal);
>                 sqlCmd.Connection = sqlConn;
>                 sqlConn.Open();
>
>                 sqlReader = sqlCmd.ExecuteReader();
>                 while (sqlReader.Read())
>                 {
>                     xmlFactData = sqlReader.GetString(0);
>                 }
>             }
>             catch(Exception ex)
>             {
>                 Logger.AddMessage("Error ........... ", ex);
>             }
>             finally
>             {
>                 sqlReader.Close();
>                 sqlConn.Close();
>             }
>
> >From time to time I receive errors like: "ExecuteReader requires an
> open and available Connection. The connection's current state is
> Closed.,System.Data." A couple of time I had got errors like:
> "Internal connection fatal error.,System.Data."
> Any idea what is going on? I have SP2 installed on .Net Framework. The
> most strange thing is that this behaviour is completely
> nondeterministic. It sometimes happens but generally works fine and
> error is difficult to reproduce.
>
> Regards:
> Greg
>
>