C#中使用SQL預存程序說明

一、表的建立sql語句：

CREATE TABLE [tree] (
    [node_id] [int] NOT NULL ,
    [node_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [pat_id] [int] NULL ,
    [url] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [icon] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [memo] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
    CONSTRAINT [tree_pk] PRIMARY KEY  CLUSTERED
    (
        [node_id]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO

二、建立一個有輸入、輸出、傳回值參數的預存程序：

代碼

create proc proc_out @uid int,@output varchar(200) output
as
--select結果集
select * from tree where node_id>@uid
--對輸出參數進行賦值
set @output='記錄總數：'+convert(varchar(10),(select count(*) from tree))
--使用return，給預存程序一個傳回值。
return 200;
go

三、在C#中，操作預存程序：

3.1 使用帶有參數的sql語句

代碼

private void sql_param()
 {

 SqlConnection conn=new SqlConnection("server=.;uid=sa;pwd=sa;database=sms");

 //在sql語句當中引入了@myid參數
 string sql="select * from tree where uid>@myid";
 SqlCommand comm=new SqlCommand(sql,conn);

 //使用comm的Parameters屬性的add方法，對上述的@myid參數進行定義和賦值
 //SqlDbType類提供了與SqlServer資料類型一致的資料庫類型
 SqlParameter sp=comm.Parameters.Add("@myid",SqlDbType.Int);
 sp.Value=10;//對輸入參數賦值

 //Command對象預設的執行方式為Text，不寫下句亦可
 comm.CommandType=CommandType.Text;

 //將Command對象作為DataAdapter的參數傳進
 SqlDataAdapter da=new SqlDataAdapter(comm);
 DataSet ds=new DataSet();
 da.Fill(ds);

 //綁定資料到DataGrid1控制項上
 this.Dgd_student.DataSource=ds;
 this.Dgd_student.DataBind();

 }

3.2 預存程序的使用標準版

代碼

private void sql_proc()
 {


 SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=sms");
 string sql = "proc_out";
 SqlCommand comm = new SqlCommand(sql, conn);

 //把Command執行類型改為預存程序方式，預設為Text。
 comm.CommandType = CommandType.StoredProcedure;

 //傳遞一個輸入參數，需賦值
 SqlParameter sp = comm.Parameters.Add("@uid", SqlDbType.Int);
 sp.Value = 4;

 //定義一個輸出參數，不需賦值。Direction用來描述參數的類型
 //Direction預設為輸入參數，還有輸出參數和傳回值型。
 sp = comm.Parameters.Add("@output", SqlDbType.VarChar, 50);
 sp.Direction = ParameterDirection.Output;

 //定義過程的傳回值參數，過程執行完之後，將把過程的傳回值賦值給名為myreturn的Paremeters賦值。
 sp = comm.Parameters.Add("myreturn", SqlDbType.Int);
 sp.Direction = ParameterDirection.ReturnValue;

 //使用SqlDataAdapter將自動完成資料庫的開啟和關閉過程，並執行相應t-sql語句或預存程序
 //如果預存程序只是執行相關操作，如串聯刪除或更新，使用SqlCommand的execute方法即可。
 SqlDataAdapter da = new SqlDataAdapter(comm);
 DataSet ds = new DataSet();
 da.Fill(ds);


 //在執行完預存程序之後，可得到輸出參數 
 string myout = comm.Parameters["@output"].Value.ToString();

 //列印輸出參數：
 Response.Write("列印輸出參數：" + myout);

 //列印預存程序傳回值
 myout = comm.Parameters["myreturn"].Value.ToString();
 Response.Write("預存程序傳回值：" + myout);

 this.Dgd_student.DataSource = ds;
 this.Dgd_student.DataBind();



 }

3.3 預存程序的使用最簡版：

代碼

private void sql_jyh()
 {

 //最簡寫法，把預存程序當作t-sql語句來使用，文法為：exec 過程名 參數

 SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=SMS");
 string sql = "execute proc_out 3,'12'";
 SqlCommand comm = new SqlCommand(sql, conn);

 //使用SqlDataAdapter將自動完成資料庫的開啟和關閉過程，並執行相應t-sql語句或預存程序
 //如果預存程序只是執行相關操作，如串聯刪除或更新，使用SqlCommand的execute方法即可。
 SqlDataAdapter da = new SqlDataAdapter(comm);
 DataSet ds = new DataSet();
 da.Fill(ds);

 //綁定資料
 this.Dgd_student.DataSource = ds;
 this.Dgd_student.DataBind();

 }

帶多個參數 的情況
create proc proc_out2 @uid int,@patid int,@output varchar(200) output

as

--select結果集

select * from tree where node_id>@uid and pat_id = @patid


--對輸出參數進行賦值

set @output='記錄總數：'+convert(varchar(10),(select count(*) from tree))

--使用return，給預存程序一個傳回值。

return 200;

go


 private void More()
 {
 SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=sms");
 string sql = "proc_out2";
 SqlCommand cmd = new SqlCommand(sql, conn);
 //把Command執行類型改為預存程序方式，預設為Text。
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.Add("@uid",SqlDbType.Int).Value = 1 ;
 cmd.Parameters.Add("@patid", SqlDbType.Int).Value = 1;
 cmd.Parameters.Add("@output", SqlDbType.VarChar, 100);
 cmd.Parameters["@output"].Direction = ParameterDirection.Output;

 //cmd.Parameters.Add(new SqlParameter("@uid", SqlDbType.Int)).Value = 1;//"A1**";
 //cmd.Parameters.Add(new SqlParameter("@patid", SqlDbType.Int)).Value = 1; //"A2**"; 
 //SqlParameter param = new SqlParameter("@output", SqlDbType.VarChar, 88);
 //param.Direction = ParameterDirection.Output;
 // cmd.Parameters.Add(param); 

 //cmd.ExecuteNonQuery(); 
 DataSet ds = new DataSet();
 SqlDataAdapter da = new SqlDataAdapter(cmd); 
 da.Fill(ds);
 string rtnstr = cmd.Parameters["@output"].Value.ToString();
 Response.Write("列印輸出參數：" + rtnstr);
 this.Dgd_student.DataSource = ds;
 this.Dgd_student.DataBind(); 
 }

出處：http://www.cnblogs.com/jayleke/archive/2010/07/10/1774758.html