如何使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程

 如何使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程

概要

有多种方法使用 ADO.NET 调用存储过程并获得返回值和返回参数，其中包括：

•	使用 DataSet 对象，在获得返回值和返回参数之外，还可以收集并使用返回的数据行。
•	使用 DataReader 对象收集返回的行，遍历这些行，然后收集返回值和返回参数。
•	使用 ExecuteScalar 方法返回结果中第一行的第一列的值以及返回值和返回参数。这对于聚合函数特别有用。
•	使用 ExecuteNonQuery 方法只返回参数和值。任何返回的行都将被丢弃。这对于执行操作查询特别有用。

本文演示后三种方法，并使用 SqlCommand 和 OleDbCommand 这两个对象。确保只复制对应于您在使用的托管提供程序的代码。如果您不确定应该使用哪个托管提供程序，请访问下面的 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 = @@Rowcount
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 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(); VS.NET里++++++++++++++++++++++++++++++++++++++++++++++ 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); +++++++++++++++++++++++++++++++++++++++++++++++ =ASP.NET里 +++++++++++++++++++++++++++++++++++++++++++++++++ testCMD.ExecuteNonQuery(); mylabel.Text="返回值为"+NumTitles.Value.ToString(); PubsConn.Close(); +++++++++++++++++++++++++++++++++++++++++++++ 使用 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) 和返回参数的值。

 

使用 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 函数的结果。