VB.NET & ASP.NET @ DMFL

Data Access Library for SQLite

2008-10-23T15:05:00.004+01:00

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...

2008-09-01T22:46:00.009+01:00

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!

2008-08-30T15:00:00.004+01:00

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...

2008-08-26T12:22:00.004+01:00

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

2008-08-20T14:39:00.002+01:00

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

2008-08-19T17:33:00.000+01:00

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

Lazy is efficient

2008-08-19T13:07:00.007+01:00

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

How to thoroughly break Visual Studio 2005

2008-08-19T12:30:00.002+01:00

This was ... entertainingly stupid of me, I suppose, but it's fun to find the limitations of the tools that you work with.

I decided to document a stack of work that I've been doing over the last two or three days, because I need to put it to one side for a little while and concentrate on some higher-priority stuff. So, I decide to write a quick html document, complete with code samples and sql scripts, in much the same manner as this blog is presented.

However, one of my scripts runs to about 3,000 lines of T-SQL (it applies a whole bunch of Db changes), and when it's converted to Html (using the excellent CopySourceAsHtml plug-in for VS2005) it gets truly bloaty. The document totals out at 560Kb, which to be fair, is pretty big for a html page.

But interestingly, it wasn't a problem, until I asked VS to "go format my html"... whereupon it ate my entire machine's resource, maxed out the CPU, and then curled up and died. Took a hard reboot to get my PC back.

Compressing your output

2008-08-18T17:28:00.003+01:00

Compressing your reponse output in ASP.NET/VB.NET is a highly desirable thing to do - especially if you don't have access to the IIS in order to switch this on (as you might encounter in some shared hosting scenarios).

This code fragment will enable GZIP or DEFLATE for the response stream. If you implement this in Global.asax for beginRequest, you'll compress all of the .aspx output from your site.

' 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

ClickOnce certificate error, VS2005

2008-08-14T09:13:00.002+01:00

Colleagues on my current contract hit an issue yesterday where the certificate on a ClickOnce deployment project expired; good old MS won't allow the current certificate to be extended, and issuing a new certificate would mean that every client would have to be re-installed from scratch, which kinda defeats the object of ClickOnce, surely?

Anyway, it turns out there's a fix, and it can be found here: KB925521.

And... it seems to actually work, too!

Interfering with the entire page output...

2008-08-12T14:51:00.002+01:00

So, one of the requirements on my current job is that all URIs in this particular site are a) unique and b) lower-case. Easy, you might say. But, the site contains thousands of internal hyperlinks, some of which are entered via a CMS; to go round manually fixing all of them would be a nightmare - and we'd never know if we'd got them all.

Enter this neato base class that your Master Pages can inherit, which gives you the opportunity to interfere with the output to the page just before it hits the response stream:

Option Explicit On
Option Strict On
 
Imports System.IO
Imports System.Text.RegularExpressions
 
''' <summary>
''' Provides a base class for all master pages in the application.
''' This enables us to manipulate the output from every page in the site.
''' </summary>
''' <remarks></remarks>
Public Class baseMasterPage
    Inherits MasterPage
 
#Region " Methods "
 
    ''' <summary>
    ''' Renders the page 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 StringWriter()
            Dim htwHtml As New HtmlTextWriter(swHtml)
 
            ' capture the rendered page into a string
            MyBase.Render(htwHtml)
            Dim strHtml As String = swHtml.ToString
 
 
            ' Do your "messing" here... examples activities include:
            '
            ' 1. Make the contents of all hrefs lower case
            ' 2. Rename a querystring parameter
            ' 3. Censor particular words or sentences
            ' 4. Change a css class name for another
            ' 5. Make all external hyperlinks open in a new browser
            ' 6. Inject tracking javascripts
            ' 7. Enforce self closing on tags that might be poorly formatted by legacy CMS
            ' .
            ' .
            ' etc. The list is probably endless - basically, anything that you want to do to your site that is 
            '
            '       a) "Shotgun", that is, it applies to EVERY page
            '           AND
            '       b) Can be implemented without too much overhead using simple string-handling techniques
            '
            ' ...can be done right here by mainpulating the strHtml variable.
            '
            ' Hint: Regular Expressions work wonders for this kind of thing...
 
 
            ' output the modified rendered page
            writer.Write(strHtml)
 
        Catch ex As Exception
 
            Throw ex
 
        End Try
 
    End Sub
 
#End Region
 
End Class

Tidy CheckBoxLists

2008-08-09T16:18:00.005+01:00

Although the built-in CheckBoxList control is very flexible, sometimes you just want one that'll drop a list of CheckBoxes on the page, without tables, and with an identical structure to a regular CheckBox (this saves having different variants of "checkbox" classes in CSS styling, for example). Here's an inherited class, PuristCheckBoxList, that does just that.

Option Explicit On

Option Strict On

''' <summary>

