SqlDataReader not returning all rows when adding to List<MyClass>

Question

I am attempting to grab some organization data from a db. I need some more data, but I am stuck at just the org name, and org address. I am attempting to perform a query that involves a join, and create an object, which is a list of a custom class I have created. The weird thing is, if I comment the code in my while loop, and just get the name of the orgs, it works, but as soon as I try to create a list of objects, it doesn't.

Here is what I've got, starting with my class:

namespace FFDFrameWorkPart
{
    public class OrgData
    {
        public string orgName { get; set; }
        public string orgAddress { get; set; }
    }
}

List<OrgData> OrgObject = new List<OrgData>();
List<string> orgName = new List<string>();
    using (SqlConnection connection = new SqlConnection(connectString))
    {
        connection.Open();
        SqlCommand GrabOrgsFromDb = new SqlCommand(getConstitData, connection);
        SqlDataReader reader = GrabOrgsFromDb.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                orgName.Add(reader.GetString(0));
                OrgObject.Add(new OrgData()
                {
                    orgName = reader.GetString(0),
                    orgAddress = reader.GetString(1)
                 });
            }
        }
        catch (Exception ex)
        {
            consoleLog.Value = ex.ToString();
        }
        finally
        {
            reader.Close();
        }
    }

Running that code results in orgName.Count and OrgObject.Count to be in the neighborhood of 20.

If I just comment out

OrgObject.Add(new OrgData()
{
    orgName = reader.GetString(0),
    orgAddress = reader.GetString(1)
});

Then orgName.Count jumps to about 28,000. No other change to the code is necessary, just comment out where I am trying to build my list, and I lose around 28,000 records from my results.

EDIT: It's hitting a null value in one of the fields and stopping

Is there an issue in data source on column 1 of the 21st row record? It would then break the loop. — ydoow
– ydoow, Commented Jun 21, 2016 at 4:58

Hari Prasad · Accepted Answer · 2016-06-21 05:46:18Z

3

You could use SqlDataReader.IsDBNull to check whether the column contains non-existent or missing values.

OrgObject.Add(new OrgData()
{
    orgName = reader.IsDBNull(0) ? null : reader.GetString(0) ,
    orgAddress = reader.IsDBNull(1) ? null : reader.GetString(1) 
);

answered Jun 21, 2016 at 5:46

Hari Prasad

17k4 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serve Laurijssen · Accepted Answer · 2016-06-21 06:39:25Z

1

Check if the fields are NULL and instead of using hardcoded ordinals it's better to use GetOrdinal. this way if fields are added to the table the ordinals will automatically adapt.

OrgObject.Add(new OrgData()
{
    orgName = reader.IsDBNull(reader.GetOrdinal("Name")) ? null : reader.GetString(reader.GetOrdinal("Name")) ,
    orgAddress = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString(reader.GetOrdinal("Address")) 
);

or a little bit cleaner

int nameOrd = reader.GetOrdinal("Name");
int addressOrd = reader.GetOrdinal("Address");

    OrgObject.Add(new OrgData()
    {
        orgName = reader.IsDBNull(nameOrd) ? null : reader.GetString(nameOrd) ,
        orgAddress = reader.IsDBNull(addressOrd) ? null : reader.GetString(addressOrd));

edited Jun 21, 2016 at 6:39

answered Jun 21, 2016 at 5:49

Serve Laurijssen

9,9606 gold badges55 silver badges109 bronze badges

Collectives™ on Stack Overflow

SqlDataReader not returning all rows when adding to List<MyClass>

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related