Data Access Library for SQLite

Having recently discovered SQLite, and having read Mike Duncan's excellent blog article on implementing SQLite with .NET, I decided to write a version of my SQL Server DAL to work with SQLite.

To use this code, you'll need to download and install System.Data.SQLite first. Create a new class library project called "DMFL.Data.SQLite.DAL", create a vb file and paste the code below into it. Ensure that your base namespace is empty! All the classes needed for the DAL are present in one file - you can split them up if you like (and I'll try to find the time to make the source available as a download soon). You'll need references to System, System.Data and System.Data.SQLite. That's all you need - enjoy!

Option Explicit On

Option Strict On

 

Imports DMFL.Data.SQLite.DAL.Exceptions

Imports Microsoft.VisualBasic

Imports System

Imports System.Data

Imports System.Data.SQLite

 

Namespace DMFL.Data.SQLite.DAL

 

    ''' <summary>

    ''' Provides data connection functionality to all of the Helper classes in this Namespace.

    ''' </summary>

    Public MustInherit Class HelperBase

 

#Region " Properties "

 

        Private c_strConnectionString As String = String.Empty

        ''' <summary>

        ''' Gets/sets the ConnectionString.

        ''' </summary>

        ''' <value>A String object.</value>

        ''' <returns>A String object.</returns>

        ''' <remarks></remarks>

        Public Property ConnectionString() As String

            Get

                Return c_strConnectionString

            End Get

            Set(ByVal value As String)

                c_strConnectionString = value

            End Set

        End Property

 

        Private c_mscConnection As SQLiteConnection

        ''' <summary>

        ''' The SQLiteConnection used by derived Classes to access the data.

        ''' </summary>

        ''' <value>A SQLiteConnection object.</value>

        ''' <returns>A SQLiteConnection object.</returns>

        Public Property Connection() As SQLiteConnection

            Get

                Return c_mscConnection

            End Get

            Set(ByVal value As SQLiteConnection)

                c_mscConnection = value

            End Set

        End Property

 

#End Region

 

