Connection questions (why don't they close?)

[rbb]

I have created a class that encapsulates all the calls to my database. The guts of it look like this (at least for reading data):

 

Public Class clsData

  Private strConnection As String
  Private cn As SqlConnection
  Private cmd As SqlCommand

  Public Sub New(ByVal str As String)

     strConnection = str

  End Sub

  Private Function OpenDB() As Boolean
     If IsNothing(cn) Then
        cn = New SqlConnection(strConnection)
        cn.Open()
        If cn.State = ConnectionState.Open Then OpenDB = True
     End If
  End Function

  Private Function CloseDB() As Boolean

     If Not IsNothing(cn) Then
        If cn.State = ConnectionState.Open Then cn.Close()         
        cn.Dispose()
        CloseDB = True
     End If

  End Function

  Public Function CommandExecuteReturnDataSet() As DataSet
     Dim ds As New DataSet
     Dim da As SqlDataAdapter


     If OpenDB() Then
        cmd.Connection = cn
        da = New SqlDataAdapter
        da.SelectCommand = cmd

        da.Fill(ds, "data")

        da.Dispose()

        CloseDB()

        Return ds
     End If
  End Function

  Public Function CommandCreateSP(ByVal sp As String) As Boolean

     cmd = New SqlCommand
     cmd.CommandText = sp
     cmd.CommandType = CommandType.StoredProcedure

  End Function

End Class

 

My calling code looks like this:

 

     Dim cls As clsData
     Dim ds As DataSet

     cls = New clsData(g_DBConnection)
     cls.CommandCreateSP("up_select_main_form")
     ds = cls.CommandExecuteReturnDataSet
     cls = Nothing

     If Not ds Is Nothing Then
       'Do stuff here
     End If
     ds.Dispose()

 

The problem is, when I make the call to cls.CommandExecuteReturnDataSet, I expect the SQL connectino to open, grab the data, and close. But if I run a sp_who2 on my SQL Server, I have TWO connections open, and they just don't go away until after the app closes. Does this have anything to do with connection pooling? In an effort to make my apps more robust, I want to open and close connections JIT to minimize network traffic and whatnot.

 

Any ideas?

 

Thanks,

 

Rob

[PlausiblyDamp]

Setting a variable to nothing doesn't free up the resources straight away under .Net, it just indicates to the Garbage Collector that they are now elligable for collection. You should call either the connection's Close or Dispose methods of the connection to release the DB connection.

[rbb]

In my CommandExecuteReturnDataSet event I am opening the connection, grabbing the data, and closing the connection.

 

I also tried the following with the same results:

  Public Function CommandExecuteReturnDataSet2() As DataSet
     Dim cn As SqlConnection
     Dim ds As New DataSet
     Dim da As SqlDataAdapter

     cn = New SqlConnection(strConnection)
     cn.Open()
     If cn.State = ConnectionState.Open Then
        cmd.Connection = cn
        da = New SqlDataAdapter
        da.SelectCommand = cmd

        da.Fill(ds, "data")

        da.Dispose()

        cn.Close()

     End If

     cn.Dispose()

     Return ds

  End Function

 

Why does cn.Open() actually open two (2) connections to SQL Server (both with ProgramName of .Net SqlClient Data Provider)?

 

Thanks again,

 

Rob

Edited September 10, 2004 by rbb

[PlausiblyDamp]

Is this being called from an ASP.Net application? If so what you will find is that ASP.Net will maintain the connections after they are closed so it can recycle them for use with other web sessions. If you re-execute the function from within the web page do you notice any new connections being made?

[rbb]

No - its just a standard windows application. If I continue using my application, it does not seem to create any new connections.

[GreenTree]

I have the same problem in a very simple Windows Application.

 

Before connecting to the database there are 14 connections (Query Analyzer said). After openning and closing and Disposing the connection, there are 16 !

 

The bad thing: I am trying to restore a database using the connection.

Due to these opened connections, I cannot get exclusive access to the DB and ... EXCEPTION throws.

 

Can we kill that unnecessary connections?

Can we restore from withing an application, any way?

 

Thanks

[rbb]

Anyone????

[JABE]

Probably caused by connection pooling or some other method not being able to close the connection. One way to be sure is to disable connection pooling (Pooling=false in conn string) and inspect Audit Login and Audit Logout events in SQL Profiler. With connection pooling disabled, run the application up to the point where db access is made. W/o closing the application yet, inspect the output in the Profiler window; the number of Audit Login events should equal the number of Audit Logout events. If not, then you forgot to close the connection somewhere in your code.

 

As to why 2 connections are made initially is a mystery to me also. Even setting Min Pool Size to 1 have no effect in my tests.