Enterprise Library2.0（1）：Data Access Application Block学习-阿里云开发者社区

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

一．改进

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

Database db = DatabaseFactory.CreateDatabase();

DBCommandWrapper dbCommand = db.GetStoredProcCommandWrapper( " GetProductsByCategory " );

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

DataSet productDataSet = db.ExecuteDataSet(dbCommand);

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

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetStoredProcCommand( " GetProductsByCategory " );

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

DataSet productDataSet = 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()

{

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

}

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

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

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

}

4 ．参数处理

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了。

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

本文转自lihuijun51CTO博客，原文链接： http://blog.51cto.com/terrylee/67623 ，如需转载请自行联系原作者

Enterprise Library2.0（1）：Data Access Application Block学习

热门文章

最新文章

相关电子书