Stored Procedure returns results in Sql correctly but not in C# using SqlDataReader

Question

SQL Server table:

CREATE TABLE [dbo].[MessageSubscribers] 
(
    [SubscriberId]     INT IDENTITY (1, 1) NOT NULL,
    [SubscriberCode]   NVARCHAR (MAX)  NULL,
    [SubscriberName]   NVARCHAR(MAX) NOT NULL,
    [SubscriberActiveDate] DATETIME NOT NULL,
    [SubscriberExpiryDate] DATETIME NULL,
    [SubscriberEndpoint]   NVARCHAR (MAX) NOT NULL,
    [TopicId]              INT NOT NULL,
    [EventId]              INT NOT NULL,

    CONSTRAINT [PK_MessageSubscribers] PRIMARY KEY ([SubscriberId])
);

Stored procedure:

CREATE PROCEDURE [dbo].[FetchSubscriberDetails_BasedonTopicEvent]
    @eventid int,
    @topicid int,
    @subscribers NVARCHAR(max) = null
AS 
BEGIN
   SET NOCOUNT ON

    /** Fetching Subscriber Details for all Subscribers based on Topic and Event**/
   SELECT DISTINCT  
       s.SubscriberCode, s.SubscriberName,
       s.SubscriberActiveDate, s.SubscriberExpiryDate,
       s.SubscriberEndpoint
   FROM
       ([dbo].[MessageSubscribers] s 
   WHERE 
       s.TopicId = @topicid 
       AND s.EventId = @eventid
       AND CURRENT_TIMESTAMP BETWEEN s.SubscriberActiveDate AND ISNULL(s.SubscriberExpiryDate,CURRENT_TIMESTAMP)
       AND (s.SubscriberCode IS NULL OR s.SubscriberCode = '' 
            OR s.SubscriberCode IN (SELECT [value] FROM STRING_SPLIT(@subscribers, ',')))
END

Sample data:

SubscriberId -- 1
SubscriberCode --   100
Subscriber Name --  Google
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..1
TopicId --  1
EventId --  1

SubscriberId -- 2
SubscriberCode --   200
Subscriber Name --  Microsoft
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..2
TopicId --  1
EventId --  1

SubscriberId -- 3
SubscriberCode -- NULL
Subscriber Name --  Wipro
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..3
TopicId --  1
EventId --  1

C# code:

public async Task<List<Subscriber>> Fetch_SubscribersDetails(int topicid, int eventid, string subscribers)
{
    log.LogInformation($"Fetch_SubscribersDetails: Started -- Retrieving Subscriber details  from database based on topicid: {topicid}, eventid: {eventid} subscribers: {subscribers}.");

    List<Subscriber> subscribersDetails = new List<Subscriber>();
    subscribersDetails.Clear();

    try
    {
        string sqlconnectionString = SharedMethods.GetEnvironmentVariable("SqlConnectionString");

        SqlConnectionStringBuilder sbstr = new SqlConnectionStringBuilder(sqlconnectionString);
        sbstr.Password = SharedMethods.GetEnvironmentVariable("DbSQLPassword");

        using (SqlConnection conn = new SqlConnection(sbstr.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = Constants.FetchSubscribersbasedonTopicEventStorProcName;

            // Setting command timeout to 1 minute
            cmd.CommandTimeout = 60;
            cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add($"@topicid", SqlDbType.Int).Value = topicid;
            //cmd.Parameters.Add($"@eventid", SqlDbType.Int).Value = eventid;
            //cmd.Parameters.Add($"@subscribers", SqlDbType.NVarChar).Value = subscribers.Trim();

            cmd.Parameters.AddWithValue($"@topicid", topicid);
            cmd.Parameters.AddWithValue($"@eventid", eventid);
            cmd.Parameters.AddWithValue($"@subscribers", subscribers.Trim());

            cmd.Connection = conn;
            await conn.OpenAsync();

            var reader = await cmd.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                //subscribersDetails = await GenerateSubscribersDataAsync(reader);

                while (await reader.ReadAsync())
                {
                    Subscriber subscriber = new Subscriber();

                    subscriber.SubscriberCode = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberCode")));
                    subscriber.SubscriberEndpoint = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberEndpoint")));
                    subscriber.SubscriberName = Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberName")));
                    subscriber.TopicName = Convert.ToString(reader.GetValue(reader.GetOrdinal("TopicName")));
                    subscriber.EventName = Convert.ToString(reader.GetValue(reader.GetOrdinal("EventName")));
                    subscriber.SubscriberActiveDate = Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("SubscriberActiveDate")));

                    if (!reader.IsDBNull(reader.GetOrdinal("SubscriberExpiryDate")))
                    {
                        subscriber.SubscriberExpiryDate = Convert.ToDateTime(reader.GetValue(reader.GetOrdinal("SubscriberExpiryDate")));
                    }

                    subscribersDetails.Add(subscriber);
                }
            }

            _logger.LogInformation($"Fetch_SubscribersDetails: Completed -- Retrieving Subscriber details  from database based on topicid: {topicid}, eventid: {eventid} subscribers: {subscribers} , TotalSubscribers: {subscribersDetails.Count}.");

            return subscribersDetails;
        }
    }
    catch (SqlException ex)
    {
        _logger.LogError($"Message: {ex.Message}. \nInnerException:{ex.InnerException}. \nStackTrace: {ex.StackTrace}. \nInnerExceptionMessage:{ex.InnerException?.Message}.");
        subscribersDetails = null;
    }
    catch (Exception ex)
    {
         subscribersDetails = null;
         _logger.LogError($"Message: {ex.Message}. \nInnerException:{ex.InnerException}. \nStackTrace: {ex.StackTrace}. \nInnerExceptionMessage:{ex.InnerException?.Message}.");
    }

    return subscribersDetails;
}

