65.9K
CodeProject 正在变化。 阅读更多。
Home

简化数据库操作的数据库助手类库

falconsoon
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.09/5 (9投票s)

2007年3月24日

CPOL

4分钟阅读

viewsIcon

94473

downloadIcon

3012

简化数据库操作的数据库助手类库

引言

数据库助手类库是一组类库，可以简化数据库访问操作。通过使用这些类库，生活将变得更轻松，因为您几乎可以在不编写任何 SQL 语句的情况下从数据库中选择数据、插入数据、更新数据或删除数据。作为数据库应用程序开发人员，有两项繁琐的任务，它们是主要的问题来源。一是业务逻辑，二是访问数据库执行 SQL 语句时重复编写相同代码片段的努力。数据库助手类库将解决第二个问题，让开发人员有更多时间专注于业务逻辑。

此类库不是用于为相应表生成各种类的对象映射之类的东西，也不是用于自动生成数据访问层类的东西。它是一组类，可以轻松地配置它们来执行对表的数据库操作。它非常适合用于中小型数据库应用程序开发。

背景

凭借作为数据库应用程序开发人员的 4 年经验，我发现执行每个 SQL 语句时重复的代码块非常耗时。因此，我试图尽量减少这个繁琐的过程，并开始在网上搜索。我找到了许多答案，其中一些是

  • 对象映射 - 用于为每个相应表生成类的工具。我认为这只适用于大型项目。
  • 数据访问层 - 用于自动生成数据访问层的工具。同样，我认为这只在大型项目中有用。例如 LLBLGen。
  • 数据组件 - 与前端组件紧密集成，可用于轻松更新表的组件。例如，Delphi 中的 TTable 组件。

基于我得到的答案，我认为有必要创建一个类似于上述数据组件的类库，但它不会与界面紧密集成。基于 Microsoft Patterns and Practices 发布的数据访问应用程序块（我不想使用 Enterprise Library 中的数据访问组件，因为它太笨重了），LLBLGen 版本 1（版本 1 免费）以及我的一些思考，我开发了这个数据库助手类库，它具有 TTable 组件的功能以及其他更有用的功能。此类库对于中小型项目的后端处理和前端表维护任务非常有用。

Using the Code

对于数据库助手类库，最有用的类是 SqlTableHelper、SqlHelper、SqlDataSetHelper 和 SqlConnectionProvider。SqlTableHelper 类可以帮助执行表上的数据库操作，而无需编写任何 SQL 语句。SqlHelper 用于轻松执行其他自定义 SQL 语句。SqlDataSetHelper 类与数据库关系相关，可以更容易地检索相关数据行。最后，SqlConnectionProvider 类提供数据库连接，并且可以在一个或多个 SqlTableHelper 和 SqlHelper 之间共享。这使得跨多个表的数据库事务控制更加容易。

SqlTableHelper 示例

选择示例

根据 SelectCriteria 属性从表中选择一些行。

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;

插入示例

向表中插入一行。

//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();

更新示例

根据 UpdateCriteria 属性更新表中的一些行。

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));

删除示例

根据 DeleteCriteria 属性删除表中的一些行。

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));

重要提示

对于 SqlTableHelper 实例，在执行任何数据库操作之前，必须至少调用一次 Compile 方法。如果重置了 FieldToSelect 和/或 FieldToUpdate 属性的值，则必须重新调用 Compile 方法，以便新的要选择和/或更新的字段能够生效。

SqlHelper 示例

SqlHelper 类用于执行自定义 SQL 语句，例如，无法通过 SqlTableHelper 类执行的存储过程。

ExecuteNonQuery 示例

示例显示执行存储过程 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 示例

示例显示执行存储过程 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 示例

示例是执行自定义 Select SQL 语句。

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 示例

在定义了所有父或子关系后，您可以使用 SqlDataSetHelper.FillParents 方法或 SqlDataSetHelper.FillChilds 方法（它是 static 的）一次性检索所有相关表的行。示例如下：

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;

关注点

在开发此类库时，我了解到对象映射和自动生成的数据访问层方法仅适用于大型项目，这些项目通常有许多程序员。对于通常只有一两个开发人员的中小型项目，为什么不将每个表视为一个对象呢？基于“表是一个对象”的概念，我开发了 SqlTableHelper 类。

历史

  • 2007/03/25：原始文章
  • 2007/04/14：更新了演示 zip 文件
© . All rights reserved.