ADO.NET使用方法和技巧

ADO.NET使用方法和技巧

 　　以下代碼闡明了如何使用 SqlDataAdapter 對象發出可產生 DataSet 或 DataTable 的命令。它從 SQL Server Northwind 資料庫中檢索一組產品類別。

　　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 將只返回第一行的第一列。

　　以下代碼說明了如何尋找與特定產品識別碼 相對應的產品名稱：

　　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 屬性來檢索輸出值。

　　上述代碼使用了以下預存程序。

　　CREATE PROCEDURE LookupProductNameSPOutput

　　@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

　　}

　　}

　　}