如何使用 ADO.NET 和 Visual C# .NET 調用帶參數的預存程序

有多種方法使用 ADO.NET 調用預存程序並獲得傳回值和返回參數，其中包括：

• 使用 DataSet 對象，在獲得傳回值和返回參數之外，還可以收集並使用返回的資料行。
• 使用 DataReader 對象收集返回的行，遍曆這些行，然後收集傳回值和返回參數。
• 使用 ExecuteScalar 方法返回結果中第一行的第一列的值以及傳回值和返回參數。這對於彙總函式特別有用。
• 使用 ExecuteNonQuery 方法只返回參數和值。任何返回的行都將被丟棄。這對於執行操作查詢特別有用。

本文示範後三種方法，並使用 SqlCommand 和 OleDbCommand 這兩個對象。確保只複製對應於您在使用的Managed 提供者的代碼。如果您不確定應該使用哪個Managed 提供者，請訪問下面的 Microsoft Developer Network 網站：

.NET 資料提供者


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetproviders.asp)

在本文的每個樣本中，這些參數都被添加到 Command 對象的 Parameters 集合中。使用 SqlCommand 對象時，您不必按照任何特定的順序添加參數，但必須保證參數名正確。使用 OleDbCommand 對象時，必須按照正確的順序添加參數，不能按照名稱使用參數。

回到頂端 使用 DataReader 返回行和參數

您可以使用 DataReader 對象返回唯讀僅向前型資料流。DataReader 中所包含的資訊可以來自一個預存程序。本樣本使用 DataReader 對象運行帶有輸入和輸出參數的預存程序，然後遍曆返回記錄，以查看返回參數。

1. 在運行 Microsoft SQL Server 的伺服器上建立下面的預存程序：

   Create Procedure TestProcedure(  @au_idIN varchar (11),  @numTitlesOUT Integer OUTPUT)AS select A.au_fname, A.au_lname, T.title from authors as A join titleauthor as TA onA.au_id=TA.au_idjoin titles as Ton T.title_id=TA.title_idwhere A.au_id=@au_idINset @numTitlesOUT = @@Rowcountreturn (5)                                         

2. 建立一個新的 Visual C# .NET Windows 應用程式項目。
3. 對 System 和 System.Data 命名空間使用 using 語句，這樣，在後面的代碼中就無需限定這些命名空間中的聲明了。將此代碼添加到"表單"代碼模組的頂部。請確保只複製對應於您所選的提供者的代碼。SQL 用戶端

   using System.Data.SqlClient;                                        

   OLE DB 資料提供者

   using System.Data.OleDb;                                        