#Region " Constructors "

 

        ''' <summary>

        ''' Sets custom connection information when the Class is instantiated.

        ''' </summary>

        ''' <param name="strConnectionString">A connection string</param>

        ''' <remarks></remarks>

        ''' <exception cref="UnhandledException">This exception is thrown

        ''' if the constructor fails for any reason.</exception>

        Protected Sub New(ByVal strConnectionString As String)

 

            Try

 

                ConnectionString = strConnectionString

 

            Catch ex As Exception

                Throw New UnhandledException(ex)

            End Try

 

        End Sub

 

        ''' <summary>

        ''' Provides a SQLiteConnection object through which to obtain data.

        ''' </summary>

        ''' <param name="mscConnection">A SQLiteConnection object.</param>

        ''' <remarks>This version of the constructor allows the developer to provide their own

        ''' SQLiteConnection object, allowing further manipulation of the connection prior to use

        ''' (you may, for example, wish to extend the ConnectionTimeout property beyond the default

        ''' value). Note that if you opt to provide your own SQLiteConnection object, you MUST manage

        ''' instantiation and disposal through your own code.</remarks>

        ''' <exception cref="NullExternalConnectionException">This exception

        ''' is thrown if the supplied connection evaulates to Nothing.</exception>

        ''' <exception cref="UnhandledException">This exception is thrown

        ''' if the constructor fails for any other reason.</exception>

        Protected Sub New(ByRef mscConnection As SQLiteConnection)

 

            Try

 

                If mscConnection Is Nothing Then

                    Throw New NullExternalConnectionException

                Else

                    Connection = mscConnection

                End If

 

            Catch ex As NullExternalConnectionException

                Throw ex

            Catch ex As Exception

                Throw New UnhandledException(ex)

            End Try

 

        End Sub

 

#End Region

 

#Region " Methods "

 

        ''' <summary>

        ''' Instantiates the Internal connection.

        ''' </summary>

        ''' <exception cref="UnableToOpenConnectionException">This exception is thrown

        ''' if this method fails for any reason.</exception>

        Protected Sub OpenConnection()

 

            Try

 

                ' connect

                Connection = New SQLiteConnection(ConnectionString)

                Connection.Open()

 

                ' check connection

                If Connection Is Nothing Then

                    Throw New UnableToOpenConnectionException

                End If

 

            Catch ex As UnableToOpenConnectionException

                Throw ex

            Catch ex As Exception

                Throw New UnableToOpenConnectionException(ex)

            End Try

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This Helper Class assists in populating a DataSet.

    ''' </summary>

    Public Class DataSetHelper

        Inherits HelperBase

 

#Region " Constructors "

 

        ''' <summary>

        ''' Sets the connection string when the Class is instantiated.

        ''' </summary>

        ''' <param name="strConnectionString">A connection string.</param>

        ''' <remarks></remarks>

        Public Sub New(ByVal strConnectionString As String)

 

            MyBase.New(strConnectionString)

 

        End Sub

 

        ''' <summary>

        ''' Provides a SQLiteConnection object through which to obtain data.

        ''' </summary>

        ''' <param name="mscConnection">A SQLiteConnection object.</param>

        ''' <remarks>This version of the constructor allows the developer to provide their own

        ''' SQLiteConnection object, allowing further manipulation of the connection prior to use

        ''' (you may, for example, wish to extend the ConnectionTimeout property beyond the default

        ''' value). Note that if you opt to provide your own SQLiteConnection object, you MUST manage

        ''' instantiation and disposal through your own code.</remarks>

        Public Sub New(ByRef mscConnection As System.Data.SQLite.SQLiteConnection)

 

            MyBase.New(mscConnection)

 

        End Sub

 

#End Region

 

#Region " Methods "

 

        ''' <summary>

        ''' Executes a SQL Statement, returning the result as a DataSet.

        ''' </summary>

        ''' <param name="strCommandText">A SQL Statement.</param>

        ''' <param name="mspParams">An array of SQL Statement Parameters.</param>

        ''' <param name="strTableName">The name of the source table for table mapping.</param>

        ''' <returns>A System.Data.DataSet object.</returns>

        ''' <remarks></remarks>

        ''' <exception cref="BadCommandException">This exception is thrown if the

        ''' sql command cannot be executed for any reason.</exception>

        ''' <exception cref="NullDataSetException">This exception is thrown

        ''' if the DataSet that results from the command evaluates to Nothing.</exception>

        Public Function ExecuteDataSet(ByVal strCommandText As String, ByVal strTableName As String, ByVal ParamArray mspParams As SQLiteParameter()) As DataSet

 

            Dim mscCommand As SQLiteCommand

            Dim msdaAdapter As SQLiteDataAdapter

            Dim dsDataSet As DataSet

            Dim mspParam As SQLiteParameter

 

            mscCommand = New SQLiteCommand

            msdaAdapter = New SQLiteDataAdapter

            dsDataSet = New DataSet

 

            Try

 

                ' set up the command

                OpenConnection()

                mscCommand = New SQLiteCommand(strCommandText, Connection)

                mscCommand.CommandType = CommandType.Text

 

                ' capture the params

                If Not mspParams Is Nothing Then

                    For Each mspParam In mspParams

                        mscCommand.Parameters.Add(mspParam)

                    Next

                End If

 

                ' execute

                msdaAdapter.SelectCommand = mscCommand

                msdaAdapter.Fill(dsDataSet, strTableName)

 

                ' check the command executed ok

                If dsDataSet Is Nothing Then

                    Throw New NullDataSetException()

                End If

 

                Return dsDataSet

 

            Catch ex As NullDataSetException

                Throw ex

            Catch ex As Exception

                Throw New BadCommandException(ex)

            Finally

 

                mspParam = Nothing

                msdaAdapter.Dispose()

                msdaAdapter = Nothing

                mscCommand.Dispose()

                mscCommand = Nothing

                Connection.Close()

 

            End Try

 

        End Function

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' Assists in execution of commands that do not return a recordset.

    ''' </summary>

    Public Class NonQueryHelper

        Inherits HelperBase

 

#Region " Constructors "

 

        ''' <summary>

        ''' Sets the connection string when the Class is instantiated.

        ''' </summary>

        ''' <param name="strConnectionString">A connection string.</param>

        ''' <remarks></remarks>

        Public Sub New(ByVal strConnectionString As String)

 

            MyBase.New(strConnectionString)

 

        End Sub

 

        ''' <summary>

        ''' Provides a SQLiteConnection object through which to obtain data.

        ''' </summary>

        ''' <param name="mscConnection">A SQLiteConnection object.</param>

        ''' <remarks>This version of the constructor allows the developer to provide their own

        ''' SQLiteConnection object, allowing further manipulation of the connection prior to use

        ''' (you may, for example, wish to extend the ConnectionTimeout property beyond the default

        ''' value). Note that if you opt to provide your own SQLiteConnection object, you MUST manage

        ''' instantiation and disposal through your own code.</remarks>

        Public Sub New(ByRef mscConnection As SQLiteConnection)

 

            MyBase.New(mscConnection)

 

        End Sub

 

#End Region

 

#Region " Methods "

 

        ''' <summary>

        ''' Executes a SQL Statement.

        ''' </summary>

        ''' <param name="strCommandText">A SQL Statement.</param>

        ''' <param name="mspParams">An array of SQL Statement Parameters.</param>

        ''' <returns>A 32-bit Integer; typically, the number of rows affected by the command.</returns>

        ''' <remarks></remarks>

        ''' <exception cref="BadCommandException">This exception is thrown if the

        ''' sql command cannot be executed for any reason.</exception>

        Public Function ExecuteNonQuery(ByVal strCommandText As String, ByVal ParamArray mspParams As SQLiteParameter()) As Int32

 

            Dim mscCommand As SQLiteCommand

            Dim mspParam As SQLiteParameter

 

            mscCommand = New SQLiteCommand

 

            Try

 

                ' set up the command

                OpenConnection()

                mscCommand = New SQLiteCommand(strCommandText, Connection)

                mscCommand.CommandType = CommandType.Text

 

                ' capture the params

                If Not mspParams Is Nothing Then

                    For Each mspParam In mspParams

                        mscCommand.Parameters.Add(mspParam)

                    Next

                End If

 

                ' execute

                Return mscCommand.ExecuteNonQuery()

 

            Catch ex As Exception

                Throw New BadCommandException(ex)

            Finally

 

                mscCommand.Dispose()

                mscCommand = Nothing

                mspParam = Nothing

                Connection.Close()

 

            End Try

 

        End Function

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' Assists in execution of commands that return a SQLiteDataReader object.

    ''' </summary>

    Public Class ReaderHelper

        Inherits HelperBase

 

#Region " Constructors "

 

        ''' <summary>

        ''' Sets the connection string when the Class is instantiated.

        ''' </summary>

        ''' <param name="strConnectionString">A connection string.</param>

        ''' <remarks></remarks>

        Public Sub New(ByVal strConnectionString As String)

 

            MyBase.New(strConnectionString)

 

        End Sub

 

        ''' <summary>

        ''' Provides a SQLiteConnection object through which to obtain data.

        ''' </summary>

        ''' <param name="mscConnection">A SQLiteConnection object.</param>

        ''' <remarks>This version of the constructor allows the developer to provide their own

        ''' SQLiteConnection object, allowing further manipulation of the connection prior to use

        ''' (you may, for example, wish to extend the ConnectionTimeout property beyond the default

        ''' value). Note that if you opt to provide your own SQLiteConnection object, you MUST manage

        ''' instantiation and disposal through your own code.</remarks>

        Public Sub New(ByRef mscConnection As SQLiteConnection)

 

            MyBase.New(mscConnection)

 

        End Sub

 

#End Region

 

#Region " Methods "

 

        ''' <summary>

        ''' Executes a SQL Statement, returning the result as a SQLiteDataReader.

        ''' </summary>

        ''' <param name="strCommandText">A SQL Statement.</param>

        ''' <param name="mspParams">An array of SQL Statement Parameters.</param>

        ''' <param name="cbBehaviour">The behavour of the command.</param>

        ''' <returns>A SQLiteDataReader object.</returns>

        ''' <remarks>You MUST close the helper's connection in your own code after using this method.</remarks>

        ''' <exception cref="BadCommandException">This exception is thrown if the

        ''' sql command cannot be executed for any reason.</exception>

        ''' <exception cref="NullDataReaderException">This exception is thrown

        ''' if the DataReader that results from the command evaluates to Nothing.</exception>

        Public Function ExecuteReader(ByVal strCommandText As String, ByVal cbBehaviour As CommandBehavior, ByVal ParamArray mspParams As SQLiteParameter()) As SQLiteDataReader

 

            Dim mscCommand As SQLiteCommand

            Dim mspParam As SQLiteParameter

            Dim msdrReader As SQLiteDataReader

 

            mscCommand = New SQLiteCommand

 

            Try

 

                ' set up the command

                OpenConnection()

                mscCommand = New SQLiteCommand(strCommandText, Connection)

                mscCommand.CommandType = CommandType.Text

 

                ' capture the params

                If Not mspParams Is Nothing Then

                    For Each mspParam In mspParams

                        mscCommand.Parameters.Add(mspParam)

                    Next

                End If

 

                ' execute

                msdrReader = mscCommand.ExecuteReader(cbBehaviour)

 

                ' check the command worked

                If msdrReader Is Nothing Then

                    Throw New NullDataReaderException()

                End If

 

                Return msdrReader

 

            Catch ex As NullDataReaderException

                Throw ex

            Catch ex As Exception

                Throw New BadCommandException(ex)

            Finally

 

                mscCommand.Dispose()

                mscCommand = Nothing

                mspParam = Nothing

 

            End Try

 

        End Function

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' Assists in execution of commands that return an Object.

    ''' </summary>

    Public Class ScalarHelper

        Inherits HelperBase

 

