OleHelper for Microsoft Access (.MDB) in 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 sealed class OleHelper

{

    #region private utility methods & constructors

    private OleHelper() { }
    #endregion private utility methods & constructors

 

    #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 static DataSet ExecuteDataset(OleDbConnection connection, OleDbTransaction transaction, CommandType 

    commandType, string commandText)

    {

        //return ExecuteDataset(transaction, commandType, commandText);

        //create a command and prepare it for execution

        OleDbCommand cmd = new OleDbCommand();

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

        //create the DataAdapter & DataSet

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        DataSet ds = new DataSet();

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

        da.Fill(ds);

        cmd.Parameters.Clear();

        //return the dataset

        return ds;

    }

    #endregion ExecuteDataSet

    #region ExecuteNonQuery
    public static int ExecuteNonQuery(OleDbConnection connection, OleDbTransaction transaction, CommandType

    commandType, string commandText)

    {

        //create a command and prepare it for execution

        OleDbCommand cmd = new OleDbCommand();

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

        //finally, execute the command.

        int retval = cmd.ExecuteNonQuery();

        cmd.Parameters.Clear();

        return retval;

    }

    #endregion ExecuteNonQuery

    #region PrepareCommand
    private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction

    transaction, CommandType commandType, string commandText)

    {

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

        if (connection.State != ConnectionState.Open)

        {

            connection.Open();

        }

        //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 != null)

        {

            command.Transaction = transaction;

        }

        //set the command type

        command.CommandType = commandType;

        return;

    }

    #endregion PrepareCommand
}

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

//Page level declarations

OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\\Web\\DbHelper\\App_Data\\Category.mdb'");

OleDbTransaction objTrans = null;

int retInsVal = 0;

protected void btnAdd_Click(object sender, EventArgs e)

{

    //Here is how we can create object and pass different parameters to the method       

    if ((txtCatName.Text.Trim() != "") && (txtDescription.Text.Trim() != ""))

    {

        //do Db activity

        try

        {

            oleCon.Open();

            objTrans = oleCon.BeginTransaction();

            string strChkVal = "";

            if (chkStatus.Checked == true)

            {

                strChkVal = "Y";

            }

            else

            {

                strChkVal = "N";

            }

            retInsVal = DbHelper.OleHelper.ExecuteNonQuery(oleCon, objTrans, CommandType.Text, "INSERT INTO

            Tbl_Categories (catName,catDescription,addedBy,catStatus,sysTime) VALUES('" + txtCatName.Text.Replace("'", "''").

            Trim() + "','" + txtDescription.Text.Replace("'", "''").Trim() + "', 1,'" + strChkVal + "','" +

            DateTime.Now.ToLongDateString()+"')");

            objTrans.Commit();

            lblMessage.Text = "New record added successfully!";

        }

        catch

        {

            lblMessage.Text = "An error hsa occured while adding new category!";

            objTrans.Rollback();

        }

        finally

        {

            //free the resources

            oleCon.Close();

        }           

    }
}

I am sure this code will help developer for maintaining two-tier architecture where one can have this class as DataAcccess Layer and aspx files as presentation layer. You can add some more methods to this class like ExecuteScaler() etc. and can extend this article on some project.

Note: In this article I have my project location at "d:\\web\\...\\Category.mdb so in this case my connection becomes as:

OleDbConnection oleCon = new OleDbConnection( "Provider= Microsoft.Jet.OLEDB.4.0;Data Source='D:\\Web\\DbHelper\\App_Data\\Category.mdb'");

In your case it will differ, you can very well store this provider in web.config file or you can use Server.Mappath() to point your database location.