c#获取存储过程的 return返回值和output输出参数值 -凯发ag旗舰厅登录网址下载

一、不用sqlhelper.cs等帮助类

1.获取return返回值
 程序代码
 存储过程
create procedure mysql
  @a int,
  @b int
as
  return @a @b
go
sqlconnection conn = new sqlconnection(configurationmanager.connectionstrings["localsqlserver"].tostring());
conn.open();
sqlcommand mycommand = new sqlcommand("mysql", conn);
mycommand.commandtype = commandtype.storedprocedure;
mycommand.parameters.add(new sqlparameter("@a", sqldbtype.int));
mycommand.parameters["@a"].value = 10;
mycommand.parameters.add(new sqlparameter("@b", sqldbtype.int));
mycommand.parameters["@b"].value = 20;
mycommand.parameters.add(new sqlparameter("@return", sqldbtype.int));
mycommand.parameters["@return"].direction = parameterdirection.returnvalue;
mycommand.executenonquery();
response.write(mycommand.parameters["@return"].value.tostring());

2.获取output输出参数值
 程序代码
 存储过程
create procedure mysql
   @a int,
   @b int,
   @c int output
as
   set @c = @a @b
go
sqlconnection conn = new sqlconnection(configurationmanager.connectionstrings["localsqlserver"].tostring());
conn.open();
sqlcommand mycommand = new sqlcommand("mysql", conn);
mycommand.commandtype = commandtype.storedprocedure;
mycommand.parameters.add(new sqlparameter("@a", sqldbtype.int));
mycommand.parameters["@a"].value = 20;
mycommand.parameters.add(new sqlparameter("@b", sqldbtype.int));
mycommand.parameters["@b"].value = 20;
mycommand.parameters.add(new sqlparameter("@c", sqldbtype.int));
mycommand.parameters["@c"].direction = parameterdirection.output;
mycommand.executenonquery();
response.write(mycommand.parameters["@c"].value.tostring());
 
---------------------------------------------------------------------------------------------------------------
以下代码转自网络：
c#接收存储过程返回值:
     public static int user_add(user us)
     {
         int iret;
         sqlconnection conn = new sqlconnection(conn_str);
         sqlcommand cmd = new sqlcommand("user_add", conn);
         cmd.commandtype = commandtype.storedprocedure;
         cmd.parameters.addwithvalue("@uname", us.uname);
         cmd.parameters.addwithvalue("@upass", us.upass);
         cmd.parameters.addwithvalue("@passquestion", us.passquestion);
         cmd.parameters.addwithvalue("@passkey", us.passkey);
         cmd.parameters.addwithvalue("@email", us.email);
         cmd.parameters.addwithvalue("@rname", us.rname);
         cmd.parameters.addwithvalue("@area", us.area);
         cmd.parameters.addwithvalue("@address", us.address);
         cmd.parameters.addwithvalue("@zipcodes", us.zipcodes);
         cmd.parameters.addwithvalue("@phone", us.phone);
         cmd.parameters.addwithvalue("@qq", us.qq);
         cmd.parameters.add("@return_value", "").direction = parameterdirection.returnvalue;      
         try
         {
             conn.open();
             cmd.executenonquery();
             iret = (int)cmd.parameters["@return_value"].value;
         }
         catch (sqlexception ex)
         {
             throw ex;
         }
         finally
         {
             conn.close();
         }
         return iret;
     }
c#接收存储过程输出参数:
    public static decimal cart_useramount(int uid)
    {
        decimal iret;
        sqlconnection conn = new sqlconnection(conn_str);
        sqlcommand cmd = new sqlcommand("cart_useramount", conn);
        cmd.commandtype = commandtype.storedprocedure;
        cmd.parameters.addwithvalue("@uid", uid);
        cmd.parameters.add("@amount", sqldbtype.decimal).direction=parameterdirection.output;
        try
        {
            conn.open();
            cmd.executenonquery();
            iret = (decimal)cmd.parameters["@amount"].value;
        }
        catch (sqlexception ex)
        {
            throw ex;
        }

二、用sqlhelper.cs等帮助类，其思想一样

如：（贴出部分代码）

string readercode = txt_reader_code.text;
string bookcode = txt_book_code.text;
decimal money = convert.todecimal(txt_price.text);
datetime borrowdate = convert.todatetime(datetime.now.toshortdatestring());
 datatable table = new dbutility.booktype().gettablebybookbarcode(bookcode);
double borrowday;
double getday = 0d;
if (table != null && table.rows.count > 0)
{
    if (double.tryparse(table.rows[0]["borrowday"].tostring(), out borrowday))
    {
         getday = borrowday;
    }
}
else
{
     pub.util.alertpostback(page, "得到图书借阅天数失败");
     return;
}
datetime returndate = convert.todatetime(datetime.now.adddays(getday).toshortdatestring());
string readername = txt_name.text.trim();
string bookname = txt_book_name.text.trim();

sqlparameter[] parameters = {
           new sqlparameter("@readerbarcode",sqldbtype.varchar),
           new sqlparameter("@bookbarcode",sqldbtype.varchar),
           new sqlparameter("@hire",sqldbtype.money),
           new sqlparameter("@borrowdate",sqldbtype.datetime),
           new sqlparameter("@returndate",sqldbtype.datetime),
           new sqlparameter("@readername",sqldbtype.varchar),
           new sqlparameter("@bookname",sqldbtype.varchar),
           new sqlparameter("@return",sqldbtype.int)           //添加一个返回参数
       };
parameters[0].value = readercode;
parameters[1].value = bookcode;
parameters[2].value = money;
parameters[3].value = borrowdate;
parameters[4].value = returndate;
parameters[5].value = readername;
parameters[6].value = bookname;
parameters[7].direction = parameterdirection.returnvalue; //声明此参数是返回类型

dbutility.sqlhelper.executenonquery(dbutility.sqlhelper.bookconn, commandtype.storedprocedure, "p_bookborrow", parameters);

int num = convert.toint32(parameters[7].value.tostring());//提取存储过程返回参数的值,成功为0，不成功为-1

if (num == 0)
{     pub.util.alertpostback(page, "借阅成功");
}
else
   pub.util.alertpostback(page, "借阅失败");

 

 

 

存储过程

 

create procedure p_bookborrow(@readerbarcode varchar(30),@bookbarcode varchar(30),@hire money,@borrowdate datetime,@returndate datetime,@bookname varchar(30),@readername varchar(20))
as
begin tran
insert into t_reader_book values(@readerbarcode,@bookbarcode,@borrowdate,@returndate,@bookname,@readername)
if(@@error<>0)
begin
  rollback tran
  return -1
end
update t_readerinfo set borrownum=borrownum 1,[money]=[money]-@hire where readerbarcode = @readerbarcode
if(@@error<>0)
begin
  rollback tran
  return -1
end
update t_bookinfo set stock=stock-1 where bookbarcode =@bookbarcode
if(@@error<>0)
begin
  rollback tran
  return -1
end
insert into logs values(getdate(),'条形码号为：' @readerbarcode '的读者，借阅了图书编号为：' @bookbarcode '的书籍')
if(@@error<>0)
begin
  rollback tran
  return -1
end
commit tran
if(@@error<>0)
  return -1
else
  return 0

go

 

转载于:https://www.cnblogs.com/xsq521/p/5509531.html

总结

以上是凯发ag旗舰厅登录网址下载为你收集整理的c#获取存储过程的 return返回值和output输出参数值的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得凯发ag旗舰厅登录网址下载网站内容还不错，欢迎将凯发ag旗舰厅登录网址下载推荐给好友。