Enterprise Library2.0(1):Data Access Application Block学习...

来自 : terrylee.cnblogs.com/archive/2 发布时间：2021-03-25

Data Access Application Block提供了通用的数据访问的功能，随着2.0版本的推出有了很大变化。

一．改进

在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象，而在DAAB2.0里面移除了DBCommandWrapper类，用ADO.NET2.0里面的DBCommand类代替实现类似的功能，这样使得DAAB跟我们的.NET类库的结合更加紧密，回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的代码：

Databasedb=DatabaseFactory.CreateDatabase();

DBCommandWrapperdbCommand=db.GetStoredProcCommandWrapper(\"GetProductsByCategory\");

dbCommand.AddInParameter(\"CategoryID\",DbType.Int32,Category);

DataSetproductDataSet=db.ExecuteDataSet(dbCommand);

而用了新的DBCommand类之后则变成了：

Databasedb=DatabaseFactory.CreateDatabase();

DbCommanddbCommand=db.GetStoredProcCommand(\"GetProductsByCategory\");

db.AddInParameter(dbCommand,\"CategoryID\",DbType.Int32,Category);

DataSetproductDataSet=db.ExecuteDataSet(dbCommand);

数据库连接字符串在我们基于数据库的开发永远是少不了的，但是在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连接字符串却是不能共享的，它们分别保存在不同的位置。而在2.0的Data Access Application Block使用了ADO.NET2.0里面 connectionStrings 配置区，这样带来的一个好处是连接字符串可以在Application Block和自定义的.NET类之间共享使用该配置区，如：

connectionStrings
add
name=\"DataAccessQuickStart\"
providerName=\"System.Data.SqlClient\"
connectionString=\"server=(local)\\SQLEXPRESS;database=EntLibQuickStarts;IntegratedSecurity=true\"/
/connectionStrings

在.NET2.0下，泛型编程已经成为了一个核心，而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase，但是如果我们需要使用其他的像DB2等数据库时，就需要用到GenericDatabase，它可以用于任何.NET类库中的数据提供者，包括OdbcProvider和OleDbProvider。

二．使用示例

DAAB2.0的配置非常简单，主要有以下几方面的配置：

配置连接字符串

配置默认数据库

添加相关的命名空间：

usingMicrosoft.Practices.EnterpriseLibrary.Data;
usingSystem.Data;

使用Data Access Application Block进行数据的读取和操作，一般分为三步：

1．创建Database对象

2．提供命令参数，如果需要的话

3．执行命令

下面分别看一下DataAccessQuickStart中提供的一些例子：

执行静态的SQL语句

publicstringGetCustomerList()
{
//创建Database对象
Databasedb=DatabaseFactory.CreateDatabase();
//使用SQL语句创建DbCommand对象
stringsqlCommand=\"SelectCustomerID,Name,Address,City,Country,PostalCode\"+
\"FromCustomers\";
DbCommanddbCommand=db.GetSqlStringCommand(sqlCommand);

StringBuilderreaderData=newStringBuilder();

//调用ExecuteReader方法
using(IDataReaderdataReader=db.ExecuteReader(dbCommand))
{
while(dataReader.Read())
{
//Getthevalueofthe\'Name\'columnintheDataReader
readerData.Append(dataReader[\"Name\"]);
readerData.Append(Environment.NewLine);
}
}

returnreaderData.ToString();
}

执行存储过程并传递参数，返回DataSet

publicDataSetGetProductsInCategory(intCategory)
{
//CreatetheDatabaseobject,usingthedefaultdatabaseservice.The
//defaultdatabaseserviceisdeterminedthroughconfiguration.
Databasedb=DatabaseFactory.CreateDatabase();

stringsqlCommand=\"GetProductsByCategory\";
DbCommanddbCommand=db.GetStoredProcCommand(sqlCommand);

//Retrieveproductsfromthespecifiedcategory.
db.AddInParameter(dbCommand,\"CategoryID\",DbType.Int32,Category);

//DataSetthatwillholdthereturnedresults
DataSetproductsDataSet=null;

productsDataSet=db.ExecuteDataSet(dbCommand);

//Note:connectionwasclosedbyExecuteDataSetmethodcall

returnproductsDataSet;
}

利用DataSet更新数据

publicintUpdateProducts()
{
//CreatetheDatabaseobject,usingthedefaultdatabaseservice.The
//defaultdatabaseserviceisdeterminedthroughconfiguration.
Databasedb=DatabaseFactory.CreateDatabase();

DataSetproductsDataSet=newDataSet();

stringsqlCommand=\"SelectProductID,ProductName,CategoryID,UnitPrice,LastUpdate\"+
\"FromProducts\";
DbCommanddbCommand=db.GetSqlStringCommand(sqlCommand);

stringproductsTable=\"Products\";

//Retrievetheinitialdata
db.LoadDataSet(dbCommand,productsDataSet,productsTable);

//Getthetablethatwillbemodified
DataTabletable=productsDataSet.Tables[productsTable];

//AddanewproducttoexistingDataSet
DataRowaddedRow=table.Rows.Add(newobject[]{DBNull.Value,\"Newproduct\",11,25});

//Modifyanexistingproduct
table.Rows[0][\"ProductName\"]=\"Modifiedproduct\";

//EstablishourInsert,Delete,andUpdatecommands
DbCommandinsertCommand=db.GetStoredProcCommand(\"AddProduct\");
db.AddInParameter(insertCommand,\"ProductName\",DbType.String,\"ProductName\",DataRowVersion.Current);
db.AddInParameter(insertCommand,\"CategoryID\",DbType.Int32,\"CategoryID\",DataRowVersion.Current);
db.AddInParameter(insertCommand,\"UnitPrice\",DbType.Currency,\"UnitPrice\",DataRowVersion.Current);

DbCommanddeleteCommand=db.GetStoredProcCommand(\"DeleteProduct\");
db.AddInParameter(deleteCommand,\"ProductID\",DbType.Int32,\"ProductID\",DataRowVersion.Current);

DbCommandupdateCommand=db.GetStoredProcCommand(\"UpdateProduct\");
db.AddInParameter(updateCommand,\"ProductID\",DbType.Int32,\"ProductID\",DataRowVersion.Current);
db.AddInParameter(updateCommand,\"ProductName\",DbType.String,\"ProductName\",DataRowVersion.Current);
db.AddInParameter(updateCommand,\"LastUpdate\",DbType.DateTime,\"LastUpdate\",DataRowVersion.Current);

//SubmittheDataSet,capturingthenumberofrowsthatwereaffected
introwsAffected=db.UpdateDataSet(productsDataSet,\"Products\",insertCommand,updateCommand,
deleteCommand,UpdateBehavior.Standard);

returnrowsAffected;

}

通过ID获取记录详细信息

publicstringGetProductDetails(intproductID)
{
//CreatetheDatabaseobject,usingthedefaultdatabaseservice.The
//defaultdatabaseserviceisdeterminedthroughconfiguration.
Databasedb=DatabaseFactory.CreateDatabase();

stringsqlCommand=\"GetProductDetails\";
DbCommanddbCommand=db.GetStoredProcCommand(sqlCommand);

//Addparamters
//Inputparameterscanspecifytheinputvalue
db.AddInParameter(dbCommand,\"ProductID\",DbType.Int32,productID);
//Outputparametersspecifythesizeofthereturndata
db.AddOutParameter(dbCommand,\"ProductName\",DbType.String,50);
db.AddOutParameter(dbCommand,\"UnitPrice\",DbType.Currency,8);

db.ExecuteNonQuery(dbCommand);

//Rowofdataiscapturedviaoutputparameters
stringresults=string.Format(CultureInfo.CurrentCulture,\"{0},{1},{2:C}\",
db.GetParameterValue(dbCommand,\"ProductID\"),
db.GetParameterValue(dbCommand,\"ProductName\"),
db.GetParameterValue(dbCommand,\"UnitPrice\"));

returnresults;
}

以XML格式返回数据

publicstringGetProductList()
{
//UseanameddatabaseinstancethatreferstoaSQLServerdatabase.
SqlDatabasedbSQL=DatabaseFactory.CreateDatabase()asSqlDatabase;

//Use\"FORXMLAUTO\"tohaveSQLreturnXMLdata
stringsqlCommand=\"SelectProductID,ProductName,CategoryID,UnitPrice,LastUpdate\"+
\"FromProductsFORXMLAUTO\";
DbCommanddbCommand=dbSQL.GetSqlStringCommand(sqlCommand);

XmlReaderproductsReader=null;
StringBuilderproductList=newStringBuilder();

try
{
productsReader=dbSQL.ExecuteXmlReader(dbCommand);

//IteratethroughtheXmlReaderandputthedataintoourresults.
while(!productsReader.EOF)
{
if(productsReader.IsStartElement())
{
productList.Append(productsReader.ReadOuterXml());
productList.Append(Environment.NewLine);
}
}
}
finally
{
//ClosetheReader.
if(productsReader!=null)
{
productsReader.Close();
}

//Explicitlyclosetheconnection.Theconnectionisnotclosed
//whentheXmlReaderisclosed.
if(dbCommand.Connection!=null)
{
dbCommand.Connection.Close();
}
}

returnproductList.ToString();
}

使用事务

publicboolTransfer(inttransactionAmount,intsourceAccount,intdestinationAccount)
{
boolresult=false;

//CreatetheDatabaseobject,usingthedefaultdatabaseservice.The
//defaultdatabaseserviceisdeterminedthroughconfiguration.
Databasedb=DatabaseFactory.CreateDatabase();

//Twooperations,onetocreditanaccount,andonetodebitanother
//account.
stringsqlCommand=\"CreditAccount\";
DbCommandcreditCommand=db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(creditCommand,\"AccountID\",DbType.Int32,sourceAccount);
db.AddInParameter(creditCommand,\"Amount\",DbType.Int32,transactionAmount);

sqlCommand=\"DebitAccount\";
DbCommanddebitCommand=db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(debitCommand,\"AccountID\",DbType.Int32,destinationAccount);
db.AddInParameter(debitCommand,\"Amount\",DbType.Int32,transactionAmount);

using(DbConnectionconnection=db.CreateConnection())
{
connection.Open();
DbTransactiontransaction=connection.BeginTransaction();

try
{
//Creditthefirstaccount
db.ExecuteNonQuery(creditCommand,transaction);
//Debitthesecondaccount
db.ExecuteNonQuery(debitCommand,transaction);

//Committhetransaction
transaction.Commit();

result=true;
}
catch
{
//Rollbacktransaction
transaction.Rollback();
}
connection.Close();

returnresult;
}
}

三．常见功能

1．创建Database对象

创建一个默认的Database对象

DatabasedbSvc=DatabaseFactory.CreateDatabase();

默认的数据库在配置文件中：

dataConfigurationdefaultDatabase=\"DataAccessQuickStart\"/

创建一个实例Database对象

//Useanameddatabaseinstancethatreferstoanarbitrarydatabasetype,
//whichisdeterminedbyconfigurationinformation.
DatabasemyDb=DatabaseFactory.CreateDatabase(\"DataAccessQuickStart\");

创建一个具体的类型的数据库对象

//CreateaSQLdatabase.
SqlDatabasedbSQL=DatabaseFactory.CreateDatabase(\"DataAccessQuickStart\")asSqlDatabase;

2．创建DbCommand对象

静态的SQL语句创建一个DbCommand

Databasedb=DatabaseFactory.CreateDatabase();
stringsqlCommand=\"SelectCustomerID,LastName,FirstNameFromCustomers\";
DbCommanddbCommand=db.GetSqlStringCommand(sqlCommand);

存储过程创建一个DbCommand

Databasedb=DatabaseFactory.CreateDatabase();
DbCommanddbCommand=db.GetStoredProcCommand(\"GetProductsByCategory\");

3．管理对象

当连接对象打开后，不需要再次连接

Databasedb=DatabaseFactory.CreateDatabase();
stringsqlCommand=\"SelectProductID,ProductNameFromProducts\";
DbCommanddbCommand=db.GetSqlStringCommand(sqlCommand);
//Noneedtoopentheconnection;justmakethecall.
DataSetcustomerDataSet=db.ExecuteDataSet(dbCommand);

使用Using及早释放对象

Databasedb=DatabaseFactory.CreateDatabase();
DbCommanddbCommand=db.GetSqlStringCommand(\"SelectName,AddressFromCustomers\");
using(IDataReaderdataReader=db.ExecuteReader(dbCommand))
{
//Processresults
}

4．参数处理

Database类提供了如下的方法，用于参数的处理：

AddParameter. 传递参数给存储过程
AddInParameter. 传递输入参数给存储过程
AddOutParameter. 传递输出参数给存储过程
GetParameterValue. 得到指定参数的值
SetParameterValue. 设定参数值

使用示例如下：

Databasedb=DatabaseFactory.CreateDatabase();
stringsqlCommand=\"GetProductDetails\";
DbCommanddbCommand=db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand,\"ProductID\",DbType.Int32,5);
db.AddOutParameter(dbCommand,\"ProductName\",DbType.String,50);
db.AddOutParameter(dbCommand,\"UnitPrice\",DbType.Currency,8);

Databasedb=DatabaseFactory.CreateDatabase();
DbCommandinsertCommand=db.GetStoredProcCommand(\"AddProduct\");
db.AddInParameter(insertCommand,\"ProductName\",DbType.String,\"ProductName\",DataRowVersion.Current);
db.AddInParameter(insertCommand,\"CategoryID\",DbType.Int32,\"CategoryID\",DataRowVersion.Current);
db.AddInParameter(insertCommand,\"UnitPrice\",DbType.Currency,\"UnitPrice\",DataRowVersion.Current);

四．使用场景

DAAB2.0是对ADO.NET2.0的补充，它允许你使用相同的数据访问代码来支持不同的数据库，您通过改变配置文件就在不同的数据库之间切换。目前虽然只提供SQLServer和Oracle的支持，但是可以通过GenericDatabase和ADO.NET 2.0下的DbProviderFactory对象来增加对其他数据库的支持。如果想要编写出来的数据库访问程序具有更好的移植性，则DAAB2.0是一个不错的选择，但是如果您想要针对特定数据库的特性进行编程，就要用ADO.NET了。

参考：Enterprise Libaray –January 2006帮助文档及QuickStart