C#调用存储过程详解(带返回值、参数输入输出等)

create procedure [dbo].[getnamebyid]
 @studentid varchar(8),
 @studentname nvarchar(50) output
as
begin
 select @studentname=studentname from student
  where studentid=@studentid
 if @@error<>0
 return -1
 else
 return 0
end

using (sqlconnection conn = new sqlconnection(connstr))
{
  try
  {
    sqlcommand cmd = new sqlcommand("getnamebyid", conn);
    cmd.commandtype = commandtype.storedprocedure;
    cmd.parameters.addwithvalue("@studentid", "09888888");　　//给输入参数赋值
    sqlparameter paroutput =cmd.parameters.add("@studentname", sqldbtype.nvarchar, 50);　　//定义输出参数
    paroutput.direction = parameterdirection.output;　　//参数类型为output
    sqlparameter parreturn = new sqlparameter("@return", sqldbtype.int);
    parreturn.direction = parameterdirection.returnvalue; 　　//参数类型为returnvalue
    cmd.parameters.add(parreturn);
    conn.open();
    cmd.executenonquery();
    messagebox.show(paroutput.value.tostring());  //显示输出参数的值
    messagebox.show(parreturn.value.tostring());　　//显示返回值
  }
  catch (system.exception ex)
  {
    messagebox.show(ex.message);
  }
}

create procedure addordertran
  @country nvarchar(100),
  @adds nvarchar(100),
  @ynames nvarchar(100),
  @pids nvarchar(100),
  @cellp nvarchar(100),
  @cphone nvarchar(100),
  @amounts nvarchar(100),
  @cartnumber nvarchar(100)
as
  declare @id int
  begin transaction
    insert into orders(order_country,order_adress,order_username,order_postid,cells,order_phone,total_pay,cartnumber,ispay)
      values (@country,@adds,@ynames,@pids,@cellp,@cphone,@amounts,@cartnumber,'0')
    select @id=@@identity
    insert into orders_item (ordernumber,productsid,products_color,products_price,order_qty,item_total)
      select @id,carts_item.productsid,carts_item.products_color,carts_item.products_price,carts_item.item_qty,carts_item.total_pay
      from carts_item where carts_item.cartnumber=@cartnumber
    delete carts_item where cartnumber=@cartnumber
    if @@error <> 0 --发生错误
    begin
      rollback transaction
      return 0
    end
    else
    begin
      commit transaction
      return @id  --执行成功
  end

#region 执行存储过程
sqlparameter[] param = new sqlparameter[]
{
   new sqlparameter("@country",country),
   new sqlparameter("@adds",adds),
   new sqlparameter("@ynames",ynames),
   new sqlparameter("@pids", pids),
   new sqlparameter("@cellp",cellp),
   new sqlparameter("@cphone", cphone),
   new sqlparameter("@amounts",amounts),
   new sqlparameter("@cartnumber",cartnumber),
   new sqlparameter("@return",sqldbtype.int)
};
param[8].direction = parameterdirection.returnvalue;
mscl.sqlhelper.runprocedure("addordertran", param);
object obj = param[8].value; //接受返回值
//string connstr = system.configuration.configurationmanager.appsettings["constr"].tostring();
//using (sqlconnection conn = new sqlconnection(connstr))
//{
//  conn.open();
//  sqlcommand cmd = new sqlcommand("addordertran", conn);
//  cmd.commandtype = commandtype.storedprocedure;
//  sqlparameter para1 = new sqlparameter("@country", country);
//  para1.direction = parameterdirection.input; //参数方向 为输入参数
//  cmd.parameters.add(para1);
//  sqlparameter para2 = new sqlparameter("@adds", adds);
//  para2.direction = parameterdirection.input;
//  cmd.parameters.add(para2);
//  sqlparameter para3 = new sqlparameter("@ynames", ynames);
//  para3.direction = parameterdirection.input;
//  cmd.parameters.add(para3);
//  sqlparameter para4 = new sqlparameter("@pids", pids);
//  para4.direction = parameterdirection.input;
//  cmd.parameters.add(para4);
//  sqlparameter para5 = new sqlparameter("@cellp", cellp);
//  para5.direction = parameterdirection.input;
//  cmd.parameters.add(para5);
//  sqlparameter para6 = new sqlparameter("@cphone", cphone);
//  para6.direction = parameterdirection.input;
//  cmd.parameters.add(para6);
//  sqlparameter para7 = new sqlparameter("@amounts", amounts);
//  para7.direction = parameterdirection.input;
//  cmd.parameters.add(para7);
//  sqlparameter para8 = new sqlparameter("@cartnumber", cartnumber);
//  para8.direction = parameterdirection.input;
//  cmd.parameters.add(para8);
//  sqlparameter parareturn = new sqlparameter("@return", sqldbtype.int);
//  parareturn.direction = parameterdirection.returnvalue; //参数方向 为返回参数
//  cmd.parameters.add(parareturn);
//  cmd.executenonquery();
//  object obj = parareturn;
//  if (obj.tostring() == "0")
//  {
//    //存储过程执行失败
//  }
//  else
//  {
//    //成功
//  }
//}
//#endregion

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
// 设置sql连接
cmd.connection = sqlconn;
// 如果执行语句
cmd.commandtext = "categoriestest1";
// 指定执行语句为存储过程
cmd.commandtype = commandtype.storedprocedure;
sqldataadapter dp = new sqldataadapter(cmd);
dataset ds = new dataset();
// 填充dataset
dp.fill(ds);
// 以下是显示效果
gridview1.datasource = ds;
gridview1.databind();

