存储过程中的空数据集

问题描述

我在SQL Server数据库中创建了一个存储过程，该过程只返回4列的1条记录，所有四列都是整数数据类型，其中一列是Identity（1,1）列



我的sql代码如下

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

我测试了Sql代码它工作正常并给出正确的输出。

并且已经写了ac＃使用SqlParameters和SqlDataAdapter获取存储过程返回的数据的代码。

这就是问题所在。它只返回并清空DataSet。



这是来自DataStore.cs类文件的My Code，这是编写通用DataAccess代码的地方

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;
}

这是调用上述方法的DataAccessLayer代码

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);
}

上述方法在Asp网页级调用。

任何帮助都表示赞赏。

This method above is called at the Asp Web Page Level.
Any help is appreciated.

推荐答案

这篇关于存储过程中的空数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！