Database Helper Class Library to Ease Database Operation

• Download source - 385.33 KB
• Download demo project - 46.75 KB

Introduction

Database Helper Class Library is a set of class library that helps to make operation of accessing database easier. By using the class library, life will be easier as you can almost select data, insert data, update data, or delete data from database even without writing a single line of SQL statement. As a database application developer, there are 2 tedious tasks, which are the main source of headaches. One is the business logic and another one is the effort of repeating the same code snippets while accessing database to perform SQL statements. The database helper class library will solve the second headache and make developers have more time to focus on business logic.

This class library is not the object mapping stuff that generates various classes for corresponding tables nor stuff that generate data access layer classes automatically. It is a set of classes that can be set to perform database operation on a table very easily. It is very suitable to be used in small and medium scale database application development.

Background

With 4 years of experience as a database application developer, I found that the repetitious block of codes when executing every SQL statement is very time consuming. As a result, I tried to minimize this tedious process and started surfing the net. I found many answers, some of them are:

• Object Mapping - Tools that will generate classes for every corresponding table. I think this is suitable for large scale project only.
• Data Access Layer - Tools that will generate data access layer automatically. Again, I think it is useful in large scale projects only. Example is LLBLGen.
• Data Component - Component that tightly integrates with frond end component and can be used to update a table easily. Example is TTable component in Delphi.

