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

本文实例讲述了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 执行存储过程