''' A CheckBoxList that renders it's checkboxes just like the regular ones, without tables.

''' </summary>

''' <remarks></remarks>

Public Class PuristCheckBoxList

    Inherits CheckBoxList

#Region " Properties "

    Private c_htwtItemWrapperTag As HtmlTextWriterTag = HtmlTextWriterTag.Span

    ''' <summary>

    ''' Gets/sets the ItemWrapperTag.

    ''' </summary>

    ''' <value>A System.Web.UI.HtmlTextWriterTag object.</value>

    ''' <returns>A System.Web.UI.HtmlTextWriterTag object.</returns>

    ''' <remarks></remarks>

    Public Property ItemWrapperTag() As HtmlTextWriterTag

Get

            Return c_htwtItemWrapperTag

        End Get

        Set(ByVal value As HtmlTextWriterTag)

            c_htwtItemWrapperTag = value

        End Set

    End Property

    Private c_strItemWrapperCssClass As String = "chkbox"

    ''' <summary>

    ''' Gets/sets the ItemWrapperCssClass.

    ''' </summary>

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

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

    ''' <remarks></remarks>

    Public Property ItemWrapperCssClass() As String

Get

            Return c_strItemWrapperCssClass

        End Get

        Set(ByVal value As String)

            c_strItemWrapperCssClass = value

        End Set

    End Property

#End Region

#Region " Event Handlers "

    ''' <summary>

    ''' Occurs after the Control object is loaded but prior to rendering.

    ''' </summary>

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

    ''' <param name="e">An EventArgs that contains additional information about the event.</param>

    ''' <remarks></remarks>

    Private Sub PuristCheckBoxList_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender

Try

            ' force a vertical list, no tables.

            Me.RepeatLayout = WebControls.RepeatLayout.Flow

            Me.RepeatColumns = 1

            Me.RepeatDirection = WebControls.RepeatDirection.Vertical

        Catch ex As Exception

            Throw ex

        End Try

    End Sub

#End Region

#Region " Methods "

    ''' <summary>

    ''' Renders an item in the list with the specified information.

    ''' </summary>

    ''' <param name="itemType">One of the ListItemType enumeration values. </param>

    ''' <param name="repeatIndex">An ordinal index that specifies the location of the item in the list control. </param>

    ''' <param name="repeatInfo">A RepeatInfo that represents the information used to render the item in the list.</param>

    ''' <param name="writer">A System.Web.UI.HtmlTextWriter that represents the output stream to render HTML content on the client.</param>

    ''' <remarks></remarks>

    Protected Overrides Sub RenderItem(ByVal itemType As System.Web.UI.WebControls.ListItemType, ByVal repeatIndex As Integer, ByVal repeatInfo As System.Web.UI.WebControls.RepeatInfo, ByVal writer As System.Web.UI.HtmlTextWriter)

Try

            ' render a span around the item

            If Not ItemWrapperCssClass = String.Empty Then

                writer.AddAttribute(HtmlTextWriterAttribute.Class, ItemWrapperCssClass)

            End If

            writer.RenderBeginTag(ItemWrapperTag)

            MyBase.RenderItem(itemType, repeatIndex, repeatInfo, writer)

            writer.RenderEndTag()

        Catch ex As Exception

            Throw ex

        End Try

    End Sub

#End Region

End Class

Note the properties that set the tag that wraps around each checkbox in the list. Feel free to change the default value for the tag and its css class to suit whatever you commonly wrap your checkboxes with.

Short is sweet

2008-08-09T14:03:00.002+01:00

If you're the kind of developer that pays attention to the resulting xhtml that your ASP.NET pages render, you'll have noticed how outrageously wasteful of bandwidth a page full of controls can be, particularly when using Master Pages and Templated ServerControls. ASP.NET has two nasty habits here: long default control ids, and the stitching-together of nested control ids. This can lead to control ids in your rendered xhtml such as:

"ctl00_ctl00_ContentPlaceHolder1_ctlSearch1_PlaceHolder1_txtSearchTerm"

