List<object> always returns 1 as it's count

Question

I have created a list and the count always returns 1. When I view the objects properties (mi), I can see that all of the values I have retrieved are in fact there. So I know the DB part works and I am reading data back

When I try to get the value from the list index, it will only allow me to access the first item (0). Its as if it has counted the object I have added to the list as one value, even though in debug , the values are itemised.

The strange thing is it, it shows values of the object items (MeterId, MeterType etc...).

The values are there but they don't seem to have any index. I am trying to assign the values by using:

           var mylist = GetMeterInfo.GetMeterInformation(MeterId);
            var mid= mylist[0];
            var mid1 = mylist[1];
            var mid2 = mylist[2];

But it fails when I try assigning 'myList[1]' with an out of index error, because there is no index greater than 0 . Even when I look at "myList" I can see all of the 10 values there, but I cannot get to them.

public static List<MeterInformation> GetMeterInformation(int MeterId)       {
        string SqlQuery = "*************"
        SqlConnection conn = null;
        SqlCommand cmd = null;
        SqlDataReader rd = null;

        conn = new SqlConnection(Connections.Conns.CS);
        cmd = new SqlCommand(SqlQuery, conn);

        conn.Open();
        rd = cmd.ExecuteReader();
 List<MeterInformation> MeterInfo = new List<MeterInformation>();           

        while (rd.Read()) {
            MeterInformation mi = new MeterInformation();
            mi.MeterId = rd.GetInt32(0);
            mi.MeterType = rd.GetString(1);
            mi.MeterName = rd.GetString(2);
            mi.MeterDescription = rd.GetString(3);
            mi.MeterSerial = rd.GetString(4);
            mi.MeterMPAN = rd.GetString(5);
            mi.MeterCreatedOn = rd.GetDateTime(6);
            mi.MeterLocation = rd.GetString(7);
            mi.MeterBuilding = rd.GetString(8);
            mi.MeterSite = rd.GetString(9);

            MeterInfo.Add(mi);
        }

        conn.Close();
        conn.Dispose();

        return MeterInfo;  

Any pointers, I have run out of ideas...

Answer 1

Rather than trying to be clever, I just made the list List. I asked a buddy of mine and as you all say it is passing the entire object as one thing.

Answer 2

You are creating a single MeterInformation object and you keep adding the same object to the list.

In the loop, you are simply modifying that single object and adding another reference of it to the list.

So, if you loop 5 times, the output would be a list that contains 5 references to the same MeterInformation object that has the information of the last row queried from the database.

To fix the problem, move this line inside the while loop:

MeterInformation mi = new MeterInformation();

This will make sure that you create a new MeterInformation object for each row in the result set.

UPDATE:

Trying to understand your problem again, I think that you should expect to get a single record from the database since your are retrieving a specific Meter (using a specific ID). I guess that your SQL query targets a specific record.

Your confusion should be cleared if you understand that MeterId, MeterType, MeterName ...etc are properties of a single object.

So to access them you can do this:

var record = GetMeterInfo.GetMeterInformation(MeterId)[0];

var id = record.MeterId;

var type = record.MeterType;

var name = record.MeterName;

If fact, since you expect the method to return a single item, you should not return a List, but instead return a MeterInformation like this:

public static MeterInformation GetMeterInformation(int MeterId)
{
    //...

    rd.Read();

    MeterInformation mi = new MeterInformation();
    mi.MeterId = rd.GetInt32(0);
    mi.MeterType = rd.GetString(1);
    mi.MeterName = rd.GetString(2);
    mi.MeterDescription = rd.GetString(3);
    mi.MeterSerial = rd.GetString(4);
    mi.MeterMPAN = rd.GetString(5);
    mi.MeterCreatedOn = rd.GetDateTime(6);
    mi.MeterLocation = rd.GetString(7);
    mi.MeterBuilding = rd.GetString(8);
    mi.MeterSite = rd.GetString(9);

    MeterInfo.Add(mi);

    //...

    return mi;
}

And use it like this:

var record = GetMeterInfo.GetMeterInformation(MeterId);

var id = record.MeterId;

var type = record.MeterType;

var name = record.MeterName;

Answer 3

How about the following? Moved the mi into the while block.

var MeterInfo = new List<MeterInformation>();
using(var conn = new SqlConnection(Connections.Conns.CS))
{
    using (var cmd = new SqlCommand(SqlQuery, conn))
    {
        conn.Open();
        using (var rd = cmd.ExecuteReader())
        {
            while (rd.Read()) {
                var mi = new MeterInformation();
                mi.MeterId = rd.GetInt32(0);
                mi.MeterType = rd.GetString(1);
                mi.MeterName = rd.GetString(2);
                mi.MeterDescription = rd.GetString(3);
                mi.MeterSerial = rd.GetString(4);
                mi.MeterMPAN = rd.GetString(5);
                mi.MeterCreatedOn = rd.GetDateTime(6);
                mi.MeterLocation = rd.GetString(7);
                mi.MeterBuilding = rd.GetString(8);
                mi.MeterSite = rd.GetString(9);

                MeterInfo.Add(mi);
            }
        }
    }
}  
return MeterInfo;

Answer 4

Move mi creation in while loop, because you are changing and trying to add same object constantly.

    while (rd.Read()) {
        MeterInformation mi = new MeterInformation();
        mi.MeterId = rd.GetInt32(0);
        mi.MeterType = rd.GetString(1);
        mi.MeterName = rd.GetString(2);
        mi.MeterDescription = rd.GetString(3);
        mi.MeterSerial = rd.GetString(4);
        mi.MeterMPAN = rd.GetString(5);
        mi.MeterCreatedOn = rd.GetDateTime(6);
        mi.MeterLocation = rd.GetString(7);
        mi.MeterBuilding = rd.GetString(8);
        mi.MeterSite = rd.GetString(9);

        MeterInfo.Add(mi);
    }