2 - Much ADO about Data Access: Using the Data Access Application Block

来源：互联网发布：淘宝不能货到付款编辑：程序博客网时间：2024/05/08 01:09

简介：

优势

无须重复代码

无须考虑数据库实现细节

无须修改代码仅需修改配置文件即实现数据库迁移，特殊用法除外

实现了数据库的简单访问

常用方法

ExecuteDataSet

LoadDataSet

UpdateDataSet

ExecuteReader

ExecuteNonQuery

ExecuteScalar

ExecuteSprocAccessor

ExecuteSqlStringAccessor

ExecuteXmlReader（sql server 专用）

CreateConnection

如何使用：

通过NuGet添加EnterpriseLibrary.Data至项目

支持sql server 数据库需要添加System.Data.SqlClient

支持Oracle 数据库需要添加System.Data.OracleClient.dll,官方不推荐使用，请至http://entlibcontrib.codeplex.com/查询相应可用驱动

添加配置文件：

<?xml version="1.0"?>

</configSections>

<dataConfigurationdefaultDatabase="ExampleDatabase"/>

</connectionStrings>

...</configuration>

创建数据库实例

DatabaseProviderFactory factory =new DatabaseProviderFactory()

Database defaultDB = factory.CreateDefault();

Database namedDB = factory.Create("ExampleDatabase");

转换为sql database实例

SqlDatabase sqlServerDB = factory.CreateDefault() as SqlDatabase;

无参获取行记录

using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))

{

DisplayRowValues(reader);

}

using (IDataReader reader = namedDB.ExecuteReader(CommandType.Text,"SELECT TOP 1 * FROM OrderList")){

DisplayRowValues(reader);

}

void DisplayRowValues(IDataReader reader)

{

while (reader.Read()) {

for (int i = 0; i < reader.FieldCount; i++) {

Console.WriteLine("{0} = {1}", reader.GetName(i), reader[i].ToString());

}

Console.WriteLine();

}

通过数组参数获取行记录

using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState","Colorado"))

{

DisplayRowValues(reader);

}

此时注意参数顺序必须与预期一致

通过命名参数获取行记录

string sqlStatement = "SELECT TOP 1 * FROM OrderList WHERE State LIKE @state";

using (DbCommand sqlCmd = defaultDB.GetSqlStringCommand(sqlStatement))

{

defaultDB.AddInParameter(sqlCmd, "state", DbType.String,"New York");

using (IDataReader sqlReader = defaultDB.ExecuteReader(sqlCmd))

{

DisplayRowValues(sqlReader);

}

string storedProcName = "ListOrdersByState";

using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand(storedProcName))

{

defaultDB.AddInParameter(sprocCmd, "state", DbType.String,"New York");

using (IDataReader sprocReader = defaultDB.ExecuteReader(sprocCmd))

{

DisplayRowValues(sprocReader);

}

获取对象数据

通过访问器

var productData = defaultDB.ExecuteSprocAccessor<Product>("GetProductList",

"%bike%");

var results = from productItem

in productData

where productItem.Description != null

orderby productItem.Name

select new { productItem.Name, productItem.Description };

foreach (var itemin results)

{

Console.WriteLine("Product Name: {0}", item.Name);

Console.WriteLine("Description: {0}", item.Description);

Console.WriteLine();

}

创建和使用映射器（待补充实例）

获取xml数据（sql server专用）

SqlDatabase sqlServerDB = DatabaseFactory.CreateDatabase() as SqlDatabase;

string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";

using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))

{

xmlCmd.Parameters.Add(new SqlParameter("state","Colorado"));

using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))

{

while (!reader.EOF) {

if (reader.IsStartElement()) {

Console.WriteLine(reader.ReadOuterXml());

}

获取单行数据

using (DbCommand sqlCmd =

defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))

{

Console.WriteLine("Result using a SQL statement: {0}", defaultDB.ExecuteScalar(sqlCmd).ToString());

}

using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))

{

Console.WriteLine("Result using a stored procedure: {0}", defaultDB.ExecuteScalar(sprocCmd).ToString());

}

异步获取数据

通过begin和end方法异步获取数据集

DbCommand cmd = asyncDB.GetStoredProcCommand("ListOrdersSlowly");

asyncDB.AddInParameter(cmd, "state", DbType.String,"Colorado");

asyncDB.AddInParameter(cmd, "status", DbType.String,"DRAFT");

