数据集为空错误 [英] Dataset is empty error

问题描述

我有以下代码，用于从我的数据库中的存储过程中获取数据并将其导出到数据表中。但我发现数据集是空的。它不提取任何数据。因此，假设从数据集获取数据的数据表也是空的。以下是我的代码：

Hi, i have the following codes which is to get data from stored procedure in my database and export it into a datatable. But i have found that the dataset is empty. It fetch no data. Therefore the datatable which is suppose to get the data from dataset is also empty. Below are my codes:

var GeminiConnString = ConfigurationManager.ConnectionStrings["GeminiConnString"].ConnectionString;
        SqlConnection conn = null;
        SqlCommand command = null;
        DataTable dt = new DataTable();

        using (conn = new SqlConnection(GeminiConnString)) 
        {
            using (command = new SqlCommand("dbo.GetGeminiRecordByDate", conn)) 
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@startDate", StartDate));
                command.Parameters.Add(new SqlParameter("@endDate", EndDate));

                conn.Open();
                command.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];

                if (ds.Tables[0].Rows.Count == 0) 
                {
                    Console.WriteLine("Empty");
                }
                foreach (DataRow dRow in dt.Rows) 
                {
                    Console.WriteLine(type);
                    Console.Write(dRow["issueid"].ToString());
                }
                conn.Close();
            } 
        }

以下是我的存储过程代码：

Below are my stored procedure codes:

ALTER PROCEDURE [dbo].[GetGeminiRecordByDate]
	-- Add the parameters for the stored procedure here
	@startDate VARCHAR(MAX), 
	@endDate VARCHAR(MAX)
	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @param NVARCHAR (MAX)
	
	SELECT @param = '@startDate VARCHAR(MAX), 
					 @endDate VARCHAR(MAX)'
					 
	SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE CONVERT(varchar(10), created, 120) BETWEEN ('+@startDate+') AND ('+@endDate+')'
	
	EXEC sp_executesql @SQL, @param, @startDate, @endDate
END

FIY，@ SQL和@param与上述相同，而@startDate为'2014-03-01'，@ endDate为'2014-08-01 '

FIY, the @SQL and @param is same as above while @startDate is '2014-03-01' and @endDate is '2014-08-01'

推荐答案

command.Parameters.AddWithValue("@startDate", StartDate); //StartDate should be a DateTime object
command.Parameters.AddWithValue("@endDate", EndDate);//EndDate should be a DateTime object

更改你的SP如下

change your SP as below

ALTER PROCEDURE [dbo].[GetGeminiRecordByDate]
	-- Add the parameters for the stored procedure here
	@startDate DATETIME, 
	@endDate DATETIME
	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @param NVARCHAR (MAX)
	
	SELECT @param = '@startDate DATETIME, 
					 @endDate DATETIME'
					 
	SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE created BETWEEN @startDate AND @endDate'
	
	EXEC sp_executesql @SQL, @param, @startDate, @endDate
END

如果你需要避免时间成为日期时间的一部分，请按以下方式更改SQL

if you need to avoid time part of the datetime, change the SQL as below

SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE CONVERT (DATE,created) BETWEEN CONVERT (DATE,@startDate) AND CONVERT (DATE,@endDate)'

阅读如何仅从Sql Server中的DateTime获取日期部分 [ ^ ]

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