在C#中使用SQL存储过程说明

转自：http://www.cnblogs.com/NetSos/archive/2010/05/27/1745380.html

一、表的创建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(); 
    }