connection pool and datareader errors

[agrothe]

Some strange errors occurring in my asp.net website.

I was using the MS ApplicationBlocks.data.dll to connect o my SQL database. Once I started testing my website on IIS as opposed to the ASP.NET development server, I started getting connection pool errors.

So, like anyone would, I searched for every instance of opening a recordset and ensured that each one was closed right after being done with it.

Still no luck.

So, I wrote my own data wrapper function, very basic, which returns a DataReader and explicitly closes the connection each time.

Then, I started getting an "ExecuteReader requires an open and available Connection. The connection's current state is closed.." error. I've modfied my function as below, which catches the error, clears the Connection Pools an re-opens the connection. This function hasn't caused any problems in my testing, except that every 9-11 clicks I notice a much longer page load as the exception is caught and handled.

I really don't want to leave this code as is. Can anyone shed some light onto the possible root cause?

Thanks!!!

Private Function _getRS(ByVal strSql As String) As System.Data.SqlClient.SqlDataReader
        Dim conn As New System.Data.SqlClient.SqlConnection(strSQLconn)
        Dim cmd As New System.Data.SqlClient.SqlCommand(strSql, conn)
        Dim rs As System.Data.SqlClient.SqlDataReader
        Try
            conn.Open()
            rs = cmd.ExecuteReader
        Catch ex As Exception

            System.Data.SqlClient.SqlConnection.ClearPool(conn)
            System.Data.SqlClient.SqlConnection.ClearAllPools()
            System.GC.Collect()
            conn.Open()
            rs = cmd.ExecuteReader
        Finally


        End Try
        Return rs

        cmd.Dispose()
        cmd = Nothing
        conn.Close()
        conn.Dispose()
        conn = Nothing

    End Function

[sknake]

The problem is you're not closing the connection. You're returning a data reader which requires an open connection to read the data. You need to declare a DataTable and use dt.Fill(rs);

Private Function _getRS(ByVal strSql As String) As System.Data.SqlClient.SqlDataReader
        Dim conn As New System.Data.SqlClient.SqlConnection(strSQLconn)
        Dim cmd As New System.Data.SqlClient.SqlCommand(strSql, conn)
        Dim rs As System.Data.SqlClient.SqlDataReader
        Try
            conn.Open()
            rs = cmd.ExecuteReader
        Catch ex As Exception

            System.Data.SqlClient.SqlConnection.ClearPool(conn)
            System.Data.SqlClient.SqlConnection.ClearAllPools()
            System.GC.Collect()
            conn.Open()
            rs = cmd.ExecuteReader
        Finally


        End Try
        Return rs <-- The function exits after this call

        cmd.Dispose() <-- This is never run
        cmd = Nothing <-- This is never run
        conn.Close() <-- This is never run
        conn.Dispose() <-- This is never run
        conn = Nothing <-- This is never run

    End Function

If you *must* return a data reader then the calling function is responsible for closing and disposing of the sqlConnection. If you 'fix' the code above and close the SqlConnection before the data reader returns, then your application will raise an exception on the caller because when you attempt to access the DataReader it will report a closed connection.

Another approach you may want to try is:

public DataTable QueryDataTable(string query, List<DataParameter> parms)
		{
			DataTable result = null;
			using (DbConnection conn = GetDbConnection())
			{
				conn.Open();
				using (DbCommand cmd = GetDbCommand(query, parms, conn))
				{
					LogQuery(query, parms);
					using (DbDataReader dr = cmd.ExecuteReader())
					{
						result = new DataTable();
						result.Load(dr);
					}
				}
				conn.Close();
			}
			return result;
		}

That is C# and using the base class, but port the concept over of using the datareader and returning a datatable.

I hope this helps

[agrothe]

The problem is you're not closing the connection. You're returning a data reader which requires an open connection to read the data. You need to declare a DataTable and use dt.Fill(rs);

That is C# and using the base class, but port the concept over of using the datareader and returning a datatable.

I hope this helps

This helps a lot actually. Too much classic asp on the go.

The c# code is awesome. I was trying to avoid using too many system resources by using a DataReader, but looks like that backfired.

Off to fix some code then. Thanks again.