今天学习了Enterprise Library2.0的Data Access Application Block,Data Access Application Block提供了通用的数据访问的功能,随着2.0版本的推出有了很大变化。俺就多写了对SQL和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接俺的代码吧。
一.改进
在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类之间共享使用该配置区,如:
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
connectionStrings
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
add
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
name
="DataAccessQuickStart"
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
providerName
="System.Data.SqlClient"
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
connectionString
="server=(local)\SQLEXPRESS;database=EntLibQuickStarts;Integrated Security=true"
/>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
connectionStrings
>
在.NET2.0下,泛型编程已经成为了一个核心,而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase,但是如果我们需要使用其他的像DB2等数据库时,就需要用到GenericDatabase,它可以用于任何.NET类库中的数据提供者,包括OdbcProvider和OleDbProvider。
二.使用示例
DAAB2.0的配置非常简单,主要有以下几方面的配置:
配置连接字符串
配置默认数据库
添加相关的命名空间:
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
using
Microsoft.Practices.EnterpriseLibrary.Data;
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
using
System.Data;
使用Data Access Application Block进行数据的读取和操作,一般分为三步:
1.创建Database对象
2.提供命令参数,如果需要的话
3.执行命令
下面分别看一下DataAccessQuickStart中提供的一些例子:
执行静态的SQL语句
执行存储过程并传递参数,返回DataSet
利用DataSet更新数据
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
public
int
UpdateProducts()
![ExpandedBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
DataSet productsDataSet = new DataSet();
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +
"From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
string productsTable = "Products";
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Retrieve the initial data
db.LoadDataSet(dbCommand, productsDataSet, productsTable);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Get the table that will be modified
DataTable table = productsDataSet.Tables[productsTable];
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Add a new product to existing DataSet
![ExpandedSubBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
DataRow addedRow = table.Rows.Add(new object[]
{DBNull.Value, "New product", 11, 25});
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Modify an existing product
table.Rows[0]["ProductName"] = "Modified product";
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// 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);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
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);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Submit the DataSet, capturing the number of rows that were affected
int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand,
deleteCommand, UpdateBehavior.Standard);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
return rowsAffected;
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
}
通过ID获取记录详细信息
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
public
string
GetProductDetails(
int
productID)
![ExpandedBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// 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);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
db.ExecuteNonQuery(dbCommand);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// 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"));
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
return results;
}
以XML格式返回数据
使用事务
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
public
bool
Transfer(
int
transactionAmount,
int
sourceAccount,
int
destinationAccount)
![ExpandedBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
bool result = false;
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Two operations, one to credit an account, and one to debit another
// account.
string sqlCommand = "CreditAccount";
DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount);
db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
sqlCommand = "DebitAccount";
DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount);
db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
using (DbConnection connection = db.CreateConnection())
![ExpandedSubBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
try
![ExpandedSubBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Credit the first account
db.ExecuteNonQuery(creditCommand, transaction);
// Debit the second account
db.ExecuteNonQuery(debitCommand, transaction);
![InBlock.gif](http://www.web3.cn/Images/OutliningIndicators/InBlock.gif)
// Commit the transaction
transaction.Commit();
result = true;
}
catch
![ExpandedSubBlockStart.gif](http://www.web3.cn/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Rollback transaction
transaction.Rollback();
}
connection.Close();
return result;
}
}
三.常见功能
1.创建Database对象
创建一个默认的Database对象
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database dbSvc
=
DatabaseFactory.CreateDatabase();
默认的数据库在配置文件中:
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
dataConfiguration
defaultDatabase
="DataAccessQuickStart"
/>
创建一个实例Database对象
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
//
Use a named database instance that refers to an arbitrary database type,
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
//
which is determined by configuration information.
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database myDb
=
DatabaseFactory.CreateDatabase(
"
DataAccessQuickStart
"
);
创建一个具体的类型的数据库对象
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
//
Create a SQL database.
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
SqlDatabase dbSQL
=
DatabaseFactory.CreateDatabase(
"
DataAccessQuickStart
"
)
as
SqlDatabase;
2.创建DbCommand对象
静态的SQL语句创建一个DbCommand
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database db
=
DatabaseFactory.CreateDatabase();
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
string
sqlCommand
=
"
Select CustomerID, LastName, FirstName From Customers
"
;
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DbCommand dbCommand
=
db.GetSqlStringCommand(sqlCommand);
存储过程创建一个DbCommand
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database db
=
DatabaseFactory.CreateDatabase();
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DbCommand dbCommand
=
db.GetStoredProcCommand(
"
GetProductsByCategory
"
);
3.管理对象
当连接对象打开后,不需要再次连接
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database db
=
DatabaseFactory.CreateDatabase();
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
string
sqlCommand
=
"
Select ProductID, ProductName From Products
"
;
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DbCommand dbCommand
=
db.GetSqlStringCommand(sqlCommand);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
//
No need to open the connection; just make the call.
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DataSet customerDataSet
=
db.ExecuteDataSet(dbCommand);
使用Using及早释放对象
4.参数处理
Database类提供了如下的方法,用于参数的处理:
AddParameter. 传递参数给存储过程
AddInParameter. 传递输入参数给存储过程
AddOutParameter. 传递输出参数给存储过程
GetParameterValue. 得到指定参数的值
SetParameterValue. 设定参数值
使用示例如下:
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database db
=
DatabaseFactory.CreateDatabase();
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
string
sqlCommand
=
"
GetProductDetails
"
;
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DbCommand dbCommand
=
db.GetStoredProcCommand(sqlCommand);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
db.AddInParameter(dbCommand,
"
ProductID
"
, DbType.Int32,
5
);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
db.AddOutParameter(dbCommand,
"
ProductName
"
, DbType.String,
50
);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
db.AddOutParameter(dbCommand,
"
UnitPrice
"
, DbType.Currency,
8
);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
Database db
=
DatabaseFactory.CreateDatabase();
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
DbCommand insertCommand
=
db.GetStoredProcCommand(
"
AddProduct
"
);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
db.AddInParameter(insertCommand,
"
ProductName
"
, DbType.String,
"
ProductName
"
, DataRowVersion.Current);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
db.AddInParameter(insertCommand,
"
CategoryID
"
, DbType.Int32,
"
CategoryID
"
, DataRowVersion.Current);
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
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
好,看到这里俺应该基本懂得使用了,俺就动手试一下SQL和ACCESS数据库自由切换的方法,因俺平时的习惯是使用vb.net写东西,所以只写出vb.net的代码出来,有兴趣的自己改成C#好了,看以下html代码:
![ContractedBlock.gif](http://www.web3.cn/Images/OutliningIndicators/ContractedBlock.gif)
<%
...
@ Page Language="VB" AutoEventWireup="false" CodeFile="sql.aspx.vb" Inherits="sql"
%>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
html
xmlns
="http://www.w3.org/1999/xhtml"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
head
runat
="server"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
title
>
web3.cn——SQL、Access数据库自由切换
</
title
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
head
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
body
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
form
id
="form1"
runat
="server"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
div
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
asp:GridView
ID
="GridView1"
runat
="server"
AutoGenerateColumns
="False"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
Columns
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
asp:BoundField
DataField
="id"
HeaderText
="id"
SortExpression
="id"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
HeaderStyle
BackColor
="Silver"
/>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
asp:BoundField
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
asp:BoundField
DataField
="provinceID"
HeaderText
="provinceID"
SortExpression
="provinceID"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
HeaderStyle
BackColor
="Silver"
/>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
asp:BoundField
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
asp:BoundField
DataField
="province"
HeaderText
="provinceID"
SortExpression
="province"
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
<
HeaderStyle
BackColor
="Silver"
/>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
asp:BoundField
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
Columns
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
asp:GridView
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
div
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
form
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
body
>
![None.gif](http://www.web3.cn/Images/OutliningIndicators/None.gif)
</
html
>
vb.net代码:
以上代码不多,应该明白了吧,呵呵,只要把“dv
=
GetList_Access().DefaultView”换成“dv
=
GetList_SQL().DefaultView”即可换成了SQL的数据库了,简单吧。这里只给出一个思路,就看大家封装起来成更加简单易用的咯。