SqlHelper.ExecuteReader results varying and unexpected when certain number of records are returned...

Question

Note: You may not need to read the whole thing to know what's wrong... skip down to the part about the 3 scenarios if you'd like. Only read the begin if you'd like some background info on how I've tried implementing this and where the errors occurred.

To begin with, I'm trying to retrieve a list of Clients stored in the CRM_Clients table for a given callerId.

I retrieve the clients from my Controller with the SelectLiveClientsForCaller method. The message is then passed on to the DAL through the static Instance method in the DataProvider class:

    public List<Client> SelectLiveClientsForCaller(int callerID)
    {
        List<Client> results = new List<Client>();
        IDataReader reader;

        reader = DataProvider.Instance().SelectLiveClientsForCaller(callerID);

        if (reader.Read())
        {
            // If I break here and enumerate the reader, it says that the IEnumerable returned no results
            results = CBO.FillCollection<Client>(reader); // Always comes out as a count of 0
        }

        return results;
    }

My DataProvider class is an abstract class which outlines all the methods the SqlDataProvider has available:

public abstract class DataProvider
{
    // singleton reference to the instantiated object 
    static DataProvider  objProvider = null;

    // constructor
    static DataProvider()
    {
        CreateProvider();
    }

    // dynamically create provider
    private static void CreateProvider()
    {
        objProvider = (DataProvider)Reflection.CreateObject("data", "Owu.Modules.CRM", "");
    }

    // return the provider
    public static  DataProvider Instance() 
    {
        return objProvider;
    }

    public abstract IDataReader SelectLiveClientsForCaller(int callerID);

    /* More abstract methods here... */
}

In the subclass SqlDataProvider the SelectLiveClientsForCaller method is actually handled and calls SqlHelper.ExecuteReader for the stored procedure CRM_Clients_SelectLiveForCaller:

public class SqlDataProvider : DataProvider
{
    private const string ProviderType = "data";
    private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
    private string _myConnectionString;
    private string _providerPath;
    private string _objectQualifier;
    private string _databaseOwner;
    private string _moduleQualifier;

    public SqlDataProvider()
    {
        //Read the configuration specific information for this provider
        Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

        //Read the attributes for this provider
        //Get Connection string from web.config
        _myConnectionString = Config.GetConnectionString();
    }

    public string MyConnectionString
    {
        get {   return _myConnectionString;   }
    }

    public override IDataReader SelectLiveClientsForCaller(int callerID)
    {
        return (IDataReader)SqlHelper.ExecuteReader(
            myConnectionString,
            "CRM_Clients_SelectLiveForCaller",
            callerID);
    }

    /* More methods here... */
}

Finally, the stored procedure CRM_Clients_SelectLiveForCaller

ALTER PROCEDURE [dbo].[CRM_Clients_SelectLiveForCaller]
@CallerID int
AS
BEGIN
    SET NOCOUNT ON;

    IF @CallerID = -1
    BEGIN
        SELECT * FROM CRM_Clients WHERE IsDeleted = 'false'
    END
    ELSE 
    BEGIN
        SELECT * FROM CRM_Clients WHERE ClientID IN 
            (SELECT ClientID FROM CRM_CallersClients WHERE CallerID = @CallerID)
        AND IsDeleted = 'false'
    END
END

Returns all non-deleted clients for a given callerid.

This should then go back up the line and return results from the controller...

There are 3 scenarios I've noticed so far

1. If there are no records returned from the stored proc (executed through sql server mgmt studio), when calling the sequence of methods reader.Read() returns false and it skips over it completely.

2. If there is 1 record returned from the stored proc (executed through sql server mgmt studio), when calling the sequence of methods reader.Read() returns true but enumerating the results gives a message saying IEnumerable returned no results

3. If there are 2 records returned from the stored proc (executed through sql server mgmt studio), when calling the sequence of methods reader.Read() returns true but enumerating the results only returns 1 record instead of 2

Can anyone explain why I'm getting these results for each scenario?

If you need any more info please ask and I'll update this as soon as I can.

Thanks,
Matt

Answer 1

Reader.Read() only examines the first row that is returned, and automatically moves the result set onto the next record.

Try the following instead:

while (reader.Read())
{
results = CBO.FillCollection<Client>(reader); 
}

You may need to adjust your FillCollection method to accomodate this change.

Answer 2

Each call to reader.Read() reads a row, so you are consuming rows just like any IEnumerable caller would consume rows.

You need to reconsider calling .Read() outside your fill routine to check for existence of rows, because if you call .Read() again inside the fill routine before using the row currently positioned in the reader, you will have lost that first row.

Answer 3

reader.Read tells you if a record is available

//expecting one record
if(reader.Read())
{
     //get reader["values"];
}

or..

//expecting multiple records
while(reader.Read())
{
     //get reader["values"];
}