So here's a tip. When you first create your master page, set the ContentPlaceHolder control's ID to something really short. Then, for subsequent controls in your tree, if the control doesn't need a meaningful name, don't give it one. Particularly with Templated controls, it's only the controls within the template that truly need a meaningful name - and even then, only if you intend to do anything with them. The above example cuts down to:

"ctl00_ctl00_cph0_s0_p0_txtSearchTerm"

Imagine applying this across your entire site, then imagine all of the views that each page will receive - do the math, and give yourself a pat on the back for the outrageous amount of bandwidth you just saved.

Here's an example of the kind of template I routinely deploy, which provides me with short, concise IDs:

<asp:Content ID="c0" ContentPlaceHolderID="cph0" runat="server">

    <cms:UserRecord ID="ur0" runat="server">

        <Template>

        Meaningfully-named controls appear here in the template

        </Template>

    </cms:UserRecord>

</asp:Content>

Remember that you really should give meaningful names to controls that you manipulate in code (otherwise your code becomes hard to read and maintain), but simple containers and placeholders - they're fair game for ruthless abbreviation!

The fine and foolish art of misdirection

2008-08-07T10:09:00.009+01:00

How often do you do this in your code?

Response.Redirect("someurl.aspx")

The site I'm currently working on is littered with these, because the front-end developer chose to use ASP LinkButtons instead of hyperlinks in quite large parts of the site. So, instead of just having a nice, sensible hyperlink on the page, there's a hyperlink that does a PostBack, and then some code behind that does a Response.Redirect to the place the link is supposed to go.

Now, quite apart from the huge page load/database traffic overhead involved in this, this is extremely poor practice - because what the browser sees is:

302: this page has temporarily moved; if you're google, you'd be better of disregarding this

instead of:

200: all is well with this page, please index me and come see all my lovely SEO features

So...

Don't replace the humble hyperlink with a LinkButton unless you've got a damn good reason to do so. Why go through all of the overhead of performing a PostBack just to go to another page???
If you really must perform a server-side redirect, make a decent job of it and return a 301 status code, so that search engine spiders know to index the resulting content.

Performing a well-formed 301, by the way:

Response.Status = "301 Permanently Moved"
Response.AppendHeader("Location", "someurl.aspx")

...or...

Response.StatusCode = 301
Response.RedirectLocation = "someurl.aspx"

You can also terminate the response to ensure that nothing else messes with it:

Response.End()

De-bloat your aspx pages with "stealthed" controls

2008-08-06T12:51:00.010+01:00

One of the most annoying things about ASP.NET, when attempting to develop anything like an efficient, clean, compliant site, is its insistence upon treating every control as though it's going to be used

With some dubiously clever javascript-y stuff on the client side
As a data-containing control that we're going to want to consume after a postback

This is hugely annoying, as in most cases we just want to render some stuff to the page - enough bloat, already!

It's safe to say that a lot of your controls probably shouldn't even be asp.net controls - why carry all the overhead of an asp:Hyperlink when a simple html anchor will do?

But, sometimes, we want to programatically inject some dynamism into our page as it's rendered, and then we end up using asp.net controls. If it's a big, complex page, perhaps with repeating content, viewstate can soon get out of hand. Just the sheer weight of control ids, unneccesarily rendered to the page, can cost us kilobits of bandwidth for no reason whatsoever, and all because ASP.NET's defaults are bad, and developers aren't shown a better way.

Below is an example of a "stealthed" control, in that it emits no client-side id and no viewstate, but it's still accessible programatically. The code speaks for itself. I'd suggest building a suite of these monkeys... stealthed Images, Hyperlinks, Labels... all the stuff that's never going to post back...

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 StealthHyperlink
    Inherits HyperLink
 
    ''' <summary>
    ''' Adds HTML attributes and styles that need to be rendered to the specified HtmlTextWriterTag.
    ''' </summary>
    ''' <param name="writer">A HtmlTextWriter that represents the output stream to render HTML content on the client. </param>
    ''' <remarks></remarks>
    Protected Overrides Sub AddAttributesToRender(ByVal writer As System.Web.UI.HtmlTextWriter)
 
        Try
 
            ' capture the ID to a temp store
            Dim strId As String = ID
 
            ' kill the control's ID for the time it takes to render attributes
            ID = String.Empty
            MyBase.AddAttributesToRender(writer)
 
            ' reinstate the ID
            ID = strId
 
        Catch ex As Exception
 
            Throw ex
 
        End Try
 
    End Sub
 
    ''' <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 StealthHyperlink_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
 
        EnableViewState = False
 
    End Sub
 
