Enterprise Library 2.0 學習筆記 (一)：Data Access Application Block 核心變更與實戰

DAAB 2.0 緊貼 ADO.NET 2.0：移除 DBCommandWrapper 改用原生 DbCommand、連線字串共用 <connectionStrings> 區段、新增 GenericDatabase 支援任意 Provider。本文詳解配置、核心 API、常用模式與交易處理。

核心改進

API 對比

// 1.1
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetStoredProcCommandWrapper("GetProductsByCategory");
cmd.AddInParameter("CategoryID", DbType.Int32, cat);
var ds = db.ExecuteDataSet(cmd);

// 2.0
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetStoredProcCommand("GetProductsByCategory");
db.AddInParameter(cmd, "CategoryID", DbType.Int32, cat);
var ds = db.ExecuteDataSet(cmd);

連線字串配置

<connectionStrings>
  <add name="DataAccessQuickStart" 
    providerName="System.Data.SqlClient"
    connectionString="server=(local)\SQLEXPRESS;database=EntLibQuickStarts;Integrated Security=true" />
</connectionStrings>
<dataConfiguration defaultDatabase="DataAccessQuickStart" />

三步驟核心模式

1. 建立 Database 物件
2. 建立/配置 DbCommand (SQL/預存程序)
3. 執行命令 (ExecuteReader/ExecuteDataSet/ExecuteNonQuery/ExecuteXmlReader)

常用實戰範例

1. 靜態 SQL 查詢 + DataReader

var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetSqlStringCommand("SELECT CustomerID, Name FROM Customers");
using (var dr = db.ExecuteReader(cmd)) {
    while (dr.Read()) Console.WriteLine(dr["Name"]);
}

2. 預存程序 + 參數 + DataSet

var cmd = db.GetStoredProcCommand("GetProductsByCategory");
db.AddInParameter(cmd, "CategoryID", DbType.Int32, cat);
var ds = db.ExecuteDataSet(cmd);

3. DataSet 批次更新

var ds = new DataSet();
db.LoadDataSet(cmd, ds, "Products");
var tbl = ds.Tables["Products"];
tbl.Rows.Add(DBNull.Value, "New Product", 11, 25);
tbl.Rows[0]["ProductName"] = "Modified";

var ins = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(ins, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(ins, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
db.AddInParameter(ins, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

var del = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(del, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);

var upd = db.GetStoredProcCommand("UpdateProduct");
db.AddInParameter(upd, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
db.AddInParameter(upd, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(upd, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);

int rows = db.UpdateDataSet(ds, "Products", ins, upd, del, UpdateBehavior.Standard);

4. 輸出參數取值

var cmd = db.GetStoredProcCommand("GetProductDetails");
db.AddInParameter(cmd, "ProductID", DbType.Int32, id);
db.AddOutParameter(cmd, "ProductName", DbType.String, 50);
db.AddOutParameter(cmd, "UnitPrice", DbType.Currency, 8);
db.ExecuteNonQuery(cmd);

string name = db.GetParameterValue(cmd, "ProductName").ToString();
decimal price = (decimal)db.GetParameterValue(cmd, "UnitPrice");

5. XML 輸出 (SQL Server FOR XML)

var sqlDb = db as SqlDatabase;
var cmd = sqlDb.GetSqlStringCommand("SELECT * FROM Products FOR XML AUTO");
using (var xr = sqlDb.ExecuteXmlReader(cmd)) {
    while (!xr.EOF) Console.WriteLine(xr.ReadOuterXml());
}
cmd.Connection?.Close(); // XmlReader 關閉不會關連線

6. ADO.NET 原生交易控制

using (var conn = db.CreateConnection()) {
    conn.Open();
    var tx = conn.BeginTransaction();
    try {
        db.ExecuteNonQuery(creditCmd, tx);
        db.ExecuteNonQuery(debitCmd, tx);
        tx.Commit();
        return true;
    } catch {
        tx.Rollback();
        return false;
    }
}

常用工廠方法

• DatabaseFactory.CreateDatabase()：預設資料庫
• CreateDatabase("name")：具名實例
• CreateDatabase("name") as SqlDatabase：強型別存取 SQL Server 特有功能 (ExecuteXmlReader)

參數管理 API

適用場景

選 DAAB 當：需支援多資料庫 (SQL Server/Oracle/其他)、追求程式碼可移植性、希望透過設定檔切換資料庫。

選原生 ADO.NET 當：深度綁定特定資料庫特性 (如 SQL Server Table-Valued Parameters、Oracle 快取)、極致效能調校。