Data Access Application Block 2 应用场景-优快云博客

1. 使用DbDataReader从数据库中返回多行

using (IDataReader reader = db.ExecuteReader(CommandType.Text, "Select Name, Address, City From Customers" )) { customerGrid.DataSource = reader; customerGrid.DataBind(); }

2.使用DataSet从数据库中返回多行

string sql = "GetProductsByCategory"; DbCommand cmd = db.GetStoredProcCommand(sql); // Retrieve products from category 7. int category = 7; db.AddInParameter(cmd, "CategoryID", DbType.Int32, category); DataSet productDataSet = db.ExecuteDataSet(cmd);

3.如何得到sql的返回值

string sql = "GetProductDetails"; DbCommand cmd = db.GetStoredProcCommand(sql); db.AddInParameter(cmd, "ProductID", DbType.Int32, 3); db.AddOutParameter(cmd, "ProductName", DbType.String, 50); db.AddOutParameter(cmd, "UnitPrice", DbType.Currency, 8); db.ExecuteNonQuery(cmd); string results = string.Format("{0}, {1}, {2:C} ", db.GetParameterValue(cmd, "ProductID"), db.GetParameterValue(cmd, "ProductName"), db.GetParameterValue(cmd, "UnitPrice"));

4. 运行command，得到唯一的返回值

string sql = "GetProductName"; int productId = 7; DbCommand cmd = db.GetStoredProcCommand(sql, productId); string productName = (string)db.ExecuteScalar(cmd);

5. 如何使用事务处理？

public bool Transfer(Database db, int transactionAmount, int sourceAccount, int destinationAccount) { // The default database service is determined through configuration // and passed to the method as a parameter that can be generated // automatically through injection when the application initializes. bool result = false; // Two operations: one to credit an account and one to debit another account. string sql = "CreditAccount"; DbCommand creditCommand = db.GetStoredProcCommand(sql); db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount); db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount); sql = "DebitAccount"; DbCommand debitCommand = db.GetStoredProcCommand(sql); db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount); db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount); using (DbConnection conn = db.CreateConnection()) { conn.Open(); DbTransaction trans = conn.BeginTransaction(); try { // Credit the first account. db.ExecuteNonQuery(creditCommand, trans); // Debit the second account. db.ExecuteNonQuery(debitCommand, trans); // Commit the transaction. trans.Commit(); result = true; } catch { // Roll back the transaction. trans.Rollback(); } conn.Close(); return result; } }

6. 使用ds更新数据库

DataSet productsDataSet = new DataSet(); string sql = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products"; DbCommand cmd = db.GetSqlStringCommand(sql); string productsTable = "Products"; // Retrieve the initial data. db.LoadDataSet(cmd, productsDataSet, productsTable); // Get the table that will be modified. DataTable dTable = productsDataSet.Tables[productsTable]; // Add a new product to existing DataSet. DataRow addedRow = dTable.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25}); // Modify an existing product. dTable.Rows[0]["ProductName"] = "Modified product"; // Establish the 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, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);

7. 异步的信息处理

String connectionString = @"server=(local); database=Northwind; Integrated Security=true; Asynchronous Processing=true"; Database db = new SqlDatabase(connectionString); DbCommand cmd = db.GetStoredProcCommand("Some Procedure Name"); try { IAsyncResult result = db.BeginExecuteReader(cmd, MyEndExecuteCallback, db); } catch { // ... // handle any execution initiation errors here } //================================================ // callback handler that executes when call completes public void MyEndExecuteCallback(IAsyncResult result) { try { // obtain the results from the database instance in the AsyncState Database db = (Database)result.AsyncState; using (IDataReader reader = db.EndExecuteReader(result)) { // ... // use the DataReader here as required } } catch { // ... // handle any execution completion errors here } }

8. Data Access Application Block 设计实现