#Region " Constructors "

 

        ''' <summary>

        ''' Sets the connection string when the Class is instantiated.

        ''' </summary>

        ''' <param name="strConnectionString">A connection string.</param>

        ''' <remarks></remarks>

        Public Sub New(ByVal strConnectionString As String)

 

            MyBase.New(strConnectionString)

 

        End Sub

 

        ''' <summary>

        ''' Provides a SQLiteConnection object through which to obtain data.

        ''' </summary>

        ''' <param name="mscConnection">A SQLiteConnection object.</param>

        ''' <remarks>This version of the constructor allows the developer to provide their own

        ''' SQLiteConnection object, allowing further manipulation of the connection prior to use

        ''' (you may, for example, wish to extend the ConnectionTimeout property beyond the default

        ''' value). Note that if you opt to provide your own SQLiteConnection object, you MUST manage

        ''' instantiation and disposal through your own code.</remarks>

        Public Sub New(ByRef mscConnection As SQLiteConnection)

 

            MyBase.New(mscConnection)

 

        End Sub

 

#End Region

 

#Region " Methods "

 

        ''' <summary>

        ''' Executes a SQL Statement, returning the result as an Object.

        ''' </summary>

        ''' <param name="strCommandText">A SQL Statement.</param>

        ''' <param name="mspParams">An array of SQL Statement Parameters.</param>

        ''' <returns>An Object.</returns>

        ''' <remarks></remarks>

        ''' <exception cref="BadCommandException">This exception is thrown if the

        ''' sql command cannot be executed for any reason.</exception>

        Public Function ExecuteScalar(ByVal strCommandText As String, ByVal ParamArray mspParams As SQLiteParameter()) As Object

 

            Dim mscCommand As SQLiteCommand

            Dim mspParam As SQLiteParameter

 

            mscCommand = New SQLiteCommand

 

            Try

 

                ' set up the command

                OpenConnection()

                mscCommand = New SQLiteCommand(strCommandText, Connection)

                mscCommand.CommandType = CommandType.Text

 

                ' capture the params

                If Not mspParams Is Nothing Then

                    For Each mspParam In mspParams

                        mscCommand.Parameters.Add(mspParam)

                    Next

                End If

 

                ' execute

                Return mscCommand.ExecuteScalar

 

            Catch ex As Exception

                Throw New BadCommandException(ex)

            Finally

 

                mscCommand.Dispose()

                mscCommand = Nothing

                mspParam = Nothing

                Connection.Close()

 

            End Try

 

        End Function

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' Provides Sql data conversion functions

    ''' </summary>

    Public Class ConversionHelper

 

#Region "Methods"

 

        ''' <summary>

        ''' Converts a DateTime into a string suitable for use in Sql queries

        ''' </summary>

        ''' <param name="dtDateTime">The DateTime to convert.</param>

        ''' <returns>A String object.</returns>

        ''' <remarks>Returns the supplied date in a single-quote enclosed string, formatted yyyy/mm/dd hh:mm:ss</remarks>

        ''' <exception cref="ConversionFailureException">This exception is thrown if

        ''' the conversion fails for any reason.</exception>

        Public Shared Function ToSqlDateTime(ByVal dtDateTime As DateTime) As String

 

            Try

 

                Return "'" & Format(dtDateTime.Year, "0000") & _

                           "/" & Format(dtDateTime.Month, "00") & _

                           "/" & Format(dtDateTime.Day, "00") & _

                           " " & Format(dtDateTime.Hour, "00") & _

                           ":" & Format(dtDateTime.Minute, "00") & _

                           ":" & Format(dtDateTime.Second, "00") & "'"

 

            Catch ex As Exception

 

                Throw New ConversionFailureException(ex)

 

            End Try

 

        End Function

 

        ''' <summary>

        ''' Converts a string into a string suitable for use in Sql queries

        ''' </summary>

        ''' <param name="strString">The String to convert.</param>

        ''' <returns>A String object.</returns>

        ''' <remarks>Returns the supplied string in a single-quote enclosed string.

        ''' Any single-quote characters in the original string are escaped.</remarks>

        ''' <exception cref="ConversionFailureException">This exception is thrown if

        ''' the conversion fails for any reason.</exception>

        Public Shared Function ToSqlString(ByVal strString As String) As String

 

            Try

 

                If Not strString = String.Empty Then

                    Return "'" & strString.Replace("'", "\'") & "'"

                Else

                    Return String.Empty

                End If

 

            Catch ex As Exception

 

                Throw New ConversionFailureException(ex)

 

            End Try

 

        End Function

 

        ''' <summary>

        ''' Converts a Guid into a string suitable for use in Sql queries

        ''' </summary>

        ''' <param name="guidGuid">The Guid to convert.</param>

        ''' <returns>A String object.</returns>

        ''' <remarks>Returns a representation of the supplied Guid in a single-quote

        ''' enclosed string.</remarks>

        ''' <exception cref="ConversionFailureException">This exception is thrown if

        ''' the conversion fails for any reason.</exception>

        Public Shared Function ToSqlGuid(ByVal guidGuid As Guid) As String

 

            Try

 

                If Not guidGuid.CompareTo(Guid.Empty) = 0 Then

                    Return String.Empty

                Else

                    Return guidGuid.ToString.Replace("{", String.Empty).Replace("}", String.Empty)

                End If

 

            Catch ex As Exception

 

                Throw New ConversionFailureException(ex)

 

            End Try

 

        End Function

 

        ''' <summary>

        ''' Converts a Sql VarChar into a system Guid.

        ''' </summary>

        ''' <param name="objValue">The Sql VarChar to convert.</param>

        ''' <returns>A System.Guid object.</returns>

        ''' <remarks>Returns Guid.Empty if the object is null or evaluates to empty string.</remarks>

        ''' <exception cref="ConversionFailureException">This exception is thrown if

        ''' the conversion fails for any reason.</exception>

        Public Shared Function FromSqlGuid(ByVal objValue As Object) As Guid

 

            Try

                If objValue Is DBNull.Value Then

                    Return Guid.Empty

                ElseIf objValue.ToString = String.Empty Then

                    Return Guid.Empty

                Else

                    Return New Guid(objValue.ToString)

                End If

 

            Catch ex As Exception

                Throw New ConversionFailureException(ex)

            End Try

 

        End Function

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' Assists smooth creation of SQLite Parameters.

    ''' </summary>

    Public Class ParameterHelper

 

#Region " Methods "

 

        ''' <summary>

        ''' Compresses creation of a Sql Parameter into a single line of code

        ''' </summary>

        ''' <param name="strName">The Parameter name.</param>

        ''' <param name="objValue">The Parameter value.</param>

        ''' <param name="msdtType">The Parameter data type.</param>

        ''' <returns>A SQLiteParameter object.</returns>

        ''' <exception cref="BadParameterException">This exception is thrown if

        ''' the parameter creation process fails for any reason.</exception>

        Public Shared Function CreateParameter(ByVal strName As String, ByVal objValue As Object, ByVal msdtType As DbType) As SQLiteParameter

 

            Dim mspParam As SQLiteParameter

 

            Try

 

                mspParam = New SQLiteParameter(strName, objValue)

                mspParam.DbType = msdtType

 

                Return mspParam

 

            Catch ex As Exception

 

                Throw New BadParameterException(ex)

 

            End Try

 

        End Function

 

#End Region

 

    End Class

 

End Namespace

 

Namespace DMFL.Data.SQLite.DAL.Exceptions

 

    ''' <summary>

    ''' This exception class is raised if the sql command fails.

    ''' </summary>

    Public Class BadCommandException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the BadCommandException class.

        ''' </summary>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal eInnerException As Exception)

 

            MyBase.New("The command failed - see InnerException for more details.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if a parameter creation method fails.

    ''' </summary>

    Public Class BadParameterException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the BadParameterException class.

        ''' </summary>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal eInnerException As Exception)

 

            MyBase.New("The parameter creation failed - see InnerException for more details.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if a data conversion method fails.

    ''' </summary>

    Public Class ConversionFailureException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the ConversionFailureException class.

        ''' </summary>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal eInnerException As Exception)

 

            MyBase.New("The data conversion failed - see InnerException for more details.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if a command returns a null datareader.

    ''' </summary>

    Public Class NullDataReaderException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the NullDataReaderException class.

        ''' </summary>

        Public Sub New()

 

            MyBase.New("Command execution failed to return a non-Null SQLiteDataReader.")

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if a command returns a null dataset.

    ''' </summary>

    Public Class NullDataSetException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the NullDataSetException class.

        ''' </summary>

        Public Sub New()

 

            MyBase.New("Command execution failed to return a non-Null DataSet")

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if an provided external data connection

    ''' is Nothing.

    ''' </summary>

    Public Class NullExternalConnectionException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the NullExternalConnectionException class.

        ''' </summary>

        Public Sub New()

 

            MyBase.New("The supplied external connection is Nothing.")

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if the sql command fails.

    ''' </summary>

    Public Class UnableToDeleteException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the UnableToDeleteException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table for which

        ''' the delete failed.</param>

        Public Sub New(ByVal strTableName As String)

 

            Me.New(strTableName, Nothing)

 

        End Sub

 

        ''' <summary>

        ''' Constructor for the UnableToDeleteException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table for which

        ''' the delete failed.</param>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal strTableName As String, ByVal eInnerException As Exception)

 

            MyBase.New("An attempt to Delete a row from table '" & strTableName & "' failed.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if the sql command fails.

    ''' </summary>

    Public Class UnableToInsertException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the UnableToInsertException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table into which

        ''' the insert failed.</param>

        Public Sub New(ByVal strTableName As String)

 

            Me.New(strTableName, Nothing)

 

        End Sub

 

        ''' <summary>

        ''' Constructor for the UnableToInsertException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table into which

        ''' the insert failed.</param>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal strTableName As String, ByVal eInnerException As Exception)

 

            MyBase.New("An attempt to Insert a row into table '" & strTableName & "' failed.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if a connection to the database cannot

    ''' be opened.

    ''' </summary>

    Public Class UnableToOpenConnectionException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the UnableToOpenConnectionException class.

        ''' </summary>

        Public Sub New()

 

            Me.New(Nothing)

 

        End Sub

 

        ''' <summary>

        ''' Constructor for the UnableToOpenConnectionException class.

        ''' </summary>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal eInnerException As Exception)

 

            MyBase.New("Unable to open the Sql connection.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if the sql command fails.

    ''' </summary>

    Public Class UnableToUpdateException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the UnableToUpdateException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table for which

        ''' the update failed.</param>

        Public Sub New(ByVal strTableName As String)

 

            Me.New(strTableName, Nothing)

 

        End Sub

 

        ''' <summary>

        ''' Constructor for the UnableToUpdateException class.

        ''' </summary>

        ''' <param name="strTableName">The name of the table for which

        ''' the update failed.</param>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal strTableName As String, ByVal eInnerException As Exception)

 

            MyBase.New("An attempt to Update a row in table '" & strTableName & "' failed.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

    ''' <summary>

    ''' This exception class is raised if an otherwise unhandled exception occurs.

    ''' </summary>

    Public Class UnhandledException

        Inherits Exception

 

#Region " Constructors "

 

        ''' <summary>

        ''' Constructor for the UnhandledException class.

        ''' </summary>

        ''' <param name="eInnerException">More specific information

        ''' about the exception.</param>

        Public Sub New(ByVal eInnerException As Exception)

 

            MyBase.New("An unhandled exception occurred - see InnerException for more information.", eInnerException)

 

        End Sub

 

#End Region

 

    End Class

 

End Namespace

Please note - code is supplied untested and without warranty - if it's badly bust, let me know and I'll fix it! Similarly, if you fix something in it, please let me know. Obviously, if you're dumb enough to throw this straight into a business-critical app, your liability is entirely your own.

Example usage:

        Dim rhHelper As New ReaderHelper("data source=C:\Documents and Settings\Me\My Documents\TestDb.s3db;")

        Dim sdrReader As SQLiteDataReader = Nothing

 

        Try

 

            sdrReader = rhHelper.ExecuteReader("SELECT Id, Text FROM TestTable Where Id=@Id", _

                                               CommandBehavior.Default, _

                                               CreateParameter("@Id", 1, DbType.Int32))

            While sdrReader.Read

                Dim intId As Int32 = Convert.ToInt32(sdrReader("Id"))

                Dim strText As String = sdrReader("Text").ToString

                Console.WriteLine(intId.ToString + ": " + strText)

            End While

 

        Catch ex As Exception

 

            Throw ex

 

        Finally

 

            If Not sdrReader Is Nothing Then

                sdrReader.Close()

                sdrReader = Nothing

            End If

 

            rhHelper.Connection.Close()

            rhHelper = Nothing

 

        End Try

Will you be my friend? Maybe...

Last week a friend of mine sent me a link to an article at the Times Online. In "Will you be my friend? Er, no.", consultant and writer Kate Alvarez attempts to convince us that

...the days of the Facebook phenomenon are numbered. Imperceptibly, there has been a shift in the zeitgeist and it’s no longer considered cool to share photographs and silly videos, do endless quizzes, promote favourite movies, books and music and send cute little icons to your friends as gifts.

This position appears to have been taken almost unilaterally, with little research outside of personal experience. While I am not unsympathetic towards Ms Alvarez's tale of having her identity "stolen" (although I suspect that "identity theft" is perhaps an overstatement, that devalues the experience of those unfortunate enough to truly undergone such), I fail to see how this, coupled with a couple of examples of badly-behaved people behaving badly adds up to "the zeitgeist".

Ms. Alvarez is clearly arguing from a position where, having had a negative experience and having only scratched the surface of social networking, she has gained correspondingly little from it. I myself (and I'm sure this is true for many) have had a much richer experience; the majority of those on my modest "friends list" are people that I see, work and play with regularly and often; I use it to organise climbing trips and classic car meets; to stay abreast of current events, both in the world and in the lives of my friends; my clients use Facebook in preference to email to contact me to enquire after my availability; co-workers (past and present) use it to seek my assistance with issues arising in their projects; music that I've composed has been picked up for play by internet radio, thanks in part to my MySpace page.

None of which, however, addresses an important and central point: that social networking, like any other web technology, will be re-purposed by its users to suit themselves, often in directions or with consequences that even the creators of the technology failed to foresee. Facebook began, let's not forget, as a tool exclusively for American college students - who amongst its early users would have anticipated its now almost global reach? eBay began as an online jumble-sale - and yet so many people now make their living from eBay stores that governments get concerned about lost tax revenue.

Ironically perhaps, I "met" the friend that sent me Ms. Alvarez's article via UseNet, an early collaborative tool that was soon re-purposed by its users into a simple form of social networking. Being early-adopters perhaps makes us less distracted by the "15 pixels of fame" and more imaginative as to the wider uses of the technology.

You don't have to look far to see examples of this. This morning, BBC News carried an article detailing the on-line resources available for tracking the landfall of Hurricane Gustav on the Louisiana coast. In addition to the "official" federal- or local-government and NGO sites, the resources listed included Twitter (a social micro-blogging tool), any number of blogs, GoogleMaps mash-ups and, of course, Facebook. A similar phenomenon occurred during the October 2007 wildfires in Southern California, with tech-savvy residents tracking the fire-front, co-ordinating evacuation efforts and assisting in identifying relief resources and available evacuation centres, all through the use of freely-available social networking tools. As a consequence, in the US, Federal agencies such as FEMA are reported to be very interested in the use of such technologies in their disaster-management and mitigation work. It seems that any group or organisation, from classic car clubs up to major political parties, can see the opportunities here.

So, sorry Kate Alvarez, but I very much suspect that Facebook and the whole breadth of the social networking arena are very much here to stay. However, your guess as to what its ultimate shape and use will turn out to be is, for the time being, as good as mine.

Number one in the world, baby!

A couple of interesting bits of search engine news - two of my sites currently rank number one in the world on Google.

Redpoint Climbing Centre has achieved top ranking for its brand keyword, and my own site is ranking number one for one of the recruitment consultants' favourite terms.

And these sites were deployed before I'd even started to delve into the clever parts of SEO!

My targets for the first quarter of 2009 are to build on these successes, and promote Redpoint to number one in all relevant searches - it's currently coming in second to an older, more established competitor, but that's going to change.

More sneaking around...

I recently wrote about a technique for de-bloating your pages with stealthy controls. Well, as with most of my (allegedly) bright ideas, it turns out that this one didn't quite run as expected.

It's not that there's anything wrong with the principal of the thing - it's just that it doesn't work for all of the basic asp.net controls, as I discovered this morning with the ListBox control; even if you destroy the control's id just prior to adding attributes to render, it still insists on adding a default id of "ctl00" to the render.

Not to worry, there's always a solution, in this case a combination of the stealthed controls code and my previously-posted page render interruption code:

Option Explicit On

Option Strict On

 

''' <summary>

''' "Stealthed" because it doesn't write a client id to the page and disables viewstate,

''' but you can still access its properties programatically

''' </summary>

''' <remarks></remarks>

Public Class StealthListBox

    Inherits ListBox

 

#Region " Methods "

 

    ''' <summary>

    ''' Renders the control to the specified HTML writer.

    ''' </summary>

    ''' <param name="writer">The HtmlTextWriter object that receives the control content.</param>

    ''' <remarks></remarks>

    Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)

 

        Try

 

            Dim swHtml As New IO.StringWriter()

            Dim htwHtml As New HtmlTextWriter(swHtml)

 

            MyBase.Render(htwHtml)

 

            Dim strHtml As String = swHtml.ToString

 

            ' replace id and name attributes in the rendered control

            strHtml = Regex.Replace(strHtml, "id=""(.*?)""", String.Empty, RegexOptions.IgnoreCase And RegexOptions.CultureInvariant)

            strHtml = Regex.Replace(strHtml, "name=""(.*?)""", String.Empty, RegexOptions.IgnoreCase And RegexOptions.CultureInvariant)

 

            writer.Write(strHtml)

 

        Catch ex As Exception

 

            Throw ex

 

        End Try

 

    End Sub

 

#End Region

 

#Region " Event Handlers "

 

    ''' <summary>

    ''' Occurs when the server control is initialized, which is the first step in its lifecycle.

    ''' </summary>

    ''' <param name="sender">The source of the event.</param>

    ''' <param name="e">An EventArgs object containing further information about the event.</param>

    ''' <remarks></remarks>

    Private Sub StealthListBox_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

 

        EnableViewState = False

 

    End Sub

 

#End Region

 

End Class

Optimizing Page Load Time

The Head of Online at my current contract brought this article to my attention. This should so be required reading for every web developer on the planet.

It kind of suggests that my gut feeling for site design/architecture has been right all along - lazy serving, few files, as much static content as possible, and maybe even (stand by: herewith follows heresy) offline publishing might be winning ideas after all.

Which, strangely enough, is how I was building websites in 1996...

CSS Cheat Sheet

Neat crib sheet for CSS. Get it at http://www.addedbytes.com/cheat-sheets/css-cheat-sheet/.

Lazy is efficient

Only serve what you absolutely have to serve... if you code to the assumption that you want your servers to be as lazy and indolent as they can possibly be, you'll end up with a pretty efficient site.

For example, today I had to make some data available to a set of JavaScript functions that refresh the items in a set of drop-downs; the value selected in each dropdown affects what's available to select in the other two (there are three of them altogether). The existing solution on the website was a (to my mind) unnecessarily complex solution involving AJAX controls and lots of round-trips to the server and the Sql Server.

Oh, and by the way... these things appear on every single page in the site, and are, of course, too slow/inaccesible/invisible to SEO, etc.

But... the data that populates these things, it's not that dynamic. Okay it does change, so we do need to maintain a route back to our Db Server to refresh the data... but not on every page load and postback, surely? I remember when we set these things up I had to implement some interesting caching just to get the round-trip time down to something that we could live with... and it's just masses and masses of bandwidth for what is, honestly, a frill.

So... a new solution is sought. There's talk of using a different AJAX toolkit, of using JQuery... but it can be so much simpler than that, surely?

Here's what I came up with, a truly old-school solution that makes great use of lazy serving, in that we only hit the Db when we absolutely must, having only hit the cache for the data when we absolutely had to, having only round-tripped to the web server from the client when we absolutely had to:

• All of the data that the drop-downs require is produced as one big string containing JavaScript variable definitions; this is created by a stored procedure on the sql server
• This data is cached, with a sql dependency against the underlying tables. If the data in the Sql Server changes, the cache will be expired immediately.
• We serve the data as an aspx file with a content-type of "application/x-javascript". This file is GZip-ed; all of the data consumed by the 3 drop-downs comes to 4kb, one-eighth of the size of the library of scripts used by the AJAX controls that we're replacing.
• When we get a request for the data file, we check the date and time that we last cached the data; if it's not changed, we don't serve anything other than a 304 header.
• If the data's changed, we re-cache it, update our reference date and serve it.
• All of the work that used to be performed by AJAX round-trips is now carried out by a handful of tiny JavaScript functions on the client; if the browser has JavaScript disabled (yeah, right!), that's OK too, since we populated the dropdowns with a set of static items when the page was loaded (and guess what - the underlying data for these is in sql-dependent cache too).

Old-school is efficient - and while there's a place for AJAX controls, weigh up the round-trip and supporting-file costs before you deploy it, it's not necessarily the solution you want. The approach that I've taken here is extremely effective for serving any often-hit but infrequently-refreshed data, such as RSS feeds.

Here's some code, the Render method of the control that pumps out the JavaScript data file, demonstrating sql-dependent caching, GZip compression and smart use of the 304 header. I've tweaked it a bit to make it more generic, but you'll have to imagine that the data-provider layer is there!

    Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)

 

        Try

 

            ' check whether we have the data in cache

            If Page.Cache.Item("SomeData") Is Nothing Then

                ' populate the cache from the db only when we have to; make the cache sql-dependent

                Dim acgDepend As New AggregateCacheDependency

                acgDepend.Add(New SqlCacheDependency("MyDatabase", "MyTable"))

                Page.Cache.Insert("SomeData", New MyDataProviderLib.SomeDataObject().Value, acgDepend)

                Page.Cache.Insert("SomeDataLastCached", DateTime.Now)

            End If

 

            ' get the last cache date from the cache

            Dim dtLastCached As DateTime = DirectCast(Page.Cache.Item("SomeDataLastCached"), DateTime)

 

            ' get the incoming headers

            Dim strIncomingETag As String = Page.Request.Headers("If-None-Match")

            Dim strIncomingLastModified As String = Page.Request.Headers("If-Modified-Since")

 

            ' clear the response

            Page.Response.Clear()

 

            ' gzip encode the output

            If Not Page.Request.Headers("Accept-Encoding") Is Nothing Then

                If Page.Request.Headers("Accept-Encoding").ToLower.Contains("gzip") Then

                    Page.Response.Filter = New System.IO.Compression.GZipStream(Page.Response.Filter, IO.Compression.CompressionMode.Compress)

                    Page.Response.AppendHeader("Content-Encoding", "gzip")

                ElseIf Page.Request.Headers("Accept-Encoding").ToLower.Contains("deflate") Then

                    Page.Response.Filter = New System.IO.Compression.DeflateStream(Page.Response.Filter, IO.Compression.CompressionMode.Compress)

                    Page.Response.AppendHeader("Content-Encoding", "deflate")

                End If

            End If

 

            ' set up the outgoing headers with the date and time the cache was last updated; we'll

            ' compare these against the request

            Dim strLastModified As String = dtLastCached.ToString("r", System.Globalization.DateTimeFormatInfo.InvariantInfo) ' IETF (RFC)1123 date/time

            Dim strETag As String = """" + dtLastCached.ToString("s", System.Globalization.DateTimeFormatInfo.InvariantInfo) + """" ' ISO 8601 sortable date/time, in quotes

            Page.Response.AppendHeader("ETag", strETag)

            Page.Response.AppendHeader("Last-Modified", strLastModified)

 

            ' check the etag then the last modified

            If String.Compare(strIncomingETag, strETag) = 0 Then

 

                ' incoming and outgoing headers are the same; no change to the data

                Page.Response.StatusCode = 304

 

            ElseIf String.Compare(strIncomingLastModified, strLastModified) = 0 Then

 

                ' incoming and outgoing headers are the same; no change to the data

                Page.Response.StatusCode = 304

 

            Else

 

                ' headers differ; write the data out, only getting data from the cache when we absolutely have to.

                Page.Response.ContentType = "application/x-javascript"

                Page.Response.Write(Page.Cache.Item("SomeData").ToString)

 

            End If

 

            ' end the response

            Page.Response.End()

 

        Catch ex As Exception

 

            Throw ex

 

        End Try

 

    End Sub