存储过程中的空数据集 [英] Empty Dataset from Stored Procedure

问题描述

我在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.

推荐答案

我的猜测是有一个异常，但是它会在空的catch块中被吞噬。然后，您的代码很乐意继续返回在方法开头创建的数据集，当然是空的。你永远不应该忽略这样的例外。始终只捕获您可以处理的异常！现在只需删除try-catch，看看你得到了什么错误。

My guess is there is an exception but it gets swallowed in the empty catch block. Your code then happily goes on to return the dataset which was created at the beginning of the method and is of course empty. You should never ignore exceptions like that. Always catch only exceptions you can handle! For now just remove the try-catch altogether and see what error you are getting.

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