从存储过程返回值到C# [英] Return value from stored procedure to c#
本文介绍了从存储过程返回值到C#的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我多了一个存储过程中得到了同样的问题。
我想离开的请求添加到SQL Server 2008数据库。我可以成功运行一个存储过程,但是从页面我不能返回状态操作是完全或不完全。
我是通过SP发送数据,但无法通过程序来获取状态。
我的存储过程是:
ALTER步骤[DBO]。[Check_LeaveDays](
@的Emp code的int,
@LV_Type INT,
@Leave_AppDt日期时间,
@Leave_ToDate日期时间,
@LV_Days INT
@ STATUS_ID INT输出
)如
开始 声明@Dt_join日期时间,@ LastDate日期时间
声明@count INT
声明@Leave_Status_Id INT
声明@Leave_period INT
声明@Rule_id INT
声明@Leave_AllocatedDays INT
声明@Leave_MaxDays INT
声明@Days_diff INT
- 声明@Status_Id INT
- 设置@ STATUS_ID = 0
选择@Dt_Join =从LTS_Employee_Master Emp_DOJ那里EMP_ID = 4 选择@ LastDate = DATEADD(年,DATEDIFF(年,-1,GETDATE()),-1)
选择@ Days_diff = 0 如果(YEAR(@Dt_Join)= YEAR(GETDATE()))
开始
选择@Days_diff = DATEDIFF(D,@ Dt_Join,@ LastDate)
结束
- 选择@Leave_AppDt = DATEADD(男,-2,GETDATE())
选择@Rule_id =情况下,当@LV_Type = 1那么ISNULL(Emp_Casual_rule,0)
当@LV_Type = 2,那么ISNULL(Emp_Medical_rule,0)
当@LV_Type = 3,那么ISNULL(Emp_PL_rule,0)
否则为0结束
从LTS_Employee_Master那里EMP_ID = @的Emp code 如果@LV_Type = 1
开始
选择@Leave_AllocatedDays = LPM_Allocated_Days,@ Leave_MaxDays = LPM_Max_Days,@ Leave_period = LPM_Count
从LTS_Leave_Policy_Master那里LPM_Id = @ RULE_ID
如果@Days_diff<> 0
开始
选择@Leave_AllocatedDays = 365 / @ Leave_AllocatedDays
选择@Leave_AllocatedDays = @Days_diff / @Leave_AllocatedDays
结束
选择@count = SUM(Leave_Days)
从LTS_Emp_Leave_Requests那里Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 1和YEAR(@Leave_ToDate)= YEAR(leave_to_Date) 选择@count = ISNULL(@计数,0)+ ISNULL(总和(Leave_Days),0)
从LTS_Emp_Leave_Requests那里Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 3和YEAR(@Leave_ToDate)= YEAR(leave_to_Date)
和REQ_ID不是(从LTS_Emp_Leave_Requests选择REQ_ID,其中Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 3和YEAR(@Leave_ToDate)= YEAR(leave_to_Date)) 选择@count = ISNULL(@计数,0)+ ISNULL(总和(Leave_Days),0)
从LTS_Emp_Leave_Requests那里Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 3和YEAR(@Leave_ToDate)= YEAR(leave_to_Date)
和REQ_ID不是(从LTS_Emp_Leave_Requests选择REQ_ID,其中Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 3和YEAR(@Leave_ToDate)= YEAR(leave_to_Date)) 选择@算,@ Leave_MaxDays 如果(@LV_Days> @Leave_MaxDays)
开始 设置@ STATUS_ID = 1 - 状态appliation休假天数比最高津贴多天在一个时间 结束
如果(@count> @Leave_AllocatedDays)
开始
选择@Status_Id = 2 --status 2适用于施加最大的天数超过天的实际分配的最大数量 结束 选择@count = SUM(Leave_Days)
从
(选择顶部1 *从LTS_Emp_Leave_Requests为了通过Leave_ID DESC)温度
其中,Leave_Emp_ID = @ EMP code和Leave_type_Id = 1和Leave_Status_ID = 1组由Leave_Emp_Id 声明@tbl表(Leave_Id INT,INT Leave_Status_Id,Leave_To_date日期时间,leave_days INT,
Leave_Emp_Id INT,INT Leave_off_Id,REQ_ID INT,INT leave_LPM_ID,leave_type_Id INT)
INSERT INTO @tbl
选择前1
Leave_Id,Leave_Status_Id,Leave_To_date,
Leave_days,Leave_Emp_Id,Leave_off_Id,REQ_ID,leave_LPM_ID,Leave_type_Id
从LTS_Emp_Leave_Requests那里Leave_Emp_ID = @ EMP code和Leave_Status_ID在(1,3,5)由Leave_ID倒序 从@tbl选择@Leave_ToDate = Leave_To_date
如果(DATEDIFF(D,@ Leave_ToDate,DATEADD(D,-1,@Leave_AppDt))> @Leave_AllocatedDays)
开始
选择@Status_Id = 3
结束
结束
返回@Status_Id
结束
我的存储过程调用功能:
公共字符串SendRequestDataSql(INT EMPID,诠释leavetype,日期时间寄件者,日期TODATE,INT leavedays)
{
串retunvalue =0;
DataTable的DT =新的DataTable();
尝试
{
的SqlConnection的SqlConnection =新的SqlConnection();
字符串conString = Connection.GetConnection; 使用(SqlConnection的CON =新的SqlConnection(conString))
{
//sqlConnection.Open();
使用(CMD的SqlCommand =新的SqlCommand(Check_LeaveDaysCON))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@的Emp code,SqlDbType.VarChar,10).value的= EMPID;
cmd.Parameters.Add(@ LV_Type,SqlDbType.VarChar,10).value的= leavetype;
cmd.Parameters.Add(@ Leave_AppDt,SqlDbType.VarChar,15).value的=寄件者;
cmd.Parameters.Add(@ Leave_ToDate,SqlDbType.VarChar,15).value的= TODATE;
cmd.Parameters.Add(@ LV_Days,SqlDbType.VarChar,10).value的= leavedays; 的SqlParameter returnParameter = cmd.Parameters.Add(RETVAL,SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue; con.Open();
INT itrrr = Convert.ToInt32(cmd.ExecuteNonQuery()); INT的returnValue =(INT)returnParameter.Value;
//消息= Convert.ToInt32(objparm.Value); con.Close();
使用(SqlDataAdapter的大=新SqlDataAdapter的(CMD))
{
DataSet的DS =新的DataSet();
da.Fill(DS);
dt的= ds.Tables [0];
}
}
}
}
赶上(SQLEXCEPTION EX)
{ }
返回retunvalue;
}
解决方案
请编辑您的code为:
删除此行:
cmd.Parameters.Add(@状态,SqlDbType.Int).value的=状态;
将此code:
的SqlParameter ABC = cmd.Parameters.Add(@状态,SqlDbType.Int);
abc.Direction = ParameterDirection.Output;
然后,你可以在你的ABC状态的结果。
希望这会帮助你。
I got same problem in one more stored procedure. I want to add leave request to a SQL Server 2008 database. I can run a stored procedure successfully but from page I can't return status whether operation is complete or incomplete.
I am sending data through SP but not able to get status through program.
My stored procedure is:
ALTER Procedure [dbo].[Check_LeaveDays](
@EmpCode int,
@LV_Type int,
@Leave_AppDt DateTime,
@Leave_ToDate Datetime ,
@LV_Days int
,@Status_Id int Output
)
as
Begin
Declare @Dt_join datetime ,@LastDate Datetime
Declare @Count int
Declare @Leave_Status_Id int
Declare @Leave_period int
Declare @Rule_id int
Declare @Leave_AllocatedDays int
Declare @Leave_MaxDays int
Declare @Days_diff int
-- Declare @Status_Id int
-- Set @Status_Id= 0
Select @Dt_Join =Emp_DOJ from LTS_Employee_Master where Emp_ID =4
Select @LastDate= DATEADD(year, DATEDIFF(year, -1, getdate()), -1)
Select @Days_diff=0
If(YEAR(@Dt_Join) = YEAR(GETDATE()))
Begin
Select @Days_diff = DATEDIFF(D, @Dt_Join,@LastDate)
End
--Select @Leave_AppDt = dateadd(M, -2, getdate())
Select @Rule_id = Case when @LV_Type =1 then ISNULL(Emp_Casual_rule,0)
when @LV_Type =2 then ISNULL(Emp_Medical_rule,0)
when @LV_Type =3 then ISNULL(Emp_PL_rule,0)
else 0 End
from LTS_Employee_Master where Emp_ID =@Empcode
If @LV_Type =1
Begin
Select @Leave_AllocatedDays = LPM_Allocated_Days ,@Leave_MaxDays =LPM_Max_Days ,@Leave_period =LPM_Count
from LTS_Leave_Policy_Master where LPM_Id =@Rule_Id
If @Days_diff <> 0
Begin
Select @Leave_AllocatedDays = 365/@Leave_AllocatedDays
Select @Leave_AllocatedDays = @Days_diff / @Leave_AllocatedDays
End
Select @Count =Sum(Leave_Days)
from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=1 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date)
Select @Count = ISNULL(@Count,0) + ISNULL(Sum(Leave_Days),0)
from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date)
and Req_id not in(Select Req_id from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date))
Select @Count = ISNULL(@Count,0) + ISNULL(Sum(Leave_Days),0)
from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date)
and Req_id not in(Select Req_id from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date))
Select @count,@Leave_MaxDays
if(@LV_Days > @Leave_MaxDays)
Begin
Set @Status_Id=1 -- Status appliation leave days is more than allowance max days at a time
End
If(@Count > @Leave_AllocatedDays)
Begin
Select @Status_Id =2 --Status 2 applies for numbers of maximum days applied is more than actual allocated maximum number of days
End
Select @Count =Sum(Leave_Days)
from
(Select top 1 * from LTS_Emp_Leave_Requests order by Leave_ID desc) Temp
where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID =1 group by Leave_Emp_Id
Declare @tbl table(Leave_Id int , Leave_Status_Id int , Leave_To_date datetime,leave_days int,
Leave_Emp_Id int,Leave_off_Id int,Req_Id int,leave_LPM_ID int, leave_type_Id int)
Insert into @tbl
Select top 1
Leave_Id , Leave_Status_Id , Leave_To_date ,
Leave_days ,Leave_Emp_Id ,Leave_off_Id ,Req_Id ,leave_LPM_ID , Leave_type_Id
from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_Status_ID in(1,3,5) order by Leave_ID desc
Select @Leave_ToDate =Leave_To_date from @tbl
If( DATEDIFF(D,@Leave_ToDate, DATEADD(D,-1, @Leave_AppDt)) > @Leave_AllocatedDays)
Begin
Select @Status_Id =3
End
End
Return @Status_Id
End
My stored procedure calling functions:
public string SendRequestDataSql(int empid, int leavetype, DateTime fromdate, DateTime todate, int leavedays)
{
string retunvalue="0";
DataTable dt = new DataTable();
try
{
SqlConnection sqlConnection = new SqlConnection();
string conString = Connection.GetConnection;
using (SqlConnection con = new SqlConnection(conString))
{
//sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("Check_LeaveDays", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmpCode", SqlDbType.VarChar, 10).Value = empid;
cmd.Parameters.Add("@LV_Type", SqlDbType.VarChar, 10).Value = leavetype;
cmd.Parameters.Add("@Leave_AppDt", SqlDbType.VarChar,15).Value = fromdate;
cmd.Parameters.Add("@Leave_ToDate", SqlDbType.VarChar,15).Value = todate;
cmd.Parameters.Add("@LV_Days", SqlDbType.VarChar,10).Value = leavedays;
SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
con.Open();
int itrrr = Convert.ToInt32( cmd.ExecuteNonQuery());
int returnValue = (int)returnParameter.Value;
//message = Convert.ToInt32(objparm.Value);
con.Close();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
}
}
}
}
catch (SqlException ex)
{
}
return retunvalue;
}
解决方案
Please Edit Your Code as:
Remove this line:
cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;
Add this Code:
SqlParameter abc = cmd.Parameters.Add("@Status", SqlDbType.Int);
abc.Direction = ParameterDirection.Output;
Then you can get your Status result in abc.
Hope this will help you.
这篇关于从存储过程返回值到C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文