HOW TO:使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程

原创 2004年10月11日 14:14:00

HOW TO:使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程

対象製品
本文的发布号曾为 CHS310070
有关本文的 Microsoft Visual Basic .NET 版本,请参见 308049
有关本文的 Microsoft Visual C++ .NET 版本,请参见 310071
有关本文的 Microsoft Visual J# .NET 版本,请参见 320627

本文引用下面的 Microsoft .NET 框架类库名称空间:
  • System.Data.SqlClient
  • System.Data.OleDb

本任务的内容

概要

有多种方法使用 ADO.NET 调用存储过程并获得返回值和返回参数,包括:
  • 使用 DataSet 对象,在获得返回值和返回参数之外,还可以收集并使用返回的数据行。
  • 使用 DataReader 对象收集返回的行,遍历这些行,然后收集返回值和返回参数。
  • 使用 ExecuteScalar 方法返回结果中第一行的第一列的值,以及返回值和返回参数。这对于聚合函数特别有用。
  • 使用 ExecuteNonQuery 方法只返回参数和返回值。任何返回的行都将被丢弃。这对于执行操作查询特别有用。
本文演示最后三个方法,并使用了 SqlCommandOleDbCommand 这两个对象。确保只复制对应于您在使用的托管提供程序的代码。如果您不确定应该使用哪个托管提供程序,请访问下面的 Microsoft Developer Network Web 站点:

.NET 数据提供程序
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 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. SystemSystem.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. SystemSystem.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 的值是存储过程的统计函数的结果。
返回页首

使用 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. SystemSystem.Data 名称空间使用 using 语句,这样,在后面的代码中就不需要在这些名称空间中限定声明了。将此代码添加到"窗体"代码模块的顶部。请确保只复制对应于您所选的提供程序的代码。 SQL 客户机
    using System.Data.SqlClient;
    OLE DB 数据提供程序
    using System.Data.OleDb;
  4. 用以下代码替换 Form1 代码模块中 private Form_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) 和返回参数的值。
返回页首

C#二十六 使用Ado.Net调用存储过程

存储过程是连接式访问数据库的一种延伸,主要是通过命令对象调用数据库系统中的存储过程来完成的。存储过程可以带参数,也可以不带参数,可以返回结果页可以没有返回结果。存储过程执行速度快、允许模块化程序设计并...
  • zhangchen124
  • zhangchen124
  • 2016年05月07日 19:05
  • 2635

ADO.NET调用存储过程,过程比较全

     一: 执行不带返回参数(Input)的存储过程           1: 首先在数据库写个存储过程, 如创建个 addUser存储过程。                  Create Pr...
  • lee576
  • lee576
  • 2008年03月12日 22:02
  • 5570

【ASP.NET开发】ADO.NET调用带输出参数的存储过程

public static void QueryList() { SqlConnection con = new SqlConnection("数据库连接字符串...
  • yisuowushinian
  • yisuowushinian
  • 2012年09月10日 21:16
  • 4084

使用ADO.NET对象调用存储过程的输入和输出参数

使用ADO.NET对象 总体上,操作SQL Server的程序和操作Access的程序方法一致。只需要做3个地方的修改,就可以把操作Access数据库的程序改成操作SQL Server的程序。 (1)...
  • godpreserve
  • godpreserve
  • 2008年04月24日 15:04
  • 1032

使用ADO.NET执行带参数的Sql语句

不带参数的SQL语句重载通用Update方法 /// /// 执行增、删、改 (带参数的SQL语句) /// /// /// ...
  • qq_36482772
  • qq_36482772
  • 2017年09月04日 04:02
  • 579

c#调用带输出参数的存储过程

我会坚持经常就一些比较典型的问题发表博客,同学们可以长上来看看,有什么问题及时来反映。 第一篇,c#调用带输出参数的存储过程。 1.存储过程的意义 sql语句的执行大致包括,语法和语句分析,...
  • mynewdays
  • mynewdays
  • 2011年09月09日 00:16
  • 13110

ado.net获取存储过程的返回值

CREATE proc sp_uptmp @tmpName varchar(50),@srcPos varchar(255)as Begin TRAN     insert into t_templa...
  • feng2375
  • feng2375
  • 2007年12月20日 15:14
  • 876

C#调用带参数的存储过程

注:我使用的数据库为SQL2005,先看下我的存储过程; create procedure spAddRenter @name varchar(20), @contact varchar(100...
  • friendan
  • friendan
  • 2013年05月08日 23:23
  • 894

VB.net数据库编程(06):调用存储过程(带参和不参数情况)

先建立一个无参的存储过程,为下面第一个无参调用作准备: [sql] view plaincopyprint? use Sales   go  ...
  • u014366427
  • u014366427
  • 2014年03月26日 11:12
  • 902

c#.net 文本编辑控件——使用 Visual C# .NET 打印 RichTextBox 控件的内容

原文连接: http://support.microsoft.com/default.aspx?scid=kb;zh-cn;812425
  • junherry
  • junherry
  • 2009年08月26日 17:57
  • 1084
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HOW TO:使用 ADO.NET 和 Visual C# .NET 调用带参数的存储过程
举报原因:
原因补充:

(最多只允许输入30个字)