Cannot close connection 1

[aimlion11yahoo]

Below is a small piece of code that connects to a database, puts the data into a DataReader, loops through the DataReader, then attemps to close the connection.

I run the code, everything works.

Dim myConnectionString As String = ConfigurationSettings.AppSettings("ConnectionString_BillyBob")

Dim mySelectQuery As String = "Select OrderGID from ORDERS"

Dim myConnection As New SqlClient.SqlConnection(myConnectionString)

Dim myCommand As New SqlClient.SqlCommand(mySelectQuery, myConnection)

Dim tempValue As Integer
myConnection.Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Implicitly closes the connection because CommandBehavior.CloseConnection is specified.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dr As SqlClient.SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Do While dr.Read
  For tempValue = 0 To dr.FieldCount - 1
    Debug.WriteLine(dr.Item(tempValue).ToString)
  Next
Loop

dr.Close()
dr = Nothing
myConnection.Close()
myConnection.Dispose()
myConnection = Nothing
myCommand.Dispose()
myCommand = Nothing

After the code has executed, and the webpage is still up, I run the following query in SQL Query Analyzer. The connection is still open?!

SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses 
WHERE loginame = 'Adam' and db_name(dbid)='ASP';

 

[IT4EVR]

I'm not sure if sysprocesses is the best place to get the number of open user connections. However I ran this code and it shows the connection state as closed. I prefer to add a using statement in the code for the connection object because it closes and dereferences the connection in one operation without having to explicity close it.

The interesting thing about this is that the commandbehavior.closeconnection doesn't actually close the connection.

I wonder if Sql Server may hold at least one connection open due to connection pooling?

public void getProducts()
    {
        string cmd = "SELECT ProductID, ProductName FROM Products";
        string strConn = GetConnectionString();
        SqlCommand objCmd;
        SqlConnection objConn;
        using (objConn = new SqlConnection(strConn))
        {


            objCmd = new SqlCommand(cmd, objConn);
            objCmd.CommandType = CommandType.Text;
            objConn.Open();


            objCmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        Response.Write(objConn.State.ToString());
    }

 

[jbenson001]

that would be great but I belive the OP is using VB, and Using is only a C# statement.

 

[aimlion11yahoo]

Mmmmm...... Let me ask a slightly different question. Am I right in closing and disposing of the dataReader, Command, and the connection?

 

[jbenson001]

I think you have to look into connection pooling. I belive the connection will stay open. And I know there are ways to change the size of the connection pool, just not off the top of my head.

 

[IT4EVR]

Actually the 2.0/VS2005 version supports a using statement for VB.NET.

http://pluralsight.com/blogs/fritz/archive/2005/04/28/7834.aspx

 

[jbenson001]

Excllent.. good to know.. I am going to use this in the future..