ADO.NET调用存储过程

一: 执行不带返回参数(Input)的存储过程
1： 首先在数据库写个存储过程， 如创建个 addUser存储过程。
Create Proc addUser
@ID int,
@Name varchar(20),
@Sex varchar(20)
As
Insert Into Users Values( @ID, @Name,@Sex )

2：创建SqlCommand对象，并初始SqlCommand对象如：
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "addUser"; //制定调用哪个存储过程
cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程， 默认的为Sql语句。
cmd.Connection = con; // 设定连接

3:向SqlCommand对象添加存储过程参数
SqlParameter param = new SqlParameter( ); //定义一个参数对象
param.ParameterName = "@ID"; //存储过程参数名称
param.Value = txtID.Text.Trim(); // 该参数的值
cmd.Parameters.Add( param ); // SqlCommand对象添加该参数对象

param = new SqlParameter( "@Name", txtName.Text.Trim() ); // 简写方式
cmd.Parameters.Add( param );

4：SqlCommand对象调用执行Sql的函数。如：
cmd.ExecuteNonQuery();

二:执行带返回参数(Output)的存储过程
1: 首先在数据库写个存储过程， 如创建个 queryUser存储过程。
alter Proc queryUser
@ID int,
@Suc varchar(10) output
As
select @Suc= 'false'
if exists( Select * From users where u_id = @ID )
select @Suc = 'success'

2：创建SqlCommand对象，并初始SqlCommand对象如：
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser"; //制定调用哪个存储过程
cmd.CommandType = CommandType.StoredProcedure; // 制定Sql命令类型是存储过程， 默认的为Sql语句。
cmd.Connection = con; // 设定连接

3:向SqlCommand对象添加存储过程参数
SqlParameter param1 = new SqlParameter( "@ID", txtID.Text ); // 添加输入参数
cmd.Parameters.Add( param1 );

SqlParameter param2 = new SqlParameter(); // 添加输出参数
param2.ParameterName = "@Suc"; // 名称
param2.SqlDbType = SqlDbType.VarChar; // 输出参数的Sql类型
param2.Size = 10;// 输出参数的Sql类型大小
param2.Direction = ParameterDirection.Output; //指定该参数对象为输出参数类型
cmd.Parameters.Add( param2 );

4：SqlCommand对象调用执行Sql的函数。如：
cmd.ExecuteNonQuery();
MessageBox.Show( param2.Value.ToString() ); // 输出输出参数的值

输入参数的存储过程的示例:
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addUser";

SqlParameter param = new SqlParameter( );
param.ParameterName = "@ID";
param.Value = txtID.Text.Trim();
cmd.Parameters.Add( param );

param = new SqlParameter( "@Name", txtName.Text.Trim() );
cmd.Parameters.Add( param );

param = new SqlParameter();
param.ParameterName = "@Sex";
param.Value = txtSex.Text.Trim();
cmd.Parameters.Add( param );

//da.InsertCommand = cmd;

if ( cmd.ExecuteNonQuery() == 1 )
{
MessageBox.Show( "添加成功" );
}
else
{
MessageBox.Show("失败");
}
}
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}

输出参数的存储过程的示例:
try
{
SqlCommand cmd = new SqlCommand( );
cmd.CommandText = "queryUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

SqlParameter param1 = new SqlParameter( "@ID", txtID.Text );
cmd.Parameters.Add( param1 );

SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@Suc";
param2.SqlDbType = SqlDbType.VarChar;
param2.Size = 10;
param2.Direction = ParameterDirection.Output;
cmd.Parameters.Add( param2 );

cmd.ExecuteNonQuery();

MessageBox.Show( param1.Value.ToString() );
MessageBox.Show( param2.Value.ToString() );

}
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}