asyncDB.BeginExecuteReader(cmd, asyncResult => {

try {

using (IDataReader reader = asyncDB.EndExecuteReader(asyncResult)) {

DisplayRowValues(reader);

}

catch (Exception ex) {

Console.WriteLine("Error after data access completed: {0}", ex.Message);

}

null);

通过task异步获取数据集

static void ReadDataAsynchronouslyTask()

{

if (!SupportsAsync(asyncDB)) return;

DoReadDataAsynchronouslyTask().Wait();

}

private staticasync Task DoReadDataAsynchronouslyTask(){

try {

DbCommand cmd = asyncDB.GetStoredProcCommand("ListOrdersSlowly");

asyncDB.AddInParameter(cmd, "state", DbType.String,"Colorado");

asyncDB.AddInParameter(cmd, "status", DbType.String,"DRAFT");

using (var timer =new Timer(_ => Console.Write("Waiting... "))) {

timer.Change(0, 1000); using (var reader =await Task<IDataReader>.Factory .FromAsync<DbCommand>(asyncDB.BeginExecuteReader, asyncDB.EndExecuteReader, cmd,null)) {

timer.Change(Timeout.Infinite, Timeout.Infinite);

Console.WriteLine();

DisplayRowValues(reader); } } }

catch (Exception ex) { Console.WriteLine("Error while starting data access: {0}", ex.Message); }}

异步获取对象数据（待补充示例）

更新数据

通过更新语句

string oldDescription = "Carries 4 bikes securely; steel construction, fits 2\" receiver hitch.";

string newDescription = "Bikes tend to fall off after a few miles.";

DbCommand cmd = defaultDB.GetStoredProcCommand("UpdateProductsTable");

defaultDB.AddInParameter(cmd, "productID", DbType.Int32, 84);

defaultDB.AddInParameter(cmd, "description", DbType.String, newDescription);

if (defaultDB.ExecuteNonQuery(cmd) == 1)

{

// Update succeeded

else{

Console.WriteLine("ERROR: Could not update just one row.");

}

defaultDB.SetParameterValue(cmd, "description", oldDescription);

if (defaultDB.ExecuteNonQuery(cmd) == 1)

{

// Update succeeded.

}

else{ Console.WriteLine("ERROR: Could not update just one row.");}

通过DataSet

DataSet productDataSet;

string sql = "SELECT CustomerName, CustomerPhone FROM Customers";

productDataSet = db.ExecuteDataSet(CommandType.Text, sql);

productDataSet = db.ExecuteDataSet("GetProductsByCategory","%bike%");

DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory");db.AddInParameter(cmd,"CategoryID", DbType.Int32, 7);

productDataSet = db.ExecuteDataSet(cmd);

string selectSQL = "SELECT Id, Name, Description FROM Products WHERE Id > 90";

DataSet simpleDS = defaultDB.ExecuteDataSet(CommandType.Text, selectSQL);

DisplayTableNames(simpleDS, "ExecuteDataSet");

DataSet loadedDS = new DataSet("ProductsDataSet");

defaultDB.LoadDataSet(CommandType.Text, selectSQL, loadedDS, new string[] { "Products" });

DisplayTableNames(loadedDS, "LoadDataSet");

DataTable dt = loadedDS.Tables["Products"];

dt.Rows[0].Delete();

object[] rowData = newobject[] { -1, "A New Row","Added to the table at " + DateTime.Now.ToShortTimeString() };

rowData = dt.Rows[1].ItemArray;

rowData[2] = "A new description at " + DateTime.Now.ToShortTimeString();

dt.Rows[1].ItemArray = rowData;

DisplayRowValues(dt);

string addSQL = "INSERT INTO Products (Name, Description) " + VALUES (@name, @description)";

string updateSQL = "UPDATE Products SET Name = @name, " + "Description = @description WHERE Id = @id";

string deleteSQL = "DELETE FROM Products WHERE Id = @id";

DbCommand insertCommand = defaultDB.GetSqlStringCommand(addSQL);

defaultDB.AddInParameter(insertCommand, "name", DbType.String,"Name", DataRowVersion.Current);

defaultDB.AddInParameter(insertCommand, "description", DbType.String,"Description", DataRowVersion.Current);

DbCommand updateCommand = defaultDB.GetSqlStringCommand(updateSQL);

defaultDB.AddInParameter(updateCommand, "name", DbType.String,"Name", DataRowVersion.Current);

defaultDB.AddInParameter(updateCommand, "description", DbType.String,"Description", DataRowVersion.Current);

defaultDB.AddInParameter(updateCommand, "id", DbType.String,"Id", DataRowVersion.Original);

DbCommand deleteCommand = defaultDB.GetSqlStringCommand(deleteSQL);

defaultDB.AddInParameter(deleteCommand, "id", DbType.Int32,"Id", DataRowVersion.Original);

int rowsAffected = defaultDB.UpdateDataSet(loadedDS,"Products", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);

Console.WriteLine("Updated a total of {0} rows in the database.", rowsAffected);

管理数据库连接

ADO.NET提供了数据库连接池的管理办法；Data Access同样提供了管理数据库连接的方案

通过using构造reader，以确保disposed被调用；

using (IDataReader reader = db.ExecuteReader(cmd))

{

}

主动创建connection，通过using构造，实现关闭和释放

using (DbConnection conn = db.CreateConnection())

{

conn.Open();

try {

// perform data access here }

catch { // handle any errors here }

}

使用基于事务的connection

ACID：原子性（事务中所有操作要么执行要么不执行），一致性（事务完成之后数据库必须保持一致的状态），隔离线（处于事务中间状态的数据不可访问）、持久性（事务成功之后数据是持久的）

using (DbConnection conn = db.CreateConnection()){

conn.Open(); DbTransaction trans = conn.BeginTransaction();

try { // execute commands, passing in the current transaction to each one

db.ExecuteNonQuery(cmdA, trans);

db.ExecuteNonQuery(cmdB, trans);

trans.Commit(); // commit the transaction }

catch {

trans.Rollback(); // rollback the transaction

}

使用分布式事务

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))

{ // perform data access here}

0 0