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.
Join the conversation! Your thoughts help the community grow.