End Class

With thanks to Peter Bucher

Why do I always forget?

2008-08-04T11:18:00.005+01:00

So here I am, first day of my new contract at Travelsphere, staring at Visual Studio as it flatly refuses to open my web app. It worked last time I was here, so what's going on?

Of course, it's down to the fact that SP1 isn't installed for this version of VS2005. Web application projects were only re-introduced into Visual Studio 2005 at Service Pack 1, having been "superseded" by the Web Sites compilation model in the baseline release.

Cue much downloading and waiting...

So, for future reference, VS2005 Service Pack 1 is here, but it'll be traveling on my memory stick from now on, anyway.

Portable Apps

2008-08-01T12:38:00.003+01:00

I've finally caught up with the rest of the universe and started keeping my bookmarks on del.icio.us; well, it makes sense, what with working at lots of different places.

Also, I came across the excellent PortableApps suite, which sits very nicely on my USB memory stick and means that I can have my own FireFox, my own Thunderbird, etc. A really nicely made and presented piece of software.

IE6 CSS buffoonery

2008-07-25T11:58:00.007+01:00

I've been beating my head against a css difference between IE6 and FF for weeks, off and on. Today I finally nailed it.

The issue: IE6 is rendering a different font size to elements contained within div tags. Also, IE6 will not parse an over-ride of the the body tag's font size (which means my font-size-switcher won't work).

It turned out that the base class for the master page that's in common use here is doing some slightly dodgy re-writing of the page on render. I finally tracked down the issue by turning the code off and comparing the rendered output to when the code is in play. Although the code does some really neat things (ensures that self-closing tags are properly self closed, enforces lower-case attribute names, enforces a doc type), it was also adding this to the head of every document:


xml version="1.0" encoding="utf-8"

Although FF is perfectly happy with this, IE6 refuses to play nice, and fails to properly interpret the CSS cascade. With this line in the base class commented out, all is well (well, not quite - I now have to go through all of the CSS for the 6 sites that use this CMS and remove all of my dirty CSS hacks that made the thing vaguely presentable in the first place).

Moving on again...

2008-07-23T13:18:00.004+01:00

For the third time this year, I'm switching contracts absolutely back-to-back.

The first change this year was from Travelsphere to Next plc; I left Travelsphere on a Friday, interviewed at Next the following Monday, and started the job on the Tuesday.

After finishing at Next, I promised myself a break. I took 3 weeks off. The day I started looking for work, I got an interview with the NHS. The day after I interviewed, I started the job.

Yesterday, I took a call from an agent that I dealt with earlier this year - "what's your availability, can you come back to Travelsphere for 3 months?". Can I ever!

So, on the 1st August, I'll finish at the NHS - and on the following Monday I'll start at Trav (wonder if I can have my old desk back?).

S'good to be popular, but I'm really looking forwards to my two-week break in October!

The two most important lines of VB you'll ever write

2008-07-21T13:56:00.006+01:00

Here they are:

Option Explicit On
Option Strict On

...and they should appear at the top of every class, module, code-behind, control, etc that you write, no exceptions, always, always, always start with these two lines.

Why? Well, option explicit tells the compiler that you're going to explicitly declare every variable that you use. Option strict tells the compiler that you're not to be allowed to make any implicit casts, and that you'll remain type-safe throughout your code. A lot of developers don't bother with these two lines, on the basis that you can switch them on globally for your app and it's therefore not necessary.

I disagree with those guys.

I've worked at lots of places where, although there's a policy of globally setting explicit and strict, it either:

Doesn't get done
Gets subsequently turned off
Code gets copied from a strongly-typed, type-safe app into a new app, and subsequently mutates into unsafe garbage

Here's the bottom line: if you explicitly declare these options, they're far less likely to go awry at a later date. As a contractor, I regularly re-use classes of code. If I explicitly set these options in my code, I can be assured that my code will remain strong.

I've also experienced a couple of contracts where, once I've gone through the clients' existing apps and pasted these lines in, I've uncovered hundreds of errors and potential points-of-failure in the app - despite the current staff swearing that they always produce strongly-typed, type-safe code. The biggest culprits, through no fault of their own, are C# developers working in a VB environment - they forget (or were unaware) that although C# enforces strong typing and explicit declaration by default, VB doesn't.

