Enterprise Library2.0（1）：Data Access Application Block学习
本文转载自：http://www.cnblogs.com/terrylee/archive/2006/03/14/350026.html 作者：Terrylee 转载请注明该声明。
Database db
=
DatabaseFactory.CreateDatabase();
DBCommandWrapper dbCommand
=
db.GetStoredProcCommandWrapper(
"
GetProductsByCategory
"
);
dbCommand.AddInParameter(
"
CategoryID
"
, DbType.Int32, Category);
DataSet productDataSet
=
db.ExecuteDataSet(dbCommand);
Database db
=
DatabaseFactory.CreateDatabase();
DbCommand dbCommand
=
db.GetStoredProcCommand(
"
GetProductsByCategory
"
);
db.AddInParameter(dbCommand,
"
CategoryID
"
, DbType.Int32, Category);
DataSet productDataSet
=
db.ExecuteDataSet(dbCommand);
<
connectionStrings
>
<
add
name
="DataAccessQuickStart"
providerName
="System.Data.SqlClient"
connectionString
="server=(local)\SQLEXPRESS;database=EntLibQuickStarts;Integrated Security=true"
/>
</
connectionStrings
>
using
Microsoft.Practices.EnterpriseLibrary.Data;
using
System.Data;
public
string
GetCustomerList()
{
// 创建Database对象
Database db = DatabaseFactory.CreateDatabase();
// 使用SQL语句创建DbCommand对象
string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
"From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
StringBuilder readerData = new StringBuilder();
// 调用ExecuteReader方法
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
// Get the value of the 'Name' column in the DataReader
readerData.Append(dataReader["Name"]);
readerData.Append(Environment.NewLine);
}
}
return readerData.ToString();
}
public
DataSet GetProductsInCategory(
int
Category)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "GetProductsByCategory";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
// Retrieve products from the specified category.
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
// DataSet that will hold the returned results
DataSet productsDataSet = null;
productsDataSet = db.ExecuteDataSet(dbCommand);
// Note: connection was closed by ExecuteDataSet method call
return productsDataSet;
}
public
int
UpdateProducts()
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
DataSet productsDataSet = new DataSet();
string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +
"From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
string productsTable = "Products";
// Retrieve the initial data
db.LoadDataSet(dbCommand, productsDataSet, productsTable);
// Get the table that will be modified
DataTable table = productsDataSet.Tables[productsTable];
// Add a new product to existing DataSet
DataRow addedRow = table.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25});
// Modify an existing product
table.Rows[0]["ProductName"] = "Modified product";
// Establish our Insert, Delete, and Update commands
DbCommand insertCommand = 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);
DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
DbCommand updateCommand = 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);
// Submit the DataSet, capturing the number of rows that were affected
int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand,
deleteCommand, UpdateBehavior.Standard);
return rowsAffected;
}
public
string
GetProductDetails(
int
productID)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
// Add paramters
// Input parameters can specify the input value
db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
// Output parameters specify the size of the return data
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);
db.ExecuteNonQuery(dbCommand);
// Row of data is captured via output parameters
string results = string.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ",
db.GetParameterValue(dbCommand, "ProductID"),
db.GetParameterValue(dbCommand, "ProductName"),
db.GetParameterValue(dbCommand, "UnitPrice"));
return results;
}
public
string
GetProductList()
{
// Use a named database instance that refers to a SQL Server database.
SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;
// Use "FOR XML AUTO" to have SQL return XML data
string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +
"From Products FOR XML AUTO";
DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);
XmlReader productsReader = null;
StringBuilder productList = new StringBuilder();
try
{
productsReader = dbSQL.ExecuteXmlReader(dbCommand);
// Iterate through the XmlReader and put the data into our results.
while (!productsReader.EOF)
{
if (productsReader.IsStartElement())
{
productList.Append(productsReader.ReadOuterXml());
productList.Append(Environment.NewLine);
}
}
}
finally
{
// Close the Reader.
if (productsReader != null)
{
productsReader.Close();
}
// Explicitly close the connection. The connection is not closed
// when the XmlReader is closed.
if (dbCommand.Connection != null)
{
dbCommand.Connection.Close();
}
}
return productList.ToString();
}
public
bool
Transfer(
int
transactionAmount,
int
sourceAccount,
int
destinationAccount)
{
bool result = false;
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
// Two operations, one to credit an account, and one to debit another
// account.
string sqlCommand = "CreditAccount";
DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount);
db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);
sqlCommand = "DebitAccount";
DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount);
db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
// Credit the first account
db.ExecuteNonQuery(creditCommand, transaction);
// Debit the second account
db.ExecuteNonQuery(debitCommand, transaction);
// Commit the transaction
transaction.Commit();
result = true;
}
catch
{
// Rollback transaction
transaction.Rollback();
}
connection.Close();
return result;
}
}
Database dbSvc
=
DatabaseFactory.CreateDatabase();
<
dataConfiguration
defaultDatabase
="DataAccessQuickStart"
/>
//
Use a named database instance that refers to an arbitrary database type,
//
which is determined by configuration information.
Database myDb
=
DatabaseFactory.CreateDatabase(
"
DataAccessQuickStart
"
);
//
Create a SQL database.
SqlDatabase dbSQL
=
DatabaseFactory.CreateDatabase(
"
DataAccessQuickStart
"
)
as
SqlDatabase;
Database db
=
DatabaseFactory.CreateDatabase();
string
sqlCommand
=
"
Select CustomerID, LastName, FirstName From Customers
"
;
DbCommand dbCommand
=
db.GetSqlStringCommand(sqlCommand);
Database db
=
DatabaseFactory.CreateDatabase();
DbCommand dbCommand
=
db.GetStoredProcCommand(
"
GetProductsByCategory
"
);
Database db
=
DatabaseFactory.CreateDatabase();
string
sqlCommand
=
"
Select ProductID, ProductName From Products
"
;
DbCommand dbCommand
=
db.GetSqlStringCommand(sqlCommand);
//
No need to open the connection; just make the call.
DataSet customerDataSet
=
db.ExecuteDataSet(dbCommand);
Database db
=
DatabaseFactory.CreateDatabase();
DbCommand dbCommand
=
db.GetSqlStringCommand(
"
Select Name, Address From Customers
"
);
using
(IDataReader dataReader
=
db.ExecuteReader(dbCommand))
{
// Process results
}
Database db
=
DatabaseFactory.CreateDatabase();
string
sqlCommand
=
"
GetProductDetails
"
;
DbCommand dbCommand
=
db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand,
"
ProductID
"
, DbType.Int32,
5
);
db.AddOutParameter(dbCommand,
"
ProductName
"
, DbType.String,
50
);
db.AddOutParameter(dbCommand,
"
UnitPrice
"
, DbType.Currency,
8
);
Database db
=
DatabaseFactory.CreateDatabase();
DbCommand insertCommand
=
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);
Data Access Application Block提供了通用的数据访问的功能，随着2.0版本的推出有了很大变化。
一．改进
在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象，而在DAAB2.0里面移除了DBCommandWrapper类，用ADO.NET2.0里面的DBCommand类代替实现类似的功能，这样使得DAAB跟我们的.NET类库的结合更加紧密，回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的代码：
而用了新的DBCommand类之后则变成了：
数据库连接字符串在我们基于数据库的开发永远是少不了的，但是在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连接字符串却是不能共享的，它们分别保存在不同的位置。而在2.0的Data Access Application Block使用了ADO.NET2.0里面<connectionStrings>配置区，这样带来的一个好处是连接字符串可以在Application Block和自定义的.NET类之间共享使用该配置区，如：
在.NET2.0下，泛型编程已经成为了一个核心，而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase，但是如果我们需要使用其他的像DB2等数据库时，就需要用到GenericDatabase，它可以用于任何.NET类库中的数据提供者，包括OdbcProvider和OleDbProvider。
二．使用示例
DAAB2.0的配置非常简单，主要有以下几方面的配置：
配置连接字符串
配置默认数据库
添加相关的命名空间：
使用Data Access Application Block进行数据的读取和操作，一般分为三步：
1．创建Database对象
2．提供命令参数，如果需要的话
3．执行命令
下面分别看一下DataAccessQuickStart中提供的一些例子：
执行静态的SQL语句
执行存储过程并传递参数，返回DataSet
利用DataSet更新数据
通过ID获取记录详细信息
以XML格式返回数据
使用事务
三．常见功能
1．创建Database对象
创建一个默认的Database对象
默认的数据库在配置文件中：
创建一个实例Database对象
创建一个具体的类型的数据库对象
2．创建DbCommand对象
静态的SQL语句创建一个DbCommand
存储过程创建一个DbCommand
3．管理对象
当连接对象打开后，不需要再次连接
使用Using及早释放对象
4．参数处理
Database类提供了如下的方法，用于参数的处理：
AddParameter. 传递参数给存储过程
AddInParameter. 传递输入参数给存储过程
AddOutParameter. 传递输出参数给存储过程
GetParameterValue. 得到指定参数的值
SetParameterValue. 设定参数值
使用示例如下：
四．使用场景
DAAB2.0是对ADO.NET2.0的补充，它允许你使用相同的数据访问代码来支持不同的数据库，您通过改变配置文件就在不同的数据库之间切换。目前虽然只提供SQLServer和Oracle的支持，但是可以通过GenericDatabase和ADO.NET 2.0下的DbProviderFactory对象来增加对其他数据库的支持。如果想要编写出来的数据库访问程序具有更好的移植性，则DAAB2.0是一个不错的选择，但是如果您想要针对特定数据库的特性进行编程，就要用ADO.NET了。
参考：Enterprise Libaray –January 2006帮助文档及QuickStart