I have created a Stored procedure in SQL Server database which returns just 1 record of 4 columns all four columns are of integer data type and one of them is Identity(1,1) column
My sql code is as below
CREATE PROCEDURE [dbo].[GetDecrementValue]
@Ref_AccPeriod_ID INT,
@Ref_LeaveType_ID INT,
@Ref_LeaveTime_ID INT,
@ResponseText VARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM Decrement WHERE Ref_AccPeriod_ID = @Ref_AccPeriod_ID AND Ref_LeaveType_ID = @Ref_LeaveType_ID AND Ref_LeaveTime_ID = @Ref_LeaveTime_ID AND IsActive = 1)
BEGIN
SELECT
DecrementType,
DecrementValue,
IsActive,
Ref_Decrement_ID
FROM Decrement
WHERE Ref_AccPeriod_ID = @Ref_AccPeriod_ID
AND Ref_LeaveType_ID = @Ref_LeaveType_ID
AND Ref_LeaveTime_ID = @Ref_LeaveTime_ID
AND IsActive = 1
END
ELSE
BEGIN
SET @ResponseText = 'Leave Decrement Value not found.'
END
END
I have tested the Sql code it work fine and give correct output.
And have written a c# code using SqlParameters and SqlDataAdapter to fetch the data returned by the Stored Procedure.
This is where the problem is. It just returns and Empty DataSet.
This is My Code from the DataStore.cs Class file which is the place where the common DataAccess Code is written
public DataSet GetDataDSWithParameters(string ProcedureName, SqlParameter[] param)
{
DataSet getDataSet = new DataSet();
try
{
LTConn = new SqlConnection();
LTConn.ConnectionString = connection;
LTConn.Open();
LTCmd = new SqlCommand();
LTCmd.Connection = LTConn;
LTCmd.CommandType = CommandType.StoredProcedure;
LTCmd.CommandText = ProcedureName;
getDapt = new SqlDataAdapter();
getDapt.SelectCommand = new SqlCommand(LTCmd.CommandText, LTConn);
getDapt.SelectCommand.CommandType = CommandType.StoredProcedure;
getDapt.SelectCommand.Parameters.AddRange(param);
getDapt.Fill(getDataSet);
LTConn.Close();
}
catch (Exception) { }
return getDataSet;
}
This is the DataAccessLayer code which call the above method
public DataSet UserGetLeaveDecrementValueDA(LeaveDecrementBO ObjLeaveDecrementBO)
{
SqlParameter[] GetParameter = new SqlParameter[4];
GetParameter[0] = new SqlParameter("@Ref_AccPeriod_ID", SqlDbType.Int);
GetParameter[0].Value = ObjLeaveDecrementBO.Ref_AccountingPeriod_ID;
GetParameter[0].Direction = ParameterDirection.Input;
GetParameter[1] = new SqlParameter("@Ref_LeaveType_ID", SqlDbType.Int);
GetParameter[1].Value = ObjLeaveDecrementBO.Ref_LeaveType_ID;
GetParameter[1].Direction = ParameterDirection.Input;
GetParameter[2] = new SqlParameter("@Ref_LeaveTime_ID", SqlDbType.Int);
GetParameter[2].Value = ObjLeaveDecrementBO.Ref_LeaveTime_ID;
GetParameter[2].Direction = ParameterDirection.Input;
GetParameter[3] = new SqlParameter("@ResponseText", SqlDbType.VarChar, 8000);
GetParameter[3].Direction = ParameterDirection.Output;
return objDataStore.GetDataDSWithParameters("GetDecrementValue", GetParameter);
}
This method above is called at the Asp Web Page Level.
Any help is appreciated.