SQLiteDatabase Helper Class

'This file is a SQLite Database helper Class
'This file was orginally written in C# by #1 brennydoogles and some functions by Mike Duncan
'I Taximaniac have ported it over to vb.net
'ENJOY
'orginal code at http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SQLite
Imports System.Windows.Forms

Public Class SQLiteDatabase

    Private dbConnection As String

    ''' <summary>
    '''  Default Constructor for SQLiteDatabase Class.
    ''' </summary>
    Public Sub New()
        dbConnection = "Data Source=CCMemberStats.s3db"
    End Sub

    ''' <summary>
    ''' Single Param Constructor for specifying the DB File
    ''' </summary>
    ''' <param name="inputFile">The file containing the DB</param>
    Public Sub New(ByVal inputFile As String)
        dbConnection = String.Format("Data Source={0}", inputFile)
    End Sub

    ''' <summary>
    ''' Single Param Constructor for specifying advanced connection options
    ''' </summary>
    ''' <param name="connectionOpts">A dictionary containing all desired options and their values.</param>
    Public Sub New(ByVal connectionOpts As Dictionary(Of String, String))
        Dim str As String = ""

        For Each row As KeyValuePair(Of String, String) In connectionOpts
            str += String.Format("{0}={1};", row.Key, row.Value)
        Next

        str = str.Trim().Substring(0, str.Length - 1)
        dbConnection = str
    End Sub

    ''' <summary>
    ''' Allows the programmer to run a query against the database.
    ''' </summary>
    ''' <param name="SQL">The SQL to run.</param>
    ''' <returns>A datatable containing the result set.</returns>
    Public Function GetDataTable(ByVal SQL As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
                    dt.Load(reader)
                    reader.Close()
                End Using
                cnn.Close()
            End Using

            Return dt
        Catch ex As Exception
            ShowDBError("GetDataTable", ex)
            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' Allows the programmer to interact with the database for purposes other than query.
    ''' </summary>
    ''' <param name="SQL">The sql to be run</param>
    ''' <returns>An integer containing the number of rows affected. if error it returns -1</returns>
    Public Function ExecuteNonQuery(ByVal SQL As String) As Integer
        Dim rowsUpdated As Integer
        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    rowsUpdated = mycommand.ExecuteNonQuery()
                End Using
                cnn.Close()
            End Using
            Return rowsUpdated
        Catch ex As Exception
            ShowDBError("ExecuteNonQuery", ex)
            Return -1
        End Try
    End Function

    ''' <summary>
    ''' Allows the programmer to retrieve single items from the db.
    ''' </summary>
    ''' <param name="SQL">the sql to run</param>
    ''' <returns>a string</returns>
    Public Function ExecuteScalar(ByVal SQL As String) As String
        Dim value As Object

        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    value = mycommand.ExecuteScalar()
                End Using
                cnn.Close()
            End Using

            If value IsNot Nothing Then
                Return value.ToString()
            End If

            Return ""
        Catch ex As Exception
            ShowDBError("ExecuteScalar", ex)
            Return ""
        End Try
    End Function

    ''' <summary>
    ''' Allows the programmer to easily update rows in the db.
    ''' </summary>
    ''' <param name="TableName">The table to update</param>
    ''' <param name="Data">A dictionary containing column names and their new values</param>
    ''' <param name="where">the where clause for the update statement</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Update(ByVal TableName As String, ByVal Data As Dictionary(Of String, String), ByVal where As String) As Boolean
        Dim vals As String = ""
        Dim returnCode As Boolean = True

        If Data.Count >= 1 Then
            For Each val As KeyValuePair(Of String, String) In Data
                vals += String.Format("{0}='{1}',", val.Key.ToString(), val.Value.ToString())
            Next
            vals = vals.Substring(0, vals.Length - 1)
        End If

        Try
            Me.ExecuteNonQuery(String.Format("UPDATE {0} SET {1} WHERE {2};", TableName, vals, where))
        Catch ex As Exception
            ShowDBError("Update", ex)
            returnCode = False
        End Try
        Return returnCode
    End Function

    ''' <summary>
    ''' Allows the programmer to easily delete rows from the db.
    ''' </summary>
    ''' <param name="TableName">the table from wich to delete.</param>
    ''' <param name="Where">the where clause for the delete.</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Delete(ByVal TableName As String, ByVal Where As String) As Boolean
        Dim returnCode As Boolean = True
        Try
            Me.ExecuteNonQuery(String.Format("DELETE FROM {0} WHERE {1};", TableName, Where))
        Catch ex As Exception
            ShowDBError("Delete", ex)
            returnCode = False
        End Try
        Return returnCode
    End Function

    ''' <summary>
    ''' Allows the programmer to insert into the db.
    ''' </summary>
    ''' <param name="TableName">The table into wich we insert the data.</param>
    ''' <param name="data">a dictionary containing the column names and data.</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Insert(ByVal TableName As String, ByVal data As Dictionary(Of String, String)) As Boolean
        Dim columns As String = ""
        Dim values As String = ""
        Dim returnCode As Boolean = True

        For Each val As KeyValuePair(Of String, String) In data
            columns += String.Format("{0},", val.Key.ToString())
            values += String.Format("{0},", val.Value.ToString())
        Next

        columns = columns.Substring(0, columns.Length - 1)
        values = values.Substring(0, values.Length - 1)

        Try
            Me.ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES({2});", TableName, columns, values))
        Catch ex As Exception
            ShowDBError("Insert", ex)
            returnCode = False
        End Try

        Return returnCode
    End Function

    ''' <summary>
    ''' Allows the programmer to easily delete all data from the db.
    ''' </summary>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function ClearDB()
        Dim tables As DataTable

        Try
            tables = Me.GetDataTable("SELECT NAME FROM SQLITE_MASTER WHERE type='table' order by NAME;")

            For Each table As DataRow In tables.Rows
                Me.ClearTable(table("NAME").ToString())
            Next
            Return True
        Catch ex As Exception
            ShowDBError("ClearDB", ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Allows the user to easily clear all data from a specific table.
    ''' </summary>
    ''' <param name="table">the name of the table to clear</param>
    ''' <returns></returns>
    Public Function ClearTable(ByVal table As String)
        Try
            Me.ExecuteNonQuery(String.Format("DELETE FROM {0};", table))
            Return True
        Catch ex As Exception
            ShowDBError("ClearTable", ex)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Show a standard error message using messagebox
    ''' </summary>
    ''' <param name="SubName">Name of the sub or function where you add this sub</param>
    ''' <param name="ex">the error in the catch</param>
    Private Sub ShowDBError(ByVal SubName As String, ByVal ex As Exception)
        MessageBox.Show(ex.Message, "Database Error in SQLiteDatabase." & SubName & "()", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Sub
End Class