[dba-VB] closing your connections

This is what I do with SQLCE when I need to modify the SDF, which shouldn't
differ too much from SQL Server:

        Dim strCEconn As String = "Data Source = " & strPath & strFileName
        Dim CEconn As SqlServerCe.SqlCeConnection = New
SqlServerCe.SqlCeConnection(strCEconn)
        CEconn.Open()
        Dim CEcmd As SqlServerCe.SqlCeCommand = CEconn.CreateCommand

        CEcmd.CommandText = "CREATE TABLE InvoiceOrderJunct([InvNo]
[nvarchar](8) NULL, [OrderID] [int], [AMNo] [nvarchar](10) NULL)"
        CEcmd.ExecuteNonQuery()

        CEcmd.Dispose()
        CEconn.Close()
        CEconn.Dispose()


I had to do this in one of my webservice functions, when we had weird
issues:


   <WebMethod()> _
Public Function InsertInvHdr(ByVal strInput As String, ByVal dsIn As
DataSet) As String

        Dim errNum As Integer
        Dim errMsg As String
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim row As DataRow

        Call fUnSecureString(strInput)

        Try
            'Following parameters are determined from strInput hash saved as
global variables
            myConnection = New SqlConnection("server=" & ServerName &
";uid=" & strUid & ";pwd=" & strPw& ";database=" & strDBname)
            myConnection.Open()
            myCommand = New SqlCommand
            myCommand.Connection = myConnection

            For Each row In dsIn.Tables(0).Rows
                myCommand.CommandText = "EXEC stpInsertIntoInvHdr '" &
row("ICNo").ToString() & "', '" & row("StoreNo").ToString() & "', '" &
row("CustNo").ToString()
                myCommand.ExecuteNonQuery()
            Next

            Return "InvHdr data inserted"
            'myConnection.Close()

'******************************************************************
            ' Added the 2 IF statements below on 8/4/2010 to see if it
clears out sleeping processes
            If (Not (myCommand) Is Nothing) Then
                myCommand.Dispose()
                myCommand = Nothing
            End If
            If (Not (myConnection) Is Nothing) Then
                myConnection.Close()
            End If
            '********************************************************
        Catch ex As Exception
            errNum = Err.Number
            errMsg = ex.Message
            Return "SQL/WS Error: WS.InsertInvHdr - " & errNum & ":" &
errMsg
            'Finally
            '   myConnection.Close()
        End Try
    End Function



On Wed, Jun 8, 2011 at 8:47 AM, Francisco Tapia <fhtapia at gmail.com> wrote:

> so I have a vb.net app (visual studio 2008) and I am making some sproc
> calls
> like so:
>
>
>  conn = New SqlConnection(My.MySettings.Default.dbConn)
>            cmd = New SqlCommand("dbo.stp_PCO", conn)
>            cmd.CommandType = CommandType.StoredProcedure
>            conn.Open()
>            da.SelectCommand = cmd
>            da.Fill(dtCTable)
>
> I've also tried closing my connections after usage by using the following
> closing statments
>
> dtCtable.Dispose()
> dtClockTable = nothing
> cmd.Dispose()
> cmd = nothing
> da.Dispose()
> da = nothing
> conn.close()
> conn.Dispose()
> conn = nothing
>
> but when I check my sql server I still see sleeping entires in the activity
> monitor suggesting that a connection is still active.  How do I sever the
> connection completely?  I know a connection is alive and well because I
> noticed that if I completely close the application the connection goes from
> sleeping to gone.  I want my connections to close once the data has been
> retrieved to allow the server to reuse that memory if needed.
>
> in vb 6 it used to be so simple with just ado :(
>
> your help is of course appreciated!
>
>
> -Francisco
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>