使用 ODBC .NET 提供者和 Visual C# .NET 執行 SQL 參數化預存程序

標籤：http   io   os   ar   使用   strong   sp   資料   div   

http://support2.microsoft.com/kb/310130/zh-cn

此分步指導文章描述如何使用 ODBC .NET Managed 提供者和 Visual C# .Net 調用參數化 SQL Server 預存程序。

儘管使用 ODBC .NET 提供者執行參數化預存程序與使用 SQL 或 OLE DB 提供者執行同一預存程序差別不大，但一個重要的差別是：預存程序必須使用 ODBC CALL 文法進行調用，而不能使用預存程序的名稱。有關此 CALL 文法的其他資訊，請參見 MSDN 庫中 ODBC Programmer‘s Reference（ODBC 程式員參考）中的"Procedure Calls"（程序呼叫）主題。 

調用文法樣本

1. 這是羅斯文樣本資料庫中一個要求單個輸入參數的實際預存程序的調用文法樣本：

   {CALL CustOrderHist (?)}

2. 這是一個要求單輸入參數並返回一個輸出參數和一個傳回值的預存程序的調用文法樣本：第一個預留位置代表傳回值：

   {? = CALL Procedure1 (?, ?)

3. ODBC .NET Managed 提供者，如 OLE DB 提供者，按照位置順序（從 0 開始）而不是按名稱處理參數。

測試專案 - 單輸入參數

1. 從以下 Microsoft Web 網站下載並安裝 ODBC.NET Managed 提供者（如果尚未進行）：http://www.microsoft.com/data
2. 啟動 Visual Studio .NET，然後建立一個 Visual C# .NET Windows 應用程式（名稱自定）。
3. 從項目菜單中，單擊添加引用，然後雙擊 Microsoft.Data.ODBC.dll 將其添加到所選項目列表。關閉引用對話方塊。
4. 將下面的語句添加到代碼視窗的頂部：

   using System.Data;using Microsoft.Data.Odbc;

5. 將 Button 控制項從工具箱拖到預設表單中。
6. 雙擊插入的按鈕切換到該按鈕的 Click 事件的代碼視窗。將以下代碼輸入或粘貼到 Click 事件程序，根據需要修改 SQL Server 連接字串：

   OdbcConnection cn;OdbcCommand cmd;OdbcParameter prm;OdbcDataReader dr;try{//Change the connection string to use your SQL Server.cn = new OdbcConnection("Driver={SQL Server};Server=servername;Database=Northwind;Trusted_Connection=Yes");//Use ODBC call syntax.cmd = new OdbcCommand("{call CustOrderHist (?)}", cn);prm = cmd.Parameters.Add("@CustomerID", OdbcType.Char, 5);prm.Value = "ALFKI";cn.Open();dr = cmd.ExecuteReader();//List each product.while (dr.Read())Console.WriteLine(dr.GetString(0));//Clean up.dr.Close();cn.Close();}catch (OdbcException o) {MessageBox.Show(o.Message.ToString());}

7. 運行該項目。此代碼調用"CustOrderHist"預存程序，傳遞 CustomerID 作為單輸入參數並返回一個結果集。在輸出視窗中，您應該看到羅斯文顧客 ALFKI 訂購的產品列表。
   
   備忘：按 CTRL+ALT+O 按鍵組合可以開啟輸出視窗。

測試專案 - 多參數類型

1. 使用 Query Analyzer，在羅斯文樣本資料庫中建立以下預存程序：此預存程序接受將 CustomerID 作為輸入參數並返回一個顧客訂單列表，返回顧客支付的每個訂單的平均運費作為輸出參數，返回顧客的訂單數作為傳回值。

   CREATE PROCEDURE usp_TestParameters@CustID CHAR(5),@AvgFreight MONEY OUTPUTASSELECT @AvgFreight = AVG(Freight) FROM Orders WHERE CustomerID = @CustIDSELECT * FROM Orders WHERE CustomerID = @CustIDRETURN @@ROWCOUNT

2. 重複上述步驟 1 到 6，將按鈕的 Click 事件程序替換為以下代碼：

   OdbcConnection cn;try{cn = new OdbcConnection("Driver={SQL Server};Server=servername;Database=Northwind;Trusted_Connection=Yes");OdbcCommand cmd = new OdbcCommand("{? = call usp_TestParameters (?, ?)}", cn);OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);prm.Direction = ParameterDirection.ReturnValue;prm = cmd.Parameters.Add("@CustomerID", OdbcType.Char, 5);prm.Value = "ALFKI";prm = cmd.Parameters.Add("@AvgFreight", OdbcType.Double);prm.Direction = ParameterDirection.Output;cn.Open();OdbcDataReader dr = cmd.ExecuteReader();while (dr.Read())Console.WriteLine(dr.GetString(0));dr.Close();cn.Close();Console.WriteLine("Average Freight (output param):{0}", cmd.Parameters[2].Value);Console.WriteLine("Order Count (return value):{0}", cmd.Parameters[0].Value);}catch (OdbcException o) {MessageBox.Show(o.Message.ToString());}

3. 運行該項目。此代碼調用在上述步驟 1 中建立的"usp_TestParameters"預存程序，傳遞 CustomerID 作為單輸入參數，並返回一個結果集和一個輸出參數作為傳回值。在輸出視窗，您應看到羅斯文顧客 ALFKI 訂購的產品列表、顧客為每個訂單支付的平均運費和訂單數。

使用 ODBC .NET 提供者和 Visual C# .NET 執行 SQL 參數化預存程序