4. 用以下代碼替換 private Form_Load 事件中的代碼：SQL 用戶端

   SqlConnection PubsConn = new SqlConnection ("Data Source=server;integrated " + "Security=sspi;initial catalog=pubs;");SqlCommand testCMD = new SqlCommand ("TestProcedure", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add    ("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add   ("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;SqlParameter NumTitles = testCMD.Parameters.Add    ("@numtitlesout", SqlDbType.VarChar, 11);NumTitles.Direction = ParameterDirection.Output ;        IdIn.Value = "213-46-8915";PubsConn.Open();SqlDataReader myReader = testCMD.ExecuteReader();Console.WriteLine ("Book Titles for this Author:");while (myReader.Read())    {     Console.WriteLine ("{0}", myReader.GetString (2));   };myReader.Close() ;Console.WriteLine("Number of Rows: " + NumTitles.Value );Console.WriteLine("Return Value: " + RetVal.Value);                                        

   OLE DB 資料提供者

   OleDbConnection PubsConn = new OleDbConnection    ("Provider=SQLOLEDB;Data Source=server;" +    "integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand    ("TestProcedure", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add    ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add    ("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;OleDbParameter NumTitles = testCMD.Parameters.Add    ("@numtitlesout", OleDbType.VarChar, 11);NumTitles.Direction = ParameterDirection.Output;        IdIn.Value = "213-46-8915";PubsConn.Open();OleDbDataReader myReader = testCMD.ExecuteReader();Console.WriteLine ("Book Titles for this Author:");while (myReader.Read())    {     Console.WriteLine ("{0}", myReader.GetString (2));   };myReader.Close() ;Console.WriteLine("Number of Rows: " + NumTitles.Value );Console.WriteLine("Return Value: " + RetVal.Value);                                        

5. 修改 Connection 對象的連接字串，以便指向運行 SQL Server 的電腦。
6. 運行上述代碼。注意，DataReader 檢索記錄並返回參數值。可以使用 DataReader 對象的 Read 方法遍曆返回的記錄。

   "輸出"視窗顯示兩本書的標題、傳回值 5 和輸出參數，其中包含記錄的數目 (2)。注意，必須關閉代碼中的 DataReader 才能看到參數值。另外還要注意，如果關閉了 DataReader，則不必為了查看返回參數而遍曆所有記錄。

回到頂端 使用 Command 對象的 ExecuteScalar 方法

可以使用 Command 對象的 ExecuteScalar 方法檢索參數值。另外，ExecuteScalar 返回該預存程序的第一行的第一列。這對於彙總函式特別有用，如下例所示。

1. 在運行 SQL Server 的伺服器上建立下面的預存程序：

   Create Procedure TestProcedure2(  @au_idIN varchar (11))As/* set nocount on */ select count (T.title) from authors as A join titleauthor as TA onA.au_id=TA.au_idjoin titles as Ton T.title_id=TA.title_idwhere A.au_id=@au_idINReturn(5)                                        

2. 建立一個新的 Visual C# .NET Windows 應用程式項目。
3. 對 System 和 System.Data 命名空間使用 using 語句，這樣，在後面的代碼中就無需限定這些命名空間中的聲明了。將此代碼添加到"表單"代碼模組的頂部。請確保只複製對應於您所選的提供者的代碼。SQL 用戶端

   using System.Data.SqlClient;                                        

   OLE DB 資料提供者

   using System.Data.OleDb;                                        

4. 將下面的代碼添加到 Form_Load 事件：SQL 用戶端

   string strCount;SqlConnection PubsConn = new SqlConnection    ("Data Source=server;integrated " +    "Security=sspi;initial catalog=pubs;");SqlCommand testCMD = new SqlCommand    ("TestProcedure2", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add    ("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add    ("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;        IdIn.Value = "213-46-8915";PubsConn.Open();strCount =testCMD.ExecuteScalar ().ToString() ;Console.WriteLine("Number of Rows: " + strCount );Console.WriteLine("Return Value: " + RetVal.Value);                                        

   OLE DB 資料提供者

   string strCount;OleDbConnection PubsConn = new OleDbConnection    ("Provider=SQLOLEDB;Data Source=server;" +    "integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand    ("TestProcedure2", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add    ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add    ("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";PubsConn.Open();strCount = testCMD.ExecuteScalar().ToString() ;Console.WriteLine("Number of Rows: " + strCount);Console.WriteLine("Return Value: " + RetVal.Value);                                        

5. 修改 Connection 對象的連接字串，以便指向運行 SQL Server 的電腦。
6. 運行上述代碼。注意，Command 對象的 ExecuteScalar 方法將返回參數。ExecuteScalar 還將返回所返回的行集中的第一行第一列的值。因此，intCount 的值是預存程序的 count 函數的結果。

回到頂端 使用 Command 對象的 ExecuteNonQuery 方法

此樣本使用 ExecuteNonQuery 方法執行查詢並返回參數值。ExecuteNonQuery 還返回在運行此查詢後受影響的記錄數。但是，ExecuteNonQuery 不從該預存程序返回任何行或列。

如果只需要知道更改的行數，那麼在使用 INSERT、UPDATE 或 DELETE 語句時，ExecuteNonQuery 方法特別有用。當預存程序中僅使用 SELECT 語句時，您將收到 -1，因為查詢不會影響任何行。

1. 在運行 SQL Server 的電腦上建立下面的預存程序：

   Create Procedure TestProcedure3(  @au_idIN varchar (11),  @au_fnam varchar (30))As/* set nocount on */ Update authors set au_fname = @au_fnamwhere au_id = @au_idin  return (5)                                        

2. 建立一個新的 Visual C# .NET Windows 應用程式項目。
3. 對 System 和 System.Data 命名空間使用 using 語句，這樣，在後面的代碼中就無需限定這些命名空間中的聲明了。將此代碼添加到"表單"代碼模組的頂部。請確保只複製對應於您所選的提供者的代碼。SQL 用戶端

   using System.Data.SqlClient;                                        

   OLE DB 資料提供者

   using System.Data.OleDb;                                        

4. 用以下代碼替換 Form1 代碼模組中 private Form1_Load 事件後面的代碼：SQL 用戶端

   string strRowAffect;SqlConnection PubsConn = new SqlConnection    ("Data Source=server;integrated Security=sspi;" +    "initial catalog=pubs;");SqlCommand testCMD = new SqlCommand    ("TestProcedure3", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;SqlParameter RetVal = testCMD.Parameters.Add    ("RetVal", SqlDbType.Int);RetVal.Direction = ParameterDirection.ReturnValue;SqlParameter IdIn = testCMD.Parameters.Add    ("@au_idIN", SqlDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;SqlParameter FnameIn = testCMD.Parameters.Add    ("@au_fnam", SqlDbType.VarChar, 30);FnameIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";FnameIn.Value = "Marjorie";PubsConn.Open();strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;Console.WriteLine("Number of Rows: " + strRowAffect );Console.WriteLine("Return Value: " + RetVal.Value);                                        

   OLE DB 資料提供者

   int intRowAffected;OleDbConnection PubsConn = new OleDbConnection    ("Provider=SQLOLEDB;Data Source=server;" +    "integrated Security=sspi;initial catalog=pubs;");OleDbCommand testCMD = new OleDbCommand    ("TestProcedure3", PubsConn);testCMD.CommandType = CommandType.StoredProcedure;OleDbParameter RetVal = testCMD.Parameters.Add    ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;OleDbParameter IdIn = testCMD.Parameters.Add    ("@au_idIN", OleDbType.VarChar, 11);IdIn.Direction = ParameterDirection.Input;OleDbParameter FnameIn = testCMD.Parameters.Add   ("@au_fname", OleDbType.VarChar, 30);FnameIn.Direction = ParameterDirection.Input;IdIn.Value = "213-46-8915";FnameIn.Value = "Marjorie";PubsConn.Open();intRowAffected = testCMD.ExecuteNonQuery();Console.WriteLine("Number of Rows affected: " + intRowAffected);Console.WriteLine(RetVal.Value);                                        

5. 修改 Connection 對象的連接字串，以便指向運行 SQL Server 的電腦。
6. 運行上述代碼。"輸出"視窗顯示受影響的行數 (intRowAffect) 和返回參數的值。

回到頂端
參考

有關其他資訊，請訪問下面的 MSDN 網站： .NET Framework 類庫簡介http://msdn.microsoft.com/library/defa...

有關其他資訊，請訪問下面的 MSDN 網站：

.NET Framework 類庫簡介


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconthenetframeworkclasslibrary.asp
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconthenetframeworkclasslibrary.asp)

使用 DataReader 檢索資料


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp)