Click here to Skip to main content
15,042,336 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a stored procedure that checks for Cards that will expire within the next 90, 60, 30, 21, 14 and 7 days. When i execute the SP in Sql Server i am able to get all the rows but when i invoke the same SP from code it only returns first row and does not loop the entire resultset.

This is how im retrieving the results :

SQL
CREATE PROCEDURE [dbo].[GetExpiringCards] 
	-- Add the parameters for the stored procedure here

AS
DECLARE @today datetime2 = cast(GETDATE() as date)
declare @INTERVALS table(intval int)
insert into @INTERVALS (intval) values (90)
insert into @INTERVALS (intval) values (60)
insert into @INTERVALS (intval) values (30)
insert into @INTERVALS (intval) values (21)
insert into @INTERVALS (intval) values (7)
insert into @INTERVALS (intval) values (14)
DECLARE @value int

DECLARE db_cursor CURSOR FOR  
SELECT intval FROM @INTERVALS
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value   

WHILE @@FETCH_STATUS = 0 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

     SELECT @value AS Interval,DATEADD(DD,@value,@today) AS ExpiresOn, CustomerId, IssueBranch from Cards 
	   where ExpiryDate=CONVERT(DATE, DATEADD(DAY, @value, GETDATE()))
       FETCH NEXT FROM db_cursor INTO @value 
END
CLOSE db_cursor   
DEALLOCATE db_cursor
GO


In the code i am invoking as :
C#
using (SqlConnection conn = new SqlConnection(DatabaseConnect2))
           {
               SqlCommand cmd = new SqlCommand("GetExpiringCards", conn);
               cmd.CommandType = CommandType.StoredProcedure;
               conn.Open();
               SqlDataReader dr = cmd.ExecuteReader();
               while (dr.Read())
               {
                   Customer customer = new Customer ();

                   customer.Interval = Convert.ToInt32(dr["Interval"]);
                   customer.ExpiresOn = Convert.ToDateTime(dr["ExpiresOn"]);
                   customer.CustomerId = Convert.ToString(dr["CustomerId"]);
                   customer.IssueBranch = Convert.ToString(dr["IssueBranch"]);
                   customer.Add(upload);

               }
               conn.Close();
           }


What am i missing ?

When i execute the SP in SSMS i get the following :
https://drive.google.com/file/d/1mcuCq5YoLmBOMbrTkYgpZPVcYmodZcNm/view?usp=sharing

What I have tried:

I have tried changing the logic as below but the flow immediately looops out once it fetches the first row :


C#
using (SqlConnection conn = new SqlConnection(DatabaseConnect2))
            {
                SqlCommand cmd = new SqlCommand("GetExpiringCards", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read() && dr.HasRows)
                {
                    Customer customer = new Customer ();

                    customer.Interval = Convert.ToInt32(dr["Interval"]);
                    customer.ExpiresOn = Convert.ToDateTime(dr["ExpiresOn"]);
                    customer.CustomerId = Convert.ToString(dr["CustomerId"]);
                    customer.IssueBranch = Convert.ToString(dr["IssueBranch"]);                   
                    customer.Add(upload);
                   
                }
                conn.Close();
Posted
Updated 30-Jan-21 0:18am

You create your Customer items inside the loop, but ... you do nothing with them other than fill them out - and then they all go out of scope and are discarded.

Your SP can return as many items as it wants - unless you keep a reference to the items you create and fill from that data, you will have no access to any of them once the loop is complete.

Use the debugger: put a breakpoint on this line:
Customer customer = new Customer ();
and watch what happens as yoru code processes the values returned in the DataReader ... you'll see what I mean.
   
You can change your cursor based approach (get one at a time and do a loop) to a set based approach where you get the desired result in one select. See query below. Use of cursors is often seen as 'not optimal' and 'avoid if you can'


SQL
CREATE TABLE [dbo].[Cards](
	[Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
	[ExpiryDate] [date] NOT NULL,
	[IssueBranch] varchar(100) NOT NULL
 CONSTRAINT [PK__Cards] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)) ON [PRIMARY]
GO

INSERT INTO [dbo].[Cards]
           ([CustomerId]
           ,[ExpiryDate]
           ,[IssueBranch])
     VALUES
           (1, DATEADD(day,  7, GETDATE()), 'b1'),
		   (2, DATEADD(day,  7, GETDATE()), 'b2'),
           (3, DATEADD(day,  13, GETDATE()), 'b3'),
		   (4, DATEADD(day,  14, GETDATE()), 'b4'),
           (5, DATEADD(day,  14, GETDATE()), 'b5'),
		   (6, DATEADD(day,  15, GETDATE()), 'b6')	 

select * from cards

-- your query cursor based approach
DECLARE @today datetime2 = cast(GETDATE() as date)
DECLARE @value int
SET @value = 7
SELECT @value AS Interval,DATEADD(DD,@value,@today) AS ExpiresOn, CustomerId, IssueBranch from Cards 
where ExpiryDate=CONVERT(DATE, DATEADD(DAY, @value, GETDATE()))
SET @value = 14
SELECT @value AS Interval,DATEADD(DD,@value,@today) AS ExpiresOn, CustomerId, IssueBranch from Cards 
where ExpiryDate=CONVERT(DATE, DATEADD(DAY, @value, GETDATE()))
-- continue loop with 21,30,60,90



-- set based approach
If(OBJECT_ID('tempdb..#ExpirySet') Is Not Null)
Begin
    Drop Table #ExpirySet
End
CREATE TABLE #ExpirySet (DeltaDays int, ExpiryDate date)

INSERT INTO #ExpirySet
            VALUES
            (7,  DATEADD(DAY, 7, GETDATE()) ),
            (14,  DATEADD(DAY, 14, GETDATE()) ),
            (21,  DATEADD(DAY, 21, GETDATE()) ),
            (30,  DATEADD(DAY, 30, GETDATE()) ),
            (60,  DATEADD(DAY, 60, GETDATE()) ),
            (90,  DATEADD(DAY, 90, GETDATE()) )

--SELECT * FROM #ExpirySet

SELECT te.DeltaDays AS Interval
      ,DATEADD(day, te.DeltaDays, GETDATE()) AS ExpiresON
      ,tc.CustomerId
	  ,tc.IssueBranch
  FROM #ExpirySet te INNER JOIN Cards tc
   ON te.ExpiryDate = tc.ExpiryDate
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900