Empty Dataset from Stored Procedure

See more:

.NET3.5

ASP.NET

.NET

C#5.0

, +

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.