ADO.NET呼叫預存程序

一: 執行不帶回傳參數(Input)的預存程序
1： 首先在資料庫寫個預存程序， 如建立個 addUser預存程序。
Create Proc addUser
@ID int,
@Name varchar(20),
@Sex varchar(20)
As
Insert Into Users Values( @ID, @Name,@Sex )

Insert Into Users Values( @ID, @Name,@Sex )

Insert Into Users Values( @ID, @Name,@Sex )
Insert Into Users Values( @ID, @Name,@Sex )
且初始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）
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. SqlDbType.Sarcc param2.Size = 10;// 輸出參數的Sql型別

param2.Direction = ParameterDirection.Output; //指定此參數物件為輸出參數型別

cmd.Parameters.Add( param2 );

4：SqlCommand,4：SqlCommand執行Sql的函數。如：
cmd.ExecuteNonQuery();
MessageBox.Show( param2.Value.ToString() ); // 輸出輸出參數的值

輸入參數的預存程序的範例:

try
{

輸入參數的儲存過程的範例:

try
{
();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "addUser";

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

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

cmd.Parameters.Add( paramters.Add( param). );

param = new SqlParameter();

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

if ( cmd.ExecuteNonQuery() == 1 )
{
MessageBox.Show( "新增成功" );
}
else
{
MessageBox.Show(");
catch( SqlException ex )
{
MessageBox.Show( ex.Message );
}

輸出參數的預存程序的範例:
try
{

SllCommand cmd = newSft); ";

cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

SqlParameter param1 = new SqlParameter( "@ID", txtID.Text );
cmd.Parameters.Addsgmparamah = new SqlParameter();
param2.ParameterName = "@Suc";
param2.SqlDbType = SqlDbType.VarChar;
param2.Size = 10;
param2.Direction = parameterDirection. ;

cmd.ExecuteNonQuery();

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

MessageBox.Show( ex.Message );
}

在ado.net 裡面取得回傳值的方法為(c#):
--------------------- ---------------------------------------
SqlConnection dbconn = new SqlConnection(connStr);

。 .Add("@srcPos",SqlDbType.VarChar);

SqlParameter rtnval = cmd.Parameters.Add("rval",SqlDbType.Int);

tmpName.Direction =InmeterDirection.Int);

tmpName.Direction =InmeterDirection.Inputir.Direction. ;

rtnval.Direction = ParameterDirection.ReturnValue;

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

tmpid = (int)rtnvalid = (int)。此處即為回傳值

假設有預存程序如下:
----------------------------------- ----------

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

Begin TRAN
insert into t_template values(@tmpName,Begin TRAN
insert into t_template values(@tmpName,Begin TRAN

insert into t_？ return isnull(@@identity,0)

GO

------------------------------------ ------------------------
在ado.net 裡面取得回傳值的方法為(c#):
--------- -------------------------------------------------- -
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("sp_uptmp",dbconn);
cmd.CommandType = CommandType.StoredProcedure;Parg SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add("@srcPos",SqlDbType.VarChar);

SqlParameter rtnval = cmTyped.Parameters.Add("rvalueD.DlType; ParameterDirection.Input;

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

tmpName.Value = "";

srcPos.

tmpName.Value = "";jdbconnet.m. ();

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”
每」2”
呼叫一個預存程序都要手動添加這個預存程序的所有參數，用自己的腦力保證參數的資料類型和預存過程中的參數的資訊的一致性。
Command.Parameters物件有一個Refresh方法，這個方法的作用時讀取目前Command物件所需的所有參數的名稱和資料類型，用這個方法就可以寫成一個呼叫所有儲存過程的共用函數，現面這個函數完成了一個傳回結果集的預存程序的通用函數。很簡單可以根據需要細化。

‘在VisualBasic6.0調試通過。
Function GetRsByPro(strConnString As String, strProName As String, arjParameter() As String)
' 傳回查詢的記錄集

' strConnString 資料連線字串

' strProName 儲存過程名稱
' arjParameter(e) 資料庫編號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. Refresh
If UBound(arjParameter) Cmd.Parameters.Count Then
Debug.Print "參數個數不對"
Exit Function
End If

'給預存程序參數賦值
For i = 08C. - 1
Cmd.Parameters(i).Value = arjParameter(i)
Next

'設定Recordset物件
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorType = 3
Rs.LockType = 3
Rs.CursorType = 303
. Rs.Open

'返回結果集
Set GetRsByPro = Rs

'關閉資料來源
Con.Close
Set Con = Nothing
errMsg:
Debug.Print Err.Description
Func As New Recordset
StrConnString=””
StrProName=”pro_GetAllUser”

Dim arjParameter(1)

arjParameter(0)=”1”
arjParameter(1)=”山東” ))

用相同的方法在.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. 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;using System.Data.OleDb ;
using System.Collections; namespace Erp

{

public sealed class TestSqlAccess
{
#region 取得預存程序參數集合
public static SqlParameter [] getParameters(string cnString,string proName)
{Connection
. );
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.StoredProcedure ;
comm.CommandText =proName;
SqlParameter[comm.Parameters.Count];
for (int i=0;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 ararPrm #endregion


#region 執行Command物件回傳DataSet

/////可以呼叫微軟提供的那個SqlHelper類別..

參數

　　您可以使用DataReader 物件傳回唯讀的僅向前型資料流。 DataReader 中所包含的資訊可以來自一個預存程序。本範例使用 DataReader 物件執行具有輸入參數和輸出參數的預存程序，然後遍歷傳回記錄，查看回傳參數。

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

　　(

　　@au_idIN 111), 11),　)

　　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氏

　　A.au_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 客戶端系統"Data Source=server;integrated " +

　　"Security=sspi;initial catalog=pubs;");

　　SqlCommand testCMD = new SqlCommand

〜　SqlCommand testCMD = new SqlCommand

,$test. = CommandType.StoredProcedure ;

　　SqlParameter RetVal = testCMD.Parameters.Add

　　("RetVal", SqlDbType.Int);

． testCMD.Parameters.Add

　　("@au_idIN" , SqlDbType.VarChar, 11);

　　IdIn.Direction = ParameterDirection.Input;

　　SqlParameter NumTitles = testCMD.Parameters.Addamml. );

　　NumTitles.Direction = ParameterDirection .Output;

　　IdIn.Value = "213-46-8915";

　　PubsConn.Open();

　　㜀接"Book Titles for this Author:" );

　　while (myReader.Read())

　　{

　　Console.WriteLine ("{0}", myReader.GetString (I)); ();

　　Console .WriteLine("Number of Rows:" + NumTitles.Value );

　　Console.WriteLine("Return Value:" + RetVal.Value);

　　OLE Con​​nection 資料提供者 Pubs DB SQLOLEDB;Data Source=server;" +

　　"integrated Security=sspi;initial catalog=pubs;");

　　OleDbCommand testCMD = new OleDbCommand

　　OleDbCommand testCMD = new OleDbCommand

『㟎〜㟜Ｓ);　testCMD.CommandType = CommandType. StoredProcedure;

　　OleDbParameter RetVal = testCMD.Parameters.Add

（“RetVal”，OleDbType.Integer）;RetVal.Direction = ParameterDirection.ReturnValue;

OleDbParameter IdIn = testCMD.Parameters.Add

IdIn.Direction = ParameterDirection.Input;

OleDbParameter NumTitles = testCMD.Parameters.Add

("@numtitlesout", OleDbType.VarChar, 111);

IdIn.Value = "213-46-第8915章{

佛Console.WriteLine("{0}", myReader.GetString(2));

佛};

myReader.Close);

佛Console.WriteLine("行數:" + NumTitles.Value ) ;

Console.WriteLine("Return Value:" + RetVal.Value);

5.ConnectionConnection:" + RetVal.Value);

5.ConnectionConnection指向執行SQL Server的電腦。 、傳回值5 和輸出參數，其中包含記錄的數目(2)。記錄。