ADO.NET在開發中的部分使用方法和技巧

ADO.NET在開發中的部分使用方法和技巧

using System.Data; using System.Data.SqlClient; public DataTable RetrieveRowsWithDataTable() { using( SqlConnection conn = new SqlConnection(connectionString) ) { conn.Open(); SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter adapter = new SqlDataAdapter( cmd ); DataTable dataTable = new DataTable("Products"); adapter .Fill(dataTable); return dataTable; } }  使用 SqlAdapter 生成 DataSet 或 DataTable  1.建立一個 SqlCommand 對象以調用該存儲過程，并将其與一個 SqlConnection 對象（顯示）或連接配接字元串（不顯示）相關聯。 2.建立一個新的 SqlDataAdapter 對象并将其與 SqlCommand 對象相關聯。  3.建立一個 DataTable（也可以建立一個 DataSet）對象。使用構造函數參數來命名 DataTable。  4.調用 SqlDataAdapter 對象的 Fill 方法，用檢索到的行填充 DataSet 或 DataTable。   如何使用 SqlDataReader 來檢索多個行 以下代碼片段闡明了可檢索多個行的 SqlDataReader方法。 using System.IO;  using System.Data;  using System.Data.SqlClient;  public SqlDataReader RetrieveRowsWithDataReader()  { SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=northwind");  SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );  cmd.CommandType = CommandType.StoredProcedure;  try { conn.Open(); // Generate the reader. CommandBehavior.CloseConnection causes the // the connection to be closed when the reader object is closed  return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) ); }  catch { conn.Close(); throw; } } // Display the product list using the console  private void DisplayProducts() { SqlDataReader reader = RetrieveRowsWithDataReader();  try { while (reader.Read()) { Console.WriteLine("{0} {1} {2}", reader.GetInt32(0).ToString(), reader.GetString(1) ); } } finally { reader.Close(); // Also closes the connection due to the // CommandBehavior enum used when generating the reader } }  使用 SqlDataReader 檢索行  1.建立一個用來執行存儲過程的 SqlCommand 對象，并将其與一個 SqlConnection對象相關聯。  2.打開連接配接。  3.通過調用 SqlCommand 對象的 ExecuteReader方法建立一個 SqlDataReader對象。  4.  要從流中讀取資料，請調用 SqlDataReader對象的 Read方法來檢索行，并使用類型化通路器方法（如 GetInt32和 GetString方法）來檢索列值。  5.  使用完讀取器後，請調用其 Close方法。 如何使用 XmlReader 檢索多個行 可以使用 SqlCommand對象來生成 XmlReader對象，後者可提供對 XML 資料的基于流的隻進通路。指令（通常為存儲過程）必須産生基于 XML 的結果集，對于 SQL Server 2000 而言，該結果集通常包含一個帶有有效 FOR XML子句的 SELECT語句。以下代碼片段闡明了該方法： public void RetrieveAndDisplayRowsWithXmlReader() {  using( SqlConnection conn = new SqlConnection(connectionString) )  { SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );  cmd.CommandType = CommandType.StoredProcedure; try { conn.Open();  XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();  while ( xreader.Read() ) { if ( xreader.Name == "PRODUCTS" ) { string strOutput = xreader.GetAttribute("ProductID");  strOutput += " "; strOutput += xreader.GetAttribute("ProductName"); Console.WriteLine( strOutput ); } } xreader.Close();  // XmlTextReader does not support IDisposable so it can't be // used within a using keyword } }  上述代碼使用了以下存儲過程： CREATE PROCEDURE DATRetrieveProductsXML AS SELECT * FROM PRODUCTS FOR XML AUTO GO  使用 XmlReader 檢索 XML 資料  1.建立一個 SqlCommand 對象來調用可生成 XML 結果集的存儲過程（例如，在 SELECT語句中使用 FOR XML子句）。将該 SqlCommand對象與某個連接配接相關聯。  2.調用 SqlCommand 對象的 ExecuteXmlReader方法，并且将結果配置設定給隻進 XmlTextReader對象。當您不需要對傳回的資料進行任何基于 XML 的驗證時，這是應該使用的最快類型的 XmlReader對象。  3.使用 XmlTextReader 對象的 Read方法來讀取資料。 如何使用存儲過程輸出參數來檢索單個行 借助于命名的輸出參數，可以調用在單個行内傳回檢索到的資料項的存儲過程。以下代碼片段使用存儲過程來檢索 Northwind 資料庫的 Products 表中包含的特定産品的産品名稱和單價。 void GetProductDetails( int ProductID, out string ProductName, out decimal UnitPrice )  { using( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=Northwind") ) { // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand( "DATGetProductDetailsSPOutput", conn )  cmd.CommandType = CommandType.StoredProcedure;  // Establish stored proc parameters.  // @ProductID int INPUT // @ProductName nvarchar(40) OUTPUT  // @UnitPrice money OUTPUT  // Must explicitly set the direction of output parameters SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID );  paramProdID.Direction = ParameterDirection.Input; SqlParameter paramProdName = cmd.Parameters.Add( "@ProductName", SqlDbType.VarChar, 40 );  paramProdName.Direction = ParameterDirection.Output; SqlParameter paramUnitPrice = cmd.Parameters.Add( "@UnitPrice", SqlDbType.Money ); paramUnitPrice.Direction = ParameterDirection.Output; conn.Open();  // Use ExecuteNonQuery to run the command.  // Although no rows are returned any mapped output parameters  // (and potentially return values) are populated cmd.ExecuteNonQuery( );  // Return output parameters from stored proc ProductName = paramProdName.Value.ToString(); UnitPrice = (decimal)paramUnitPrice.Value; } }  使用存儲過程輸出參數來檢索單個行  1.建立一個 SqlCommand 對象并将其與一個 SqlConnection對象相關聯。  2.通過調用 SqlCommand 的 Parameters集合的 Add方法來設定存儲過程參數。預設情況下，參數都被假設為輸入參數，是以必須顯式設定任何輸出參數的方向。  注一種良好的習慣做法是顯式設定所有參數（包括輸入參數）的方向。 3.打開連接配接。  4.調用 SqlCommand 對象的 ExecuteNonQuery方法。這将填充輸出參數（并可能填充傳回值）。  5.通過使用 Value 屬性，從适當的 SqlParameter對象中檢索輸出參數。  6.關閉連接配接。  上述代碼片段調用了以下存儲過程。 CREATE PROCEDURE DATGetProductDetailsSPOutput  @ProductID int, @ProductName nvarchar(40) OUTPUT,  @UnitPrice money OUTPUT AS SELECT @ProductName = ProductName,  @UnitPrice = UnitPrice FROM Products WHERE ProductID = @ProductID GO  如何使用 SqlDataReader 來檢索單個行 可以使用 SqlDataReader對象來檢索單個行，尤其是可以從傳回的資料流中檢索需要的列值。以下代碼片段對此進行了說明。 void GetProductDetailsUsingReader( int ProductID, out string ProductName, out decimal UnitPrice ) { using( SqlConnection conn = new SqlConnection( "server=(local); Integrated Security=SSPI;database=Northwind") ) { // Set up the command object used to execute the stored proc SqlCommand cmd = new SqlCommand( "DATGetProductDetailsReader", conn );  cmd.CommandType = CommandType.StoredProcedure; // Establish stored proc parameters.  // @ProductID int INPUT SqlParameter paramProdID = cmd.Parameters.Add( "@ProductID", ProductID ); paramProdID.Direction = ParameterDirection.Input;  conn.Open(); using( SqlDataReader reader = cmd.ExecuteReader() ) { if( reader.Read() )  // Advance to the one and only row { // Return output parameters from returned data stream ProductName = reader.GetString(0);  UnitPrice = reader.GetDecimal(1);  } } } }  使用 SqlDataReader 對象來傳回單個行  1.建立 SqlCommand 對象。  2.打開連接配接。  3.調用 SqlDataReader 對象的 ExecuteReader方法。  4.通過 SqlDataReader 對象的類型化通路器方法（在這裡，為 GetString和 GetDecimal）來檢索輸出參數。  上述代碼片段調用了以下存儲過程。 CREATE PROCEDURE DATGetProductDetailsReader  @ProductID int AS SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @ProductID GO  如何使用 ExecuteScalar 來檢索單個項 ExecuteScalar方法專門适用于僅傳回單個值的查詢。如果查詢傳回多個列和/或行，ExecuteScalar将隻傳回第一行的第一列。 以下代碼說明了如何查找與特定産品 ID 相對應的産品名稱： void GetProductNameExecuteScalar( int ProductID, out string ProductName ) { using( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=northwind") ) { SqlCommand cmd = new SqlCommand("LookupProductNameScalar", conn ); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ProductID", ProductID );  conn.Open(); ProductName = (string)cmd.ExecuteScalar();  } }  使用 ExecuteScalar 來檢索單個項  1.建立一個 SqlCommand 對象來調用存儲過程。  2.打開連接配接。  3.調用 ExecuteScalar 方法。注意，該方法傳回一個對象類型。它包含檢索到的第一列的值，并且必須轉化為适當的類型。  4.關閉連接配接。  上述代碼使用了以下存儲過程： CREATE PROCEDURE LookupProductNameScalar @ProductID int AS SELECT TOP 1 ProductName FROM Products WHERE ProductID =  @ProductID GO  如何使用存儲過程輸出或傳回參數來檢索單個項 可以使用存儲過程輸出或傳回參數來查找單個值。以下代碼闡明了輸出參數的用法： void GetProductNameUsingSPOutput( int ProductID, out string ProductName )  { using( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=northwind") )  { SqlCommand cmd = new SqlCommand("LookupProductNameSPOutput", conn );  cmd.CommandType = CommandType.StoredProcedure;  SqlParameter paramProdID = cmd.Parameters.Add("@ProductID", ProductID );  ParamProdID.Direction = ParameterDirection.Input;  SqlParameter paramPN = cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 ); paramPN.Direction = ParameterDirection.Output; conn.Open();  cmd.ExecuteNonQuery(); ProductName = paramPN.Value.ToString(); } }  使用存儲過程輸出參數來檢索單個值  1.建立一個 SqlCommand 對象來調用存儲過程。  2.通過将 SqlParameters 添加到 SqlCommand的 Parameters集合中，設定任何輸入參數和單個輸出參數。 3.打開連接配接。  4.調用 SqlCommand 對象的 ExecuteNonQuery方法。  5.關閉連接配接。  6.通過使用輸出 SqlParameter 的 Value屬性來檢索輸出值。  上述代碼使用了以下存儲過程。 @ProductID int, @ProductName nvarchar(40) OUTPUT AS SELECT  @ProductName = ProductName FROM Products WHERE ProductID = @ProductID GO  以下代碼闡明了如何使用傳回值來指明是否存在特定行。從編碼角度來看，這類似于使用存儲過程輸出參數，不同之處在于必須将 SqlParameter方向顯式設定為 ParameterDirection.ReturnValue。 bool CheckProduct( int ProductID )  { using( SqlConnection conn = new SqlConnection( "server=(local); Integrated Security=SSPI;database=northwind") )  { SqlCommand cmd = new SqlCommand("CheckProductSP", conn );  cmd.CommandType = CommandType.StoredProcedure;  cmd.Parameters.Add("@ProductID", ProductID );  SqlParameter paramRet = cmd.Parameters.Add("@ProductExists", SqlDbType.Int );  paramRet.Direction = ParameterDirection.ReturnValue; conn.Open(); cmd.ExecuteNonQuery(); }  return (int)paramRet.Value == 1; }  通過使用存儲過程傳回值來檢查是否存在特定行  1.建立一個 SqlCommand 對象來調用存儲過程。  2.設定一個輸入參數，該參數含有要通路的行的主鍵值。  3.設定單個傳回值參數。将一個 SqlParameter 對象添加到 SqlCommand的 Parameters集合中，并将其方向設定為 ParameterDirection.ReturnValue。  4.打開連接配接。  5.調用 SqlCommand 對象的 ExecuteNonQuery方法。  6.關閉連接配接。  7.通過使用傳回值 SqlParameter 的 Value屬性來檢索傳回值。  上述代碼使用了以下存儲過程。  CREATE PROCEDURE CheckProductSP  @ProductID int AS IF EXISTS( SELECT ProductID FROM Products WHERE ProductID =  @ProductID )  return 1 ELSE return 0 GO  如何使用 SqlDataReader 來檢索單個項 可以使用 SqlDataReader對象并通過調用指令對象的 ExecuteReader方法來擷取單個輸出值。這要求編寫稍微多一點的代碼，因為必須調用 SqlDataReader Read 方法，然後通過該讀取器的通路器方法之一來檢索需要的值。以下代碼闡明了 SqlDataReader對象的用法。 bool CheckProductWithReader( int ProductID )  { using( SqlConnection conn = new SqlConnection( "server=(local); Integrated Security=SSPI;database=northwind") )  { SqlCommand cmd = new SqlCommand("CheckProductExistsWithCount", conn );  cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ProductID", ProductID ); cmd.Parameters["@ProductID"].Direction = ParameterDirection.Input; conn.Open();  using( SqlDataReader reader = cmd.ExecuteReader( CommandBehavior.SingleResult ) )  { if( reader.Read() ) { return (reader.GetInt32(0) >0); } return false; } }  上述代碼采用了以下存儲過程。 CREATE PROCEDURE CheckProductExistsWithCount @ProductID int AS SELECT COUNT(*) FROM Products WHERE ProductID = @ProductID GO  如何編寫 ADO.NET 手動事務處理代碼 以下代碼顯示了如何充分利用 SQL Server .NET 資料提供程式所提供的事務處理支援，通過事務來保護資金轉帳操作。該操作在同一資料庫中的兩個帳戶之間轉移資金。 public void TransferMoney( string toAccount, string fromAccount, decimal amount ) {  using ( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=SimpleBank" ) )  { SqlCommand cmdCredit = new SqlCommand("Credit", conn ); cmdCredit.CommandType = CommandType.StoredProcedure;  cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) ); cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount ));  SqlCommand cmdDebit = new SqlCommand("Debit", conn ); cmdDebit.CommandType = CommandType.StoredProcedure; cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) ); cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));  conn.Open();  // Start a new transaction using ( SqlTransaction trans = conn.BeginTransaction() ) {  // Associate the two command objects with the same transaction cmdCredit.Transaction = trans; cmdDebit.Transaction = trans; try { cmdCredit.ExecuteNonQuery();  cmdDebit.ExecuteNonQuery(); // Both commands (credit and debit) were successful trans.Commit(); } catch( Exception ex ) { // transaction failed trans.Rollback(); // log exception details . . .  throw ex; } } } }  如何使用 Transact-SQL 執行事務處理 以下存儲過程闡明了如何在 Transact-SQL 存儲過程内部執行事務性資金轉帳操作。 CREATE PROCEDURE MoneyTransfer  @FromAccount char(20), @ToAccount char(20),  @Amount money AS BEGIN TRANSACTION -- PERFORM DEBIT OPERATION UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountNumber = @FromAccount IF RowCount = 0 BEGIN RAISERROR('Invalid From Account Number', 11, 1)  GOTO ABORT END DECLARE  @Balance money SELECT @Balance = Balance FROM ACCOUNTS  WHERE AccountNumber = @FromAccount IF @BALANCE  0 BEGIN RAISERROR('Insufficient funds', 11, 1) GOTO ABORT END -- PERFORM CREDIT OPERATION UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @ToAccount  IF RowCount = 0 BEGIN RAISERROR('Invalid To Account Number', 11, 1) GOTO ABORT END COMMIT TRANSACTION RETURN 0 ABORT: ROLLBACK TRANSACTION GO 該存儲過程使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 語句來手動控制該事務。 如何編寫事務性 .NET 類 以下示例代碼顯示了三個服務性 .NET 托管類，這些類經過配置以執行自動事務處理。每個類都使用 Transaction屬性進行了批注，該屬性的值确定是否應該啟動新的事務流，或者該對象是否應該共享其直接調用方的事務流。這些元件協同工作來執行銀行資金轉帳任務。Transfer類被使用 RequiresNew事務屬性進行了配置，而 Debit和 Credit被使用 Required進行了配置。結果，所有這三個對象在運作時都将共享同一事務。 using System;  using System.EnterpriseServices;  [Transaction(TransactionOption.RequiresNew)] public class Transfer : ServicedComponent { [AutoComplete]  public void Transfer( string toAccount, string fromAccount, decimal amount ) { try { // Perform the debit operation Debit debit = new Debit();  debit.DebitAccount( fromAccount, amount ); // Perform the credit operation Credit credit = new Credit();  credit.CreditAccount( toAccount, amount ); }  catch( SqlException sqlex ) {  // Handle and log exception details  // Wrap and propagate the exception throw new TransferException( "Transfer Failure", sqlex ); } } } [Transaction(TransactionOption.Required)] public class Credit : ServicedComponent { [AutoComplete] public void CreditAccount( string account, decimal amount ) { try { using( SqlConnection conn = new SqlConnection( "Server=(local); Integrated Security=SSPI"; database="SimpleBank") ) {  SqlCommand cmd = new SqlCommand("Credit", conn );  cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );  cmd.Parameters.Add( new SqlParameter("@Amount", amount ));  conn.Open(); cmd.ExecuteNonQuery(); } } } catch( SqlException sqlex ){  // Log exception details here throw; // Propagate exception } } [Transaction(TransactionOption.Required)]  public class Debit : ServicedComponent { public void DebitAccount( string account, decimal amount ) {  try { using( SqlConnection conn = new SqlConnection( "Server=(local);  Integrated Security=SSPI";  database="SimpleBank") ) { SqlCommand cmd = new SqlCommand("Debit", conn );  cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add( new SqlParameter("@AccountNo", account) ); cmd.Parameters.Add( new SqlParameter("@Amount", amount )); conn.Open(); cmd.ExecuteNonQuery(); } }  catch (SqlException sqlex) {  // Log exception details here throw;  // Propagate exception back to caller } } }