OleHelper for microsoft access using asp.net 2.0

This is just a thought of providing some code snippet for those who works on small project's and basically deals with Microsoft Access Database. You can use normal T-SQL statements with OleHelper class. It does not support for stored procedures.

Here is the detail code:

Public NotInheritable Class OleHelper

#Region "private utility methods & constructors"

    Private Sub New()

    End Sub

#End Region

#Region "ExecuteDataSet"

    ''' <summary>

    ''' Execute a OleCommand (that returns a resultset and takes no parameters) against the provided OleDbTransaction.

    ''' </summary>

    ''' <remarks>

    ''' e.g.:

    ''' DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");

    ''' </remarks>

    ''' <param name="transaction">a valid OleDBTransaction</param>

    ''' <param name="commandType">the CommandType (stored procedure, text, etc.)</param>

    ''' <param name="commandText">the stored procedure name or T-SQL command</param>

    ''' <returns>a dataset containing the resultset generated by the command</returns>

    Public Shared Function ExecuteDataset(ByVal connection As OleDbConnection, ByVal transaction As OleDbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As DataSet

        'return ExecuteDataset(transaction, commandType, commandText);

        'create a command and prepare it for execution

        Dim cmd As New OleDbCommand()

        PrepareCommand(cmd, connection, transaction, commandType, commandText)

        'create the DataAdapter & DataSet

        Dim da As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet()

        'fill the DataSet using default values for DataTable names, etc.

        da.Fill(ds)

        cmd.Parameters.Clear()

        'return the dataset

        Return ds

    End Function

#End Region

#Region "ExecuteNonQuery"

    Public Shared Function ExecuteNonQuery(ByVal connection As OleDbConnection, ByVal transaction As OleDbTransaction, ByVal commandType As CommandType, ByVal commandText As String) As Integer

        'create a command and prepare it for execution

        Dim cmd As New OleDbCommand()

        PrepareCommand(cmd, connection, transaction, commandType, commandText)

        'finally, execute the command.

        Dim retval As Integer = cmd.ExecuteNonQuery()

        cmd.Parameters.Clear()

        Return retval

    End Function

#End Region

#Region "PrepareCommand"

    Private Shared Sub PrepareCommand(ByVal command As OleDbCommand, ByVal connection As OleDbConnection, ByVal transaction As OleDbTransaction, ByVal commandType As CommandType, ByVal commandText As String)

        'if the provided connection is not open, we will open it

        If connection.State <> ConnectionState.Open Then

            connection.Open()

        End If

        'associate the connection with the command

        command.Connection = connection

        'set the command text (stored procedure name or SQL statement)

        command.CommandText = commandText

        'if we were provided a transaction, assign it.

        If transaction IsNot Nothing Then

            command.Transaction = transaction

        End If

        'set the command type

        command.CommandType = commandType

        Return

    End Sub

#End Region

End Class

In my project I have "Category.mdb" database and in this I have "Tbl_Categories" table which is as below:

catId                     AutoNumber
catName              Text
catDescription    Text
addedBy              Number
catStatus             Text
sysTime               Text

Where catId is a primary key.

On Default.aspx page I am having form which accepts the input values.

Here is a code which I can use to add new record by instantiating "OleHelper".