有多种方法使用 ADO.NET 调用存储过程并获得返回值和返回参数,其中包括:
• | 使用 DataSet 对象,在获得返回值和返回参数之外,还可以收集并使用返回的数据行。 |
• | 使用 DataReader 对象收集返回的行,遍历这些行,然后收集返回值和返回参数。 |
• | 使用 ExecuteScalar 方法返回结果中第一行的第一列的值以及返回值和返回参数。这对于聚合函数特别有用。 |
• | 使用 ExecuteNonQuery 方法只返回参数和值。任何返回的行都将被丢弃。这对于执行操作查询特别有用。 |
本文演示后三种方法,并使用
SqlCommand 和
OleDbCommand 这两个对象。确保只复制对应于您在使用的托管提供程序的代码。如果您不确定应该使用哪个托管提供程序,请访问下面的 Microsoft Developer Network 网站:
在本文的每个示例中,这些参数都被添加到
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 on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.au_id=@au_idIN
set @numTitlesOUT = @@Rowcount
return (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 on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.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. | 将下面的代码添加到 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_fnam
where 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) 和返回参数的值。 |