在 ado.net 里面获取返回值的方法为(c#):
------------------------------------------------------------
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tmpName = cmd.Parameters.Add("@tmpName",SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add("@srcPos",SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);

tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input;
rtnval.Direction = ParameterDirection.ReturnValue;

tmpName.Value = "";
srcPos.Value = "";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();

tmpid = (int)rtnval.Value; //此处即为返回值

假设有存储过程如下:
---------------------------------------------

CREATE proc sp_uptmp @tmpName varchar(50),@srcPos varchar(255)
as

Begin TRAN
insert into t_template values(@tmpName,@srcPos)
COMMIT

return isnull(@@identity,0)
GO

------------------------------------------------------------
在 ado.net 里面获取返回值的方法为(c#):
------------------------------------------------------------
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tmpName = cmd.Parameters.Add("@tmpName",SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add("@srcPos",SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);

tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input;
rtnval.Direction = ParameterDirection.ReturnValue;

tmpName.Value = "";
srcPos.Value = "";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();

tmpid = (int)rtnval.Value; //此处即为返回值

在ADO环境下，调用存储过程查询数据时常规做法为：
1 创建Connection Command对象
2 打开连接,给Command赋参数的名称、数据类型、值
3 执行Command对象
4 返回给Recordset对象交给客户端
这样做每调用一次存储过程都要按照存储过程中的参数的数据类型创建Parameters对象
比如存储过程需要两个参数 @ID int、@Name varchar(10)就需要
‘创建参数
cmd.Parameters.Append cmd.CreateParameter("@ID",adInteger,adParamInput,4)
cmd.Parameters.Append cmd.CreateParameter("@Name",adVarChar,adParamInput,10)
‘给参数赋值
cmd("@State") = 1
cmd("@WhereT")=”2”
每调用一个存储过程都要手工添加这个存储过程的所有参数，用自己的脑力保证参数的数据类型和存储过程中的参数的信息的一致性。
Command.Parameters对象有一个Refresh方法，这个方法的作用时读取当前Command对象需要的所有参数的名称和数据类型，用这个方法就可以写成一个调用所有存储过程的共用函数，现面这个函数完成了一个返回结果集的存储过程的通用函数。很简单可以根据需要细化。

‘在VisualBasic6.0调试通过。
Function GetRsByPro(strConnString As String, strProName As String, arjParameter() As String)
' 返回 查询的记录集
' strConnString 数据连接串
' strProName 存储过程名
' arjParameter() 存储过程需要的数组
On Error GoTo errMsg
'创建ADO对象
Dim Cmd As New Command
' ASP Con = Server.CreateObject("ADODB.Connection")
Dim Con As New Connection
' ASP Set Cmd = Server.CreateObject("ADODB.Command")
Dim Rs As New Recordset
' ASP Set rs = Server.CreateObject("ADODB.Recordset")

'打开数据库
Con.Open strConnString
Set Cmd.ActiveConnection = Con
Cmd.Commandtype = adCmdStoredProc
Cmd.Parameters.Refresh
If UBound(arjParameter) <> Cmd.Parameters.Count Then
Debug.Print "参数个数不对"
Exit Function
End If

'给存储过程参数赋值
For i = 0 To Cmd.Parameters.Count - 1
Cmd.Parameters(i).Value = arjParameter(i)
Next

'设置Recordset对象
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorLocation = 3
Set Rs.Source = Cmd
Rs.Open

'返回结果集
Set GetRsByPro = Rs

'关闭数据源
Con.Close
Set Con = Nothing
errMsg:
Debug.Print Err.Description
End Function

‘调用Demo
Dim Rs As New Recordset
StrConnString=””
StrProName=”pro_GetAllUser”
Dim arjParameter(1)
arjParameter(0)=”1”
arjParameter(1)=”山东”
Set Rs= GetRsByPro(strConnString, strProName, arjParameter())

用相同的方法在.NET开发环境里也可以建立一个通用的方法调用存储过程。
在ADO.NET里不管是OleDbCommand.Parameters对象还是SqlCommand.Parameters对象都没有Refresh方法读取存储过程的参数信息，.NET在OleDbCommandBuilder类里提供了一个DeriveParameters静态方法可以实现相同的功能。
.NET SDK里关于DeriveParameters的描述
“使用在 SqlCommand 中指定的存储过程的参数信息，填充指定的 SqlCommand 对象的 Parameters 集合。”

SqlConnection Conn=new SqlConnection(cnString);
Conn.Open();
SqlCommand Comm=new SqlCommand();
Comm.Connection =conn;
Comm.CommandType =CommandType.StoredProcedure ;
Comm.CommandText =proName;
SqlCommandBuilder.DeriveParameters(comm);
//经过这个方法后SqlCommand对象的SqlParameters对象已经帮定了存储过程中的信息了
实现执行任意一个存储过程返回一个DataSet对象的具体函数代码
文件名 ：TestSqlAccess.cs
// 在vs.net调试通过
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.OleDb ;
using System.Collections;

namespace Erp
{
public sealed class TestSqlAccess
{
#region 获取存储过程参数集合
public static SqlParameter [] getParameters(string cnString,string proName)
{
SqlConnection conn=new SqlConnection(cnString);
conn.Open();
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.StoredProcedure ;
comm.CommandText =proName;

SqlCommandBuilder.DeriveParameters(comm);
SqlParameter [] arPrm=new SqlParameter[comm.Parameters.Count];
for (int i=0;i<comm.Parameters.Count;i )
{
arPrm[i]=new SqlParameter();
arPrm[i].SqlDbType =comm.Parameters[i].SqlDbType ;
arPrm[i].ParameterName=comm.Parameters[i].ParameterName;
arPrm[i].Size =comm.Parameters[i].Size;
}
return arPrm;
}
#endregion

#region 执行Command对象返回DataSet

/////可以调用微软提供的那个SqlHelper类..

#endregion 执行Command对象返回DataSet

使用 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，则不必为了查看返回参数而遍历所有记录。