Based on the answers I got, I think there is a need to create a class library that is similar to data component as mentioned above but will not integrate tightly with interface. Based on Data Access Application Block as released by Microsoft Patterns and Practices (I don't want to use data access component in Enterprise Library as it is too heavy weight), LLBLGen version 1 (free for version 1) and some of my brain juice, I had churned out this Database Helper Class Library which has a feature of TTable component and other more useful features. This class library is very useful for backend processing and front end table maintenance task in small and medium scale project.

Using the Code

For Database Helper Class Library, the most useful classes are SqlTableHelper, SqlHelper, SqlDataSetHelper and SqlConnectionProvider. SqlTableHelper is a class to help to perform database operation on a table without writing a single line of SQL statement. SqlHelper is to help to execute other customized SQL statements easily. SqlDataSetHelper is a class related to database relationship and makes retrieving related data row easier. At last, SqlConnectionProvider is a class that provides database connection and can span among 1 or more SqlTableHelpers and SqlHelpers. This make control of database transaction among several tables easier.

SqlTableHelper Samples

Select Samples

To select some rows from a table based on SelectCriteria property.

DataTable tblData;

//It is good to clear table helper instance's data table first b4
//start select any data
mobjTableHelper.Data.Clear();

//Get Sort criteria to apply to select action
if (mtxtSort.Text.Length > 0)
{
    mobjTableHelper.Sort = mtxtSort.Text.Trim();
}

//select some rows based on select criteria and return a data table instance
//e.g. ItemName = 'Pen'
mobjTableHelper.SelectCriteria = mtxtSelectCriteria.Text;
tblData = mobjTableHelper.SelectSome();
mdgrData.DataSource = tblData;

Insert Samples

To insert a row into a table.

//retrieve data table instance from table helper to prepare for insertion
DataTable tblData = mobjTableHelper.Data;
tblData.Rows.Clear();

//prepare new row to insert
DataRow row = tblData.NewRow();
row["ItemName"] = mtxtItemNameInsert.Text.Trim();
row["ItemPrice"] = Convert.ToDecimal(mtxtItemPriceInsert.Text);
row["ItemOnHand"] = Convert.ToInt32(mtxtItemOnHandInsert.Text);
tblData.Rows.Add(row);

mobjCnnProvider.OpenConnection();
mobjTableHelper.Insert();
mobjCnnProvider.CloseConnection();

Update Samples

To update some rows in a table based on UpdateCriteria property.

DataTable tblData;
DataRow row;

//update some recs for item price field ONLY
mobjTableHelper.FieldsToUpdate = "ItemPrice";
mobjTableHelper.Compile();

//must clear the records first to avoid confusion with any previous selection
tblData = mobjTableHelper.Data;
tblData.Rows.Clear();

//prepare row for update using filter expression
row = tblData.NewRow();
row["ItemPrice"] = Convert.ToDecimal(mtxtItemPriceUpdate.Text);
tblData.Rows.Add(row);

//update using primary key
mobjTableHelper.CriteriaType = DBCriteria.UseFilterExpression;
mobjTableHelper.UpdateCriteria = mtxtFilterExpUpd.Text.Trim();

mobjCnnProvider.OpenConnection();
mobjTableHelper.Update();
mobjCnnProvider.CloseConnection();

MessageBox.Show(String.Format("{0} rec is updated.", mobjTableHelper.RowsAffected));

Delete Samples

To delete some rows in a table based on DeleteCriteria property.

DataTable tblData;
DataRow row;

//clear records in data table instance first to avoid 
//confusion with previous selection
tblData = mobjTableHelper.Data;
tblData.Rows.Clear();

//delete using filter expression
mobjTableHelper.CriteriaType = DBCriteria.UseFilterExpression;
mobjTableHelper.DeleteCriteria = mtxtFilterExpDel.Text.Trim();

mobjCnnProvider.OpenConnection();
mobjTableHelper.Delete();
mobjCnnProvider.CloseConnection();

MessageBox.Show(String.Format("{0} rec is deleted.", mobjTableHelper.RowsAffected)); 

Important Things to Note

For SqlTableHelper instance, Compile method must be called at least 1 time before any database operation can be performed. If FieldToSelect and/or FieldToUpdate property value is reset, Compile method must be recalled so that new fields to select and/or update can work.

SqlHelper Samples

SqlHelper is a class that is used to perform customized SQL statements, e.g. stored procedure that cannot be done through SqlTableHelper class.

ExecuteNonQuery Samples

The example shown is to execute stored procedure sp_UpdItem.

int iItemID;
double dblItemPrice;
int iItemOnHand;
int iItemOnHand3x;

int iRetVal;
Hashtable hstOutput;
int iRowsAffected;
StringBuilder strbTemp;

strbTemp = new StringBuilder(100);

iItemID = Convert.ToInt32(mtxtItemID.Text.Trim());
dblItemPrice = Convert.ToDouble(mtxtItemPrice.Text.Trim());
iItemOnHand = Convert.ToInt32(mtxtItemOnHand.Text.Trim());
iItemOnHand3x = 0;

//update using stored procedure and 1 type of ExecuteNonQuery overloads
//you can try other overloads that achieve same result
mobjSqlHelper.ExecuteNonQuery
	("sp_UpdItem", iItemID, dblItemPrice, iItemOnHand, iItemOnHand3x);

//display executing result
iRetVal = mobjSqlHelper.ReturnValue;
hstOutput = mobjSqlHelper.OutputValue;
iRowsAffected = mobjSqlHelper.RowsAffected;

strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
if (hstOutput != null && hstOutput.Count > 0) 
{
    strbTemp.Append("Output Value: \n");
    foreach (DictionaryEntry entry in hstOutput)
    {
        strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
    }
}
strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

MessageBox.Show(strbTemp.ToString());

ExecuteDataset Samples

The example shown is to execute stored procedure sp_SelItem.

string strItemName;
int iRetVal;
Hashtable hstOutput;
int iRowsAffected;
StringBuilder strbTemp;

DataSet dsData;
DataTable tblData;
DataRow row;

strbTemp = new StringBuilder(100);

strItemName = mtxtItemName.Text.Trim();

//prepare row for selecting purpose
tblData = new DataTable();
tblData.Columns.Add("strItemName", typeof (string));
row = tblData.NewRow();
row["strItemName"] = strItemName;

//select using stored procedure and 1 type of ExecuteDataset overloads
//you can try other overloads that achieve same result
dsData = mobjSqlHelper.ExecuteDatasetTypedParams("sp_SelItem", row);

//display executing result
iRetVal = mobjSqlHelper.ReturnValue;
hstOutput = mobjSqlHelper.OutputValue;
iRowsAffected = mobjSqlHelper.RowsAffected;

strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
if (hstOutput != null && hstOutput.Count > 0) 
{
    strbTemp.Append("Output Value: \n");
    foreach (DictionaryEntry entry in hstOutput)
    {
        strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
    }
}
strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

MessageBox.Show(strbTemp.ToString());

//display return dataset value
strbTemp.Remove(0, strbTemp.Length);
tblData = dsData.Tables[0];
foreach (DataRow rowData in tblData.Rows)
{
    foreach (DataColumn col in tblData.Columns)
    {
        strbTemp.Append(col.ColumnName + ": " + rowData[col.ColumnName] + "\n");
    }
}

MessageBox.Show(this, strbTemp.ToString(), "DataSet result");

ExecuteXmlReader Samples

The example is to execute customized Select SQL statement.

string strItemName;
int iRetVal;
Hashtable hstOutput;
int iRowsAffected;

string strSQL;
StringBuilder strbTemp;

XmlReader xmlr;
SqlParameter[] apar = new SqlParameter[1];

strbTemp = new StringBuilder(100);

strItemName = mtxtItemName.Text.Trim();

//prepare sql parameters for updating purpose
apar[0] = new SqlParameter
	("@strItemName", SqlDbType.NVarChar, 50, ParameterDirection.Input,
    false, 0, 0, "", DataRowVersion.Default, strItemName);
strSQL = "SELECT * FROM x_Item WHERE ItemName = @strItemName FOR XML AUTO";

//select using inline SQL and 1 type of ExecuteDataset overloads
//you can try other overloads that achieve same result
xmlr = mobjSqlHelper.ExecuteXmlReader(CommandType.Text, strSQL, apar);

//display executing result
iRetVal = mobjSqlHelper.ReturnValue;
hstOutput = mobjSqlHelper.OutputValue;
iRowsAffected = mobjSqlHelper.RowsAffected;

strbTemp.Append("Return Value: " + mobjSqlHelper.ReturnValue + "\n");
if (hstOutput != null && hstOutput.Count > 0) 
{
    strbTemp.Append("Output Value: \n");
    foreach (DictionaryEntry entry in hstOutput)
    {
        strbTemp.Append(entry.Key + ": " + entry.Value + "\n");
    }
}
strbTemp.Append("Rows Affected: " + iRowsAffected + "\n");

MessageBox.Show(strbTemp.ToString());

//display return dataset value
strbTemp.Remove(0, strbTemp.Length);
while (xmlr.Read())
{
    strbTemp.Append(xmlr.ReadOuterXml() + "\n");
}

MessageBox.Show(this, strbTemp.ToString(), "XML Reader result");

SqlDataSetHelper Samples

You can use SqlDataSetHelper.FillParents method or SqlDataSetHelper.FillChilds method which is static to retrieve all related tables' rows once all the parent or child relationships are defined. Example is as follows:

int iSelected;
string strSelected;

//add foreign keys, apply same concept for parent relationship situation
mobjOrderHeaderTblHelper.ChildRelations.Clear();
mobjOrderDetailsTblHelper.ChildRelations.Clear();
mobjOrderHeaderTblHelper.AddForeignKeys
	(mobjOrderDetailsTblHelper, new string[] {"OrderDetailsOrderHeaderID"});
//add foreign relationship explicitly as foreign key in x_Item not reference primary key
//in x_OrderDetails
mobjOrderDetailsTblHelper.ChildRelations.Add("x_OrderDetailsFKx_Item|ItemID", 
    mobjOrderDetailsTblHelper, mobjItemTblHelper,
    new DataColumn[] {mobjOrderDetailsTblHelper.Data.Columns["OrderDetailsItemID"]},
    new DataColumn[] {mobjItemTblHelper.Data.Columns["ItemID"]});
    
//construct beginning row to retrieve child rows
iSelected = Convert.ToInt32(mdgrOrderHeader[mdgrOrderHeader.CurrentRowIndex, 0]);
Console.WriteLine("Order Header ID: " + iSelected);
strSelected = String.Format("OrderHeaderID = {0}", iSelected);

//clear data before retrieve foreign/child records
mobjOrderHeaderTblHelper.Data.Clear();
mobjOrderDetailsTblHelper.Data.Clear();
mobjItemTblHelper.Data.Clear();

mobjCnnProvider.OpenConnection();
mobjCnnProvider.BeginTransaction();

//retrieve all related child/foreign rows based on select criteria for top table helper.
//use FillParents for retrieving all related parent rows case
SqlDataSetHelper.FillChilds(mobjOrderHeaderTblHelper, strSelected);

mobjCnnProvider.CommitTransaction();
mobjCnnProvider.CloseConnection();

mdgrOrderHeader.DataSource = mobjOrderHeaderTblHelper.Data;
mdgrOrderDetails.DataSource = mobjOrderDetailsTblHelper.Data;
mdgrItem.DataSource = mobjItemTblHelper.Data;

Points of Interest

When I developed this class library, I learnt that object mapping and auto-generated data access layer method are useful only for large scale projects where there are many programmers for the project. For small and medium scale projects where there are 1 or 2 developers normally, why not we consider every table as an object. With the concept - a table is an object; I developed the SqlTableHelper class.

History

• 03/25/2007: Original article
• 04/14/2007: Updated demo zip file