以下代碼闡明了如何使用 SqlDataAdapter 對象發(fā)出可生成 DataSet 或 DataTable 的命令。它從 SQL Server Northwind 數(shù)據(jù)庫中檢索一組產(chǎn)品類別。
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;}}1. | 創(chuàng)建一個 SqlCommand 對象以調(diào)用該存儲過程，并將其與一個 SqlConnection 對象（顯示）或連接字符串（不顯示）相關(guān)聯(lián)。 |
2. | 創(chuàng)建一個新的 SqlDataAdapter 對象并將其與 SqlCommand 對象相關(guān)聯(lián)。 |
3. | 創(chuàng)建一個 DataTable（也可以創(chuàng)建一個 DataSet）對象。使用構(gòu)造函數(shù)參數(shù)來命名 DataTable。 |
4. | 調(diào)用 SqlDataAdapter 對象的 Fill 方法，用檢索到的行填充 DataSet 或 DataTable。 |
以下代碼片段闡明了可檢索多個行的 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 closedreturn( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );}catch{conn.Close();throw;}}// Display the product list using the consoleprivate 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}}1. | 創(chuàng)建一個用來執(zhí)行存儲過程的 SqlCommand 對象，并將其與一個 SqlConnection 對象相關(guān)聯(lián)。 |
2. | 打開連接。 |
3. | 通過調(diào)用 SqlCommand 對象的 ExecuteReader 方法創(chuàng)建一個 SqlDataReader 對象。 |
4. | 要從流中讀取數(shù)據(jù)，請調(diào)用 SqlDataReader 對象的 Read 方法來檢索行，并使用類型化訪問器方法（如 GetInt32 和 GetString 方法）來檢索列值。 |
5. | 使用完讀取器后，請調(diào)用其 Close 方法。 |
可以使用 SqlCommand 對象來生成 XmlReader 對象，后者可提供對 XML 數(shù)據(jù)的基于流的只進(jìn)訪問。命令（通常為存儲過程）必須產(chǎn)生基于 XML 的結(jié)果集，對于 SQL Server 2000 而言，該結(jié)果集通常包含一個帶有有效 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 DATRetrieveProductsXMLASSELECT * FROM PRODUCTSFOR XML AUTOGO
1. | 創(chuàng)建一個 SqlCommand 對象來調(diào)用可生成 XML 結(jié)果集的存儲過程（例如，在 SELECT 語句中使用 FOR XML 子句）。將該 SqlCommand 對象與某個連接相關(guān)聯(lián)。 |
2. | 調(diào)用 SqlCommand 對象的 ExecuteXmlReader 方法，并且將結(jié)果分配給只進(jìn) XmlTextReader 對象。當(dāng)您不需要對返回的數(shù)據(jù)進(jìn)行任何基于 XML 的驗(yàn)證時，這是應(yīng)該使用的最快類型的 XmlReader 對象。 |
3. | 使用 XmlTextReader 對象的 Read 方法來讀取數(shù)據(jù)。 |
借助于命名的輸出參數(shù)，可以調(diào)用在單個行內(nèi)返回檢索到的數(shù)據(jù)項(xiàng)的存儲過程。以下代碼片段使用存儲過程來檢索 Northwind 數(shù)據(jù)庫的 Products 表中包含的特定產(chǎn)品的產(chǎn)品名稱和單價。
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 procSqlCommand 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 parametersSqlParameter 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 populatedcmd.ExecuteNonQuery( );// Return output parameters from stored procProductName = paramProdName.Value.ToString();UnitPrice = (decimal)paramUnitPrice.Value;}}1. | 創(chuàng)建一個 SqlCommand 對象并將其與一個 SqlConnection 對象相關(guān)聯(lián)。 |
2. | 通過調(diào)用 SqlCommand 的 Parameters 集合的 Add 方法來設(shè)置存儲過程參數(shù)。默認(rèn)情況下，參數(shù)都被假設(shè)為輸入?yún)?shù)，因此必須顯式設(shè)置任何輸出參數(shù)的方向。 注 一種良好的習(xí)慣做法是顯式設(shè)置所有參數(shù)（包括輸入?yún)?shù)）的方向。 |
3. | 打開連接。 |
4. | 調(diào)用 SqlCommand 對象的 ExecuteNonQuery 方法。這將填充輸出參數(shù)（并可能填充返回值）。 |
5. | 通過使用 Value 屬性，從適當(dāng)?shù)?SqlParameter 對象中檢索輸出參數(shù)。 |
6. | 關(guān)閉連接。 |
上述代碼片段調(diào)用了以下存儲過程。
CREATE PROCEDURE DATGetProductDetailsSPOutput@ProductID int,@ProductName nvarchar(40) OUTPUT,@UnitPrice money OUTPUTASSELECT @ProductName = ProductName,@UnitPrice = UnitPriceFROM ProductsWHERE ProductID = @ProductIDGO
可以使用 SqlDataReader 對象來檢索單個行，尤其是可以從返回的數(shù)據(jù)流中檢索需要的列值。以下代碼片段對此進(jìn)行了說明。
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 procSqlCommand cmd = new SqlCommand( "DATGetProductDetailsReader", conn );cmd.CommandType = CommandType.StoredProcedure;// Establish stored proc parameters.// @ProductID int INPUTSqlParameter 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 streamProductName = reader.GetString(0);UnitPrice = reader.GetDecimal(1);}}}}1. | 建立 SqlCommand 對象。 |
2. | 打開連接。 |
3. | 調(diào)用 SqlDataReader 對象的 ExecuteReader 方法。 |
4. | 通過 SqlDataReader 對象的類型化訪問器方法（在這里，為 GetString 和 GetDecimal）來檢索輸出參數(shù)。 |
上述代碼片段調(diào)用了以下存儲過程。
CREATE PROCEDURE DATGetProductDetailsReader@ProductID intASSELECT ProductName, UnitPrice FROM ProductsWHERE ProductID = @ProductIDGO
ExecuteScalar 方法專門適用于僅返回單個值的查詢。如果查詢返回多個列和/或行，ExecuteScalar 將只返回第一行的第一列。
以下代碼說明了如何查找與特定產(chǎn)品 ID 相對應(yīng)的產(chǎn)品名稱：
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();}}1. | 建立一個 SqlCommand 對象來調(diào)用存儲過程。 |
2. | 打開連接。 |
3. | 調(diào)用 ExecuteScalar 方法。注意，該方法返回一個對象類型。它包含檢索到的第一列的值，并且必須轉(zhuǎn)化為適當(dāng)?shù)念愋汀? |
4. | 關(guān)閉連接。 |
上述代碼使用了以下存儲過程：
CREATE PROCEDURE LookupProductNameScalar@ProductID intASSELECT TOP 1 ProductNameFROM ProductsWHERE ProductID = @ProductIDGO
可以使用存儲過程輸出或返回參數(shù)來查找單個值。以下代碼闡明了輸出參數(shù)的用法：
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 對象來調(diào)用存儲過程。 |
2. | 通過將 SqlParameters 添加到 SqlCommand 的 Parameters 集合中，設(shè)置任何輸入?yún)?shù)和單個輸出參數(shù)。 |
3. | 打開連接。 |
4. | 調(diào)用 SqlCommand 對象的 ExecuteNonQuery 方法。 |
5. | 關(guān)閉連接。 |
6. | 通過使用輸出 SqlParameter 的 Value 屬性來檢索輸出值。 |
上述代碼使用了以下存儲過程。
CREATE PROCEDURE LookupProductNameSPOutput@ProductID int,@ProductName nvarchar(40) OUTPUTASSELECT @ProductName = ProductNameFROM ProductsWHERE ProductID = @ProductIDGO
以下代碼闡明了如何使用返回值來指明是否存在特定行。從編碼角度來看，這類似于使用存儲過程輸出參數(shù)，不同之處在于必須將 SqlParameter 方向顯式設(shè)置為 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 對象來調(diào)用存儲過程。 |
2. | 設(shè)置一個輸入?yún)?shù)，該參數(shù)含有要訪問的行的主鍵值。 |
3. | 設(shè)置單個返回值參數(shù)。將一個 SqlParameter 對象添加到 SqlCommand 的 Parameters 集合中，并將其方向設(shè)置為 ParameterDirection.ReturnValue。 |
4. | 打開連接。 |
5. | 調(diào)用 SqlCommand 對象的 ExecuteNonQuery 方法。 |
6. | 關(guān)閉連接。 |
7. | 通過使用返回值 SqlParameter 的 Value 屬性來檢索返回值。 |
上述代碼使用了以下存儲過程。
CREATE PROCEDURE CheckProductSP@ProductID intASIF EXISTS( SELECT ProductIDFROM ProductsWHERE ProductID = @ProductID )return 1ELSEreturn 0GO
可以使用 SqlDataReader 對象并通過調(diào)用命令對象的 ExecuteReader 方法來獲取單個輸出值。這要求編寫稍微多一點(diǎn)的代碼，因?yàn)楸仨氄{(diào)用 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 intASSELECT COUNT(*) FROM ProductsWHERE ProductID = @ProductIDGO
以下代碼顯示了如何充分利用 SQL Server .NET 數(shù)據(jù)提供程序所提供的事務(wù)處理支持，通過事務(wù)來保護(hù)資金轉(zhuǎn)帳操作。該操作在同一數(shù)據(jù)庫中的兩個帳戶之間轉(zhuǎn)移資金。
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 transactionusing ( SqlTransaction trans = conn.BeginTransaction() ){// Associate the two command objects with the same transactioncmdCredit.Transaction = trans;cmdDebit.Transaction = trans;try{cmdCredit.ExecuteNonQuery();cmdDebit.ExecuteNonQuery();// Both commands (credit and debit) were successfultrans.Commit();}catch( Exception ex ){// transaction failedtrans.Rollback();// log exception details . . .throw ex;}}}}以下存儲過程闡明了如何在 Transact-SQL 存儲過程內(nèi)部執(zhí)行事務(wù)性資金轉(zhuǎn)帳操作。
CREATE PROCEDURE MoneyTransfer@FromAccount char(20),@ToAccount char(20),@Amount moneyASBEGIN TRANSACTION-- PERFORM DEBIT OPERATIONUPDATE AccountsSET Balance = Balance - @AmountWHERE AccountNumber = @FromAccountIF @@RowCount = 0BEGINRAISERROR('Invalid From Account Number', 11, 1)GOTO ABORTENDDECLARE @Balance moneySELECT @Balance = Balance FROM ACCOUNTSWHERE AccountNumber = @FromAccountIF @BALANCE < 0BEGINRAISERROR('Insufficient funds', 11, 1)GOTO ABORTEND-- PERFORM CREDIT OPERATIONUPDATE AccountsSET Balance = Balance + @AmountWHERE AccountNumber = @ToAccountIF @@RowCount = 0BEGINRAISERROR('Invalid To Account Number', 11, 1)GOTO ABORTENDCOMMIT TRANSACTIONRETURN 0ABORT:ROLLBACK TRANSACTIONGO該存儲過程使用 BEGIN TRANSACTION、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 語句來手動控制該事務(wù)。
以下示例代碼顯示了三個服務(wù)性 .NET 托管類，這些類經(jīng)過配置以執(zhí)行自動事務(wù)處理。每個類都使用 Transaction 屬性進(jìn)行了批注，該屬性的值確定是否應(yīng)該啟動新的事務(wù)流，或者該對象是否應(yīng)該共享其直接調(diào)用方的事務(wù)流。這些組件協(xié)同工作來執(zhí)行銀行資金轉(zhuǎn)帳任務(wù)。Transfer 類被使用 RequiresNew 事務(wù)屬性進(jìn)行了配置，而 Debit 和 Credit 被使用 Required 進(jìn)行了配置。結(jié)果，所有這三個對象在運(yùn)行時都將共享同一事務(wù)。
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 operationDebit debit = new Debit();debit.DebitAccount( fromAccount, amount );// Perform the credit operationCredit credit = new Credit();credit.CreditAccount( toAccount, amount );}catch( SqlException sqlex ){// Handle and log exception details// Wrap and propagate the exceptionthrow 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 herethrow; // 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 herethrow; // Propagate exception back to caller}}}