create procedure categoriestest1
 as
 select *
 from categories
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest2";
cmd.commandtype = commandtype.storedprocedure;
sqlconn.open();
// 执行并显示影响行数
label1.text = cmd.executenonquery().tostring();
sqlconn.close();

create procedure categoriestest2 as
 insert into dbo.categories
 (categoryname,[description],[picture])
 values ('test1','test1',null)
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest3";
cmd.commandtype = commandtype.storedprocedure;
// 创建参数
idataparameter[] parameters = {
     new sqlparameter("rval", sqldbtype.int,4)
   };
// 将参数类型设置为 返回值类型
parameters[0].direction = parameterdirection.returnvalue;
// 添加参数
cmd.parameters.add(parameters[0]);
sqlconn.open();
// 执行存储过程并返回影响的行数
label1.text = cmd.executenonquery().tostring();
sqlconn.close();
// 显示影响的行数和返回值
label1.text += "-" + parameters[0].value.tostring() ;

create procedure categoriestest3
 as
 insert into dbo.categories
 (categoryname,[description],[picture])
 values ('test1','test1',null)
return @@rowcount
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest4";
cmd.commandtype = commandtype.storedprocedure;
// 创建参数
idataparameter[] parameters = {
     new sqlparameter("@id", sqldbtype.int,4) ,
     new sqlparameter("@categoryname", sqldbtype.nvarchar,15) ,
   };
// 设置参数类型
parameters[0].direction = parameterdirection.output; // 设置为输出参数
parameters[1].value = "testcategoryname";
// 添加参数
cmd.parameters.add(parameters[0]);
cmd.parameters.add(parameters[1]);
sqlconn.open();
// 执行存储过程并返回影响的行数
label1.text = cmd.executenonquery().tostring();
sqlconn.close();
// 显示影响的行数和输出参数
label1.text += "-" + parameters[0].value.tostring() ;

create procedure categoriestest4
 @id int output,
 @categoryname nvarchar(15)
 as
 insert into dbo.categories
 (categoryname,[description],[picture])
 values (@categoryname,'test1',null)
set @id = @@identity
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest5";
cmd.commandtype = commandtype.storedprocedure;
// 创建参数
idataparameter[] parameters = {
     new sqlparameter("@id", sqldbtype.int,4) ,
     new sqlparameter("@categoryname", sqldbtype.nvarchar,15) ,
     new sqlparameter("rval", sqldbtype.int,4)
   };
// 设置参数类型
parameters[0].direction = parameterdirection.output;    // 设置为输出参数
parameters[1].value = "testcategoryname";         // 给输入参数赋值
parameters[2].direction = parameterdirection.returnvalue; // 设置为返回值
// 添加参数
cmd.parameters.add(parameters[0]);
cmd.parameters.add(parameters[1]);
cmd.parameters.add(parameters[2]);
sqlconn.open();
// 执行存储过程并返回影响的行数
label1.text = cmd.executenonquery().tostring();
sqlconn.close();
// 显示影响的行数，输出参数和返回值
label1.text += "-" + parameters[0].value.tostring() + "-" + parameters[2].value.tostring();

create procedure categoriestest5
 @id int output,
 @categoryname nvarchar(15)
 as
 insert into dbo.categories
 (categoryname,[description],[picture])
 values (@categoryname,'test1',null)
set @id = @@identity
return @@rowcount
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest6";
cmd.commandtype = commandtype.storedprocedure;
// 创建参数
idataparameter[] parameters = {
     new sqlparameter("@id", sqldbtype.int,4) ,
     new sqlparameter("@categoryname", sqldbtype.nvarchar,15) ,
     new sqlparameter("rval", sqldbtype.int,4)          // 返回值
  };
// 设置参数类型
parameters[0].direction = parameterdirection.output;    // 设置为输出参数
parameters[1].value = "testcategoryname";          // 给输入参数赋值
parameters[2].direction = parameterdirection.returnvalue;  // 设置为返回值
// 添加参数
cmd.parameters.add(parameters[0]);
cmd.parameters.add(parameters[1]);
cmd.parameters.add(parameters[2]);
sqldataadapter dp = new sqldataadapter(cmd);
dataset ds = new dataset();
// 填充dataset
dp.fill(ds);
// 显示结果集
gridview1.datasource = ds.tables[0];
gridview1.databind();
label1.text = "";
// 显示输出参数和返回值
label1.text += parameters[0].value.tostring() + "-" + parameters[2].value.tostring();

create procedure categoriestest6
 @id int output,
 @categoryname nvarchar(15)
 as
 insert into dbo.categories
 (categoryname,[description],[picture])
 values (@categoryname,'test1',null)
set @id = @@identity
 select * from categories
return @@rowcount
 go

sqlconnection sqlconn = new sqlconnection(conn);
sqlcommand cmd = new sqlcommand();
cmd.connection = sqlconn;
cmd.commandtext = "categoriestest7";
cmd.commandtype = commandtype.storedprocedure;
sqldataadapter dp = new sqldataadapter(cmd);
dataset ds = new dataset();
// 填充dataset
dp.fill(ds);
// 显示结果集1
gridview1.datasource = ds.tables[0];
gridview1.databind();
// 显示结果集2
gridview2.datasource = ds.tables[1];
gridview2.databind();

create procedure categoriestest7
 as
 select * from categories
 select * from categories
 go