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;Integrated Security=true" />
</connectionStrings>
在.NET2.0下，泛型編程已經成為了一個核心，而2.0版的DAAB中也新增了一個GenericDatabase對象。DAAB中雖然已經包含了SqlDatabase和OrcaleDatabase，但是如果我們需要使用其他的像DB2等數據庫時，就需要用到GenericDatabase，它可以用於任何.NET類庫中的數據提供者，包括OdbcProvider和OleDbProvider。
二．使用示例
DAAB2.0的配置非常簡單，主要有以下幾方面的配置：
配置連接字符串
配置默認數據庫
添加相關的命名空間：
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
使用Data Access Application Block進行數據的讀取和操作，一般分為三步：
1．創建Database對象
2．提供命令參數，如果需要的話
3．執行命令
下面分別看一下DataAccessQuickStart中提供的一些例子：
執行靜態的SQL語句
public string GetCustomerList()執行存儲過程並傳遞參數，返回DataSet
{
// 創建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;
}
利用DataSet更新數據
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;
}
通過ID獲取記錄詳細信息
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;
}
以XML格式返回數據
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;
}
}
三．常見功能
1．創建Database對象
創建一個默認的Database對象
Database dbSvc = DatabaseFactory.CreateDatabase();
默認的數據庫在配置文件中：
<dataConfiguration defaultDatabase="DataAccessQuickStart" />
創建一個實例Database對象
// 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;
2．創建DbCommand對象
靜態的SQL語句創建一個DbCommand
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select CustomerID, LastName, FirstName From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
存儲過程創建一個DbCommand
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory");
3．管理對象
當連接對象打開後，不需要再次連接
Database db = DatabaseFactory.CreateDatabase();4．參數處理
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);
使用Using及早釋放對象
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers");
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
// Process results
}
Database類提供了如下的方法，用於參數的處理：
AddParameter. 傳遞參數給存儲過程
AddInParameter. 傳遞輸入參數給存儲過程
AddOutParameter. 傳遞輸出參數給存儲過程
GetParameterValue. 得到指定參數的值
SetParameterValue. 設定參數值
使用示例如下：
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);
DAAB2.0是對ADO.NET2.0的補充，它允許你使用相同的數據訪問代碼來支持不同的數據庫，您通過改變配置文件就在不同的數據庫之間切換。目前雖然只提供SQLServer和Oracle的支持，但是可以通過GenericDatabase和ADO.NET 2.0下的DbProviderFactory對象來增加對其他數據庫的支持。如果想要編寫出來的數據庫訪問程序具有更好的移植性，則DAAB2.0是一個不錯的選擇，但是如果您想要針對特定數據庫的特性進行編程，就要用ADO.NET了。
