Thread: Where to open/close SQLite connection?

Close your connections straight after you have finished with your queries. So at the the end of your last executenonquery, close and dispose your connection.

You do not want to have an open connection while your project is open.

Take a look at this:

Code:

Imports HoN.Common
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class PassiveData
    Inherits BaseDataAccess

    Public Shared Function GetAllPassive() As EffectCollection
        Dim pPassiveCol As New EffectCollection
        Dim sCommand As New SqlCommand()
        sCommand.CommandType = CommandType.StoredProcedure
        sCommand.CommandText = "HoN_GetAllPassive"
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        Try
            Dim ds As New DataSet
            ds = FillDataSet(sCommand)
            For Each pDR As DataRow In ds.Tables(0).Rows
                pPassiveCol.Add(ParsePassive(pDR))
            Next
        Catch ex As Exception
            Throw New Exception(ex.ToString)
        End Try
        Return pPassiveCol
    End Function

    Public Shared Function GetPassive(ByVal pItemID As Integer) As EffectCollection
        ' Declare our Stored Procedure
        Dim pEffectCol As New EffectCollection
        Dim sCommand As New SqlCommand()
        sCommand.CommandType = CommandType.StoredProcedure
        sCommand.CommandText = "dbo.HoN_GetPassive"
        sCommand.Parameters.Add("@ItemID", SqlDbType.Int).Value = pItemID
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString

        ' Try
        Try
            ' Fill our DataSet
            Dim ds As New DataSet
            ds = FillDataSet(sCommand)

            ' Parse
            For Each pDR As DataRow In ds.Tables(0).Rows
                pEffectCol.Add(ParsePassive(pDR))
            Next

        Catch ex As Exception
            ' Throw an Exception
            Throw New Exception(ex.ToString)
        End Try

        Return pEffectCol
    End Function

    Public Shared Function Insert(ByVal pItemID As Integer, ByVal pPassiveID As Integer) As Boolean
        Dim pCmd As New SqlCommand()
        pCmd.CommandType = CommandType.StoredProcedure
        pCmd.CommandText = "dbo.HoN_CreatePassiveLink"
        pCmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = pItemID
        pCmd.Parameters.Add("@PassiveID", SqlDbType.Int).Value = pPassiveID
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        Try
            ExecuteNonSelect(pCmd)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function Update(ByVal pItemID As Integer, ByVal pOldPassiveID As Integer, ByVal pNewPassiveID As Integer) As Boolean
        Dim pCmd As New SqlCommand()
        pCmd.CommandType = CommandType.StoredProcedure
        pCmd.CommandText = "dbo.HoN_UpdatePassiveLink"
        pCmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = pItemID
        pCmd.Parameters.Add("@PassiveID", SqlDbType.Int).Value = pNewPassiveID
        pCmd.Parameters.Add("@OldPassiveID", SqlDbType.Int).Value = pOldPassiveID
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        Try
            ExecuteNonSelect(pCmd)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Shared Function Delete(ByVal pItemID As Integer, ByVal pPassiveID As Integer) As Boolean
        Dim pCmd As New SqlCommand()
        pCmd.CommandType = CommandType.StoredProcedure
        pCmd.CommandText = "dbo.HoN_DeletePassiveLink"
        pCmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = pItemID
        pCmd.Parameters.Add("@PassiveID", SqlDbType.Int).Value = pPassiveID
        SetConnectionString = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString
        Try
            ExecuteNonSelect(pCmd)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Private Shared Function ParsePassive(ByVal pDR As DataRow) As Effect
        Dim newEffect As New Effect

        With newEffect
            .ID = pDR("id")
            .Name = Trim(pDR("name"))
        End With

        Return newEffect
    End Function
End Class

and this is the BaseDataAcess class

Code:

Imports System.Data
Imports System.Data.SqlClient

Public Class BaseDataAccess
    Private Shared mstrConnectionString As String
    Private Shared mConn As SqlConnection

    Public Shared WriteOnly Property SetConnectionString() As String
        Set(ByVal value As String)
            mstrConnectionString = value
        End Set
    End Property

    Shared Function FillDataSet(ByVal pSQLCmd As SqlCommand) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            da.SelectCommand = pSQLCmd
            da.Fill(ds)
            da.Dispose()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
        Return ds
    End Function

    Shared Sub ExecuteNonSelect(ByVal pSQLCmd As SqlCommand)
        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            pSQLCmd.ExecuteNonQuery()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If
    End Sub

    Shared Function ExecuteScalar(ByVal pSQLCmd As SqlCommand)
        Dim sReturn

        If mstrConnectionString <> "" Then
            OpenConnection()
            pSQLCmd.Connection = mConn
            sReturn = pSQLCmd.ExecuteScalar()
            CloseConnection()
        Else
            Throw New ApplicationException("Connection String has not been set")
        End If

        Return sReturn
    End Function

    Private Shared Sub OpenConnection()
        mConn = New SqlConnection
        mConn.ConnectionString = mstrConnectionString
        mConn.Open()
    End Sub

    Private Shared Sub CloseConnection()
        If mConn.State = ConnectionState.Open Then
            mConn.Close()
            mConn.Dispose()
        End If
    End Sub

End Class

Notice where my open connection and close connections are being called in the BaseDataAccess class. I am ensuring that there are no open connections at alll after I have finished executing my stored procedures.

I hope that helps

Thanks for the tip. I'll write a couple of routines to open/close the connection right before/after running a query then.