Where to open/close SQLite connection?

[littlebigman]

Hello,

I'm learning VB.Net 2008 and would like to make sure this is the right way to open/close the connection to an SQLite database file:

Public Class Form1
    'Declare SQLite variables globally since must be accessed by Form + BackgroundWorker threads
  Dim SQLconnect As New SQLite.SQLiteConnection()
  Dim SQLcommand As SQLite.SQLiteCommand
  Dim SQLtransaction As SQLite.SQLiteTransaction
  Dim SQLreader As SQLite.SQLiteDataReader

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      SQLconnect.ConnectionString = "Data Source=test.sqlite;"
      SQLconnect.Open()
      SQLcommand = SQLconnect.CreateCommand

      '1. Empty DB, read all files from UE backup directory, and update DB
      SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY AUTOINCREMENT, hash TEXT)"
      SQLcommand.ExecuteNonQuery()

      SQLcommand.CommandText = "DELETE FROM files; VACUUM"
      SQLcommand.ExecuteNonQuery()

      SQLcommand.CommandText = "CREATE INDEX IF NOT EXISTS index_hash ON files(hash)"
      SQLcommand.ExecuteNonQuery()
  End Sub

  Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
      SQLcommand.Dispose()
      SQLconnect.Close()
  End Sub
End Class

Thank you.

 

[r3plica]

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:

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

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

 

[littlebigman]

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