存储过程将Empty表返回给DATAREADER C# [英] Stored Procedure returns Empty table to DATAREADER C#

问题描述

我使用了StoredProcedure从表中返回一些记录.因此，我尝试将其保存在临时表中，并将这些记录从临时表返回到我的应用程序端.

我如上所述尝试过，但我只有SP的临时表生成的具有某些字段(列)的EMPTY datareader.

这是我的SP，

I have used an StoredProcedure to return some records from an table. Hence i tried to saved it in an temp table and return those records from temp table to my application side.

I have tried as above but i got only EMPTY datareader with certain fields(columns) i have generated for temp table in SP.

Here is my SP,

ALTER procedure [dbo].[SP_SELECT_BILLDELETE]
As
Begin
SET NOCOUNT ON
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#MyTempTable')
)
BEGIN
DROP TABLE #MyTempTable
END
--Here i have Created an TempTable
select * into #MyTempTable from BillDelete where CONVERT(date,billdate,101) = CONVERT(date,GETDATE(),101) union
select * from WBillDelete  where CONVERT(date,billdate,101) = CONVERT(date,GETDATE(),101)

return
End

这是我的C#代码，用于从SP中检索记录

Here is my C# code to retrieve records from SP

sqlcon.Open();
               sqlcom = new SqlCommand("SP_SELECT_BILLDELETE", sqlcon);
               sqlcom.CommandType = CommandType.StoredProcedure;

               sqldr = sqlcom.ExecuteReader();
               if(sqldr.Read()) //Here it passed this condition and get into it
               {
                   dtable = new DataTable();

                   dtable.Load(sqldr); //Here i'm getting structure alone with no records
               }

               return dtable;

推荐答案

将过程更改为此..

change the procedure to this..

ALTER procedure [dbo].[SP_SELECT_BILLDELETE]
As
Begin
SET NOCOUNT ON
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#MyTempTable')
)
BEGIN
DROP TABLE #MyTempTable
END
--Here i have Created an TempTable
select * into #MyTempTable from BillDelete where CONVERT(date,billdate,101) = CONVERT(date,GETDATE(),101) 
union
select * from WBillDelete  where CONVERT(date,billdate,101) = CONVERT(date,GETDATE(),101)

select * from #MyTempTable 

End

在end之前的底部添加select * from #MyTempTable .

希望它能起作用.

add select * from #MyTempTable at the bottom before end.

hope it works..

您可以像这样从sqldatareader中获取datatable.

you can get datatable from sqldatareader like this..

SqlDataReader dr = cmd.ExecuteReader();
 DataTable dt = new DataTable();
 dt.Load(dr);

尝试这个..

try this..

我代替datareader在应用程序端使用像下面这样的dataAdapter ...

Instead of datereader i used dataAdapter like below in application side...

SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);

               dtable = new DataTable();
               sqlda.Fill(dtable);

这篇关于存储过程将Empty表返回给DATAREADER C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！