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; } finally {
conn.Close(); }
return iRet; }
另一种方法
public int generateWEReport(int toDepartNum) {
SqlCommand myComm = new SqlCommand(\"Proc_GenerateWEReport\sqlconnection);
myComm.CommandType = CommandType.StoredProcedure; //添加参数公寓ID
SqlParameter toDepart = new SqlParameter(\"@toDepart\SqlDbType.SmallInt, 2);
toDepart.Value = toDepartNum; myComm.Parameters.Add(toDepart); //添加返回的参数
SqlParameter toReturn = new SqlParameter(\"@toReturn\SqlDbType.Int, 4);
toReturn.Direction = ParameterDirection.Output; myComm.Parameters.Add(toReturn); sqlconnection.Open();
myComm.ExecuteNonQuery(); return (int)toReturn.Value; }
CREATE PROCEDURE Proc_GenerateWEReport (
@toDepart int, @toReturn int out ) as
declare @weYear int declare @weMonth int declare @HasRecord int
set @weYear = Year(GetDate()) set @weMonth = Month(GetDate())
select @HasRecord = count(*) from _tRoomWE, _tRoom where _tRoomWE.toRoom = _tRoom.roomid and _tRoom.toDepart = @toDepart and _tRoomWE.weYear = @weYear and _tRoomWE.weMonth = @weMonth if(@HasRecord = 0) begin
Insert into _tRoomWE(toRoom, weYear, weMonth) select roomId, @weYear, @weMonth from _tRoom where toDepart = @toDepart set @toReturn = 1 end else begin
set @toReturn = 0 end GO