Make a snippet out of these, and always, always use them.

Re-ranking with Sql Server 2005

2008-07-21T12:45:00.001+01:00

You'll often have a need to rank stuff in an application in a particular order, and allow the user to change that order (for example, pages in a website, blocks of content within a page, items within a list, photos in a gallery... y'get the idea). This gets particularly interesting when a user wants to be able to insert and delete stuff from said list, too.

My usual way of tackling this is to have a [Rank] column that contains a number in increments of 10. That way, if the user wants to insert an item before or after another one in the list, you just add or subtract 5 from the "reference" [Rank] and there y'go - job done. Of course, you're then going to want to re-rank your table frequently, otherwise the gaps will close up as new items are added, and holes will appear as items are deleted.

Here's a really neat way of doing this in Sql Server 2005, that you can save as a stored procedure and then trigger whenever a row inserted, updated or deleted - instant re-ranking, with absolutely no further programmer interference required:

UPDATE
   Pages
SET
   Pages.[Rank] = RankedPages.[Rank]
FROM
   (SELECT
       Url,
       (ROW_NUMBER() OVER (ORDER BY [Rank] ASC))
           * 10 AS [Rank]
   FROM
       Pages) RankedPages
WHERE
   Pages.Url = RankedPages.Url

In the above example, imagine a database for a Content Management System where we have webpages in a table called Pages, the primary key of which is the Url column. The [Rank] column states what order the pages appear in the website's sitemap. Our user has added a page (thus has added a row to the table). We want to take whatever values are in the [Rank] column, and re-rank them so that they're a nice, orderly 10... 20... 30... 40... etc. By using ROW_NUMBER() and a sort expression, we can generate the ordered [Rank] value, then use this to update the actual rows in the table. It's quick, it's easy and it's all in one hit.

Tools of the trade

2008-07-21T11:49:00.003+01:00

Here, in my opinion, is the minimum list of software that, as a VB.NET/ASP.NET developer, you should be toting on your PC:

The Pro edition of whatever release of Visual Studio you're using. Yes, there are the Express editions (and believe it or not, I've done a contract where Web Dev Express was the only tool available - with which to develop a CMS, for crying out loud...) Why would you not want to compile your code? Why would you not want to properly weaponise your libraries? Why would you not want to basically do the job you're paid to do - as though you're paid to do it?
The Express edition of Sql Server, and the Express edition of Management Studio. But get the one with the advanced services in. Why the express version? It's good enough, powerful enough, and it's free. What more d'you want?
MySql. It's free, and there's always someone who'll insist on using it over Sql Express, despite how bad an idea that might be. Better to be prepared and familiar with it, I reckon. Write yourself a data acces library for it that behaves just like your Sql Server one does, so that you can maintain your coding habits and snippets,
FireFox. It beats the living snot out of IE, and it probably always will, and it's miles better for debugging JS with. Make it your default browser, both in your OS and VS.
SnippetEditor. When you're cutting code, if you're not using a snippet approximately every 10 seconds, you should be. This tool makes the whole thing a lot easier.
BeyondCompare. Your designer tweaks a page and chucks it onto the server without telling you. Actually, he does this several times. And he changed some classes in the stylesheet. And some of the graphics changed, and some of 'em got renamed. Did he keep a change log? Does it matter, as long as you have BeyondCompare? Not one bit. Get it, and stop worrying about people messing with your stuff.
Adobe PhotoShop. Yeah, yeah, you're gonna give me a speech about FireWorks. This is still better. Deal with. And you get to play with designers who prefer Macs.
ColorCop - too simple to describe, enormously useful, go get. Now.
WinZip. Just because.
FileZilla. It works, it's free, you need it.
MS Office Pro. Make sure you get Visio. Because you will be writing some documentation, you will be drawing pictures for people, and you undoubtedly will be faced with porting some amateur's MS Access app at some point in your life (or even one of your own, from back when you didn't know any better).

Now, stick as much of that list as you can onto a big-assed memory stick and keep it with you at all times. Update it with new versions periodically. That's your toolbag - look after it and it'll see you through 99% of all your work situations. Let's face it, if you've got that lot on your workhorse machine, you can write anything else you need...

Finally - don't be tight. If it's not free, put your hand in your pocket and pay for the software you use. Get licensed.