public class Subscriber
{
        public string SubscriberCode { get; set; }
        public string SubscriberName { get; set; }
        public string SubscriberEndpoint { get; set; }
        public string TopicName { get; set; }
        public string EventName { get; set; }
        public int TopicId { get; set; }
        public int EventId { get; set; }
        public DateTime SubscriberActiveDate { get; set; }
        public DateTime? SubscriberExpiryDate { get; set; }
}

SubscriberCode column may be NULL or have data. subscribers data passed as comma delimiter value.

Case #1: if subscribers data is passed, the stored procedure should return matching subscriber details and also where subscrbercode is empty based on topic id and event id

exec FetchSubscriberDetails_BasedonTopicEvent @topicid =1,@eventid = 1, @subscribers = '100,200' 

In above sample all 3 records must return but only SubscriberId=1 and SubcriberId=2 records are coming in c# but not SubscriberId =3

Case #2: if subscribers data is not passed, only subscrbercode empty records should return based on topic id and event id.

exec FetchSubscriberDetails_BasedonTopicEvent @topicid =1,@eventid = 1, @subscribers = '' 

In above example, only 1 record with SubscriberId = 3 row data should return.

This is working fine in Azure SQL when executing but when running with C# code, I am not getting proper records where subscribercode is NULL.

The C# code should return SubscriberId = 3 record which is not happening.

Expected output for case #2:

SubscriberId -- 3
SubscriberCode -- NULL
Subscriber Name --  Wipro
ActiveDate --   01-01-2022
ExpiryDate  -- NULL
Endpoint --     https://..3
TopicId --  1
EventId --  1

Actual output of case #2:

No records found

I tried replacing NULL with blank empty text in row data. This also does not help me. What am I doing wrong?

Answer 1

You have a number of flaws in your code

• Needs a using

SqlCommand cmd = new SqlCommand();

• Also needs a using

var reader = await cmd.ExecuteReaderAsync();

• Specify your parameters types and lengths/precision explicitly

cmd.Parameters.AddWithValue($"@subscribers", subscribers.Trim());

Instead do

cmd.Parameters.Add("@subscribers", SqlDbType.NVarchar, -1).Value = subscribers.Trim();

• This is your primary issue. This should not be there at all, it is causing you to always skip the first row.

if (await reader.ReadAsync())

• This one is fiendishly complex, completely not necessary

Convert.ToString(reader.GetValue(reader.GetOrdinal("SubscriberCode")))

Instead do this

(string)reader["SubscriberCode"]

Or if you might get a null do this

reader["SubscriberCode"] as string

---

Ideally you wouldn't use a comma-separated list at all. Instead use a Table Valued Parameter.

First define a table type. I keep a few standard ones.

CREATE TYPE dbo.StringList AS TABLE (value nvarchar(100) PRIMARY KEY);

CREATE PROCEDURE [dbo].[FetchSubscriberDetails_BasedonTopicEvent]
    @eventid int,
    @topicid int,
    @subscribers dbo.StringList READONLY,
    @noSubscriber bit
AS 
BEGIN

    /** Fetching Subscriber Details for all Subscribers based on Topic and Event**/
   SELECT DISTINCT  
       s.SubscriberCode, s.SubscriberName,
       s.SubscriberActiveDate, s.SubscriberExpiryDate,
       s.SubscriberEndpoint
   FROM
       [dbo].[MessageSubscribers] s 
   WHERE 
       s.TopicId = @topicid 
       AND s.EventId = @eventid
       AND CURRENT_TIMESTAMP BETWEEN s.SubscriberActiveDate AND ISNULL(s.SubscriberExpiryDate, CURRENT_TIMESTAMP)
       AND (@noSubscriber = 1
            OR s.SubscriberCode IN (SELECT [value] FROM @subscribers);

You use it in C# like this:

var table = new DataTable { Columns = {
    {"value", typeof(string)}
} };
foreach (var subscriber in SomeSubscriberList)
    table.Rows.Add(subscriber);

cmd.Parameters.Add(new SqlParameter("@subscribers", SqlDbType.Structured) {
    TypeName = "dbo.StringList",
    Value = table
});

Also why use DISTINCT, what does it get you? Why do you have duplicates in the first place.