Fill any ArrayList with results of an SQL statement

Question

I have the following code that takes an SQL statement (string), loads the results into an ArrayList (organisationList) which is a collection of Organisations:

public void FillDataGridView(DataGridView grid, string SQLCommand)
{
    SqlCommand dataCommand = new SqlCommand();
    dataCommand.Connection = dataConnection;
    dataCommand.CommandType = CommandType.Text;
    dataCommand.CommandText = SQLCommand;

    SqlDataReader dataReader = dataCommand.ExecuteReader();

    while (dataReader.Read())
    {
        Organisation org = new Organisation();

        org.OrganisationId = (int)dataReader["OrganisationId"];
        org.OrganisationName = (string)dataReader["OrganisationName"];

        organisationList.Add(org);
    }

    grid.DataSource = organisationList;
    dataReader.Close();
}

I would like to adapt this method to be possible to fill an ArrayList passed into it.

Is it possible for me to pass the list into the method and have something like:

public void FillArrayList(DataGridView grid, SqlDataReader reader, ArrayList list)
{
    //Fill the list with the contents of the reader
    while (reader.Read())
    {
        Object  obj = new Object

        for(int i; i = 0; i < obj.NoOfProperties)
        {
            obj.Property[i] = reader[i];
        }

        list.Add(obj);
    }
}

Sorry if this is a little vague, I'm quite new to OOP and a bit lost!

Edit: Based on the advice of Darren Davies, I have modified the method as follows:

public void FillArrayList<T>(DataGridView grid, SqlDataReader reader, List<T> list)
{
    //Fill the list with the contents of the reader
    while (reader.Read())
    {
        Object obj = new Object();
        Type type = typeof(T);

        FieldInfo[] fields = type.GetFields(); // Get the fields of the assembly
        int i = 0;

        foreach(var field in fields)
        {
            field.SetValue(obj, reader[i]); // set the fields of T to the reader's value
            // field.setValue(obj, reader[field.Name]); // You can also set the field value to the explicit reader name, i.e. reader["YourProperty"]
            i++;
        }

        list.Add((T)obj);
    }

    grid.DataSource = list;
}

When I run the code, I get an error when casting the object to type T:

Unable to cast object of type 'System.Object' to type 'TestHarness.Organisation'.

I was under the impression that an Object could store anything. Can anyone advise me on why this cast cannot be performed?

Thanks,

Andy

Answer 1

Unless you are using .NET 1.1, you probably shouldn't be using ArrayList; the generic List<T> is preferable.

You cannot add members to object - it is not extensible. You would need to know the type of object to create. Generics would be a reasonable object. However, to save you some time, you might do well to look at dapper:

var list = dataConnection.Query<YourType>(SQLCommand).ToList();

Which will do everything, using a direct column-name to member-name mapping. You would need to create a YourType class with the properties (appropriately typed) that you expect.

If you are using 4.0, dapper also supports dynamic:

var list = dataConnection.Query(SQLCommand).ToList();

This uses dynamic, so you can still do (without declaring a type):

foreach(var obj in list) {
    Console.WriteLine(obj.OrganisationId);
    Console.WriteLine(obj.OrganisationName);
}

Personally I'd only use the dynamic approach if the data is used very close to where it is accessed. For returning from a method, the generic approach is preferred. Likewise, dynamic doesn't work well with DataGridView.

Finally, I notice no parameters; you always want to use parameters and not concatenation. Dapper supports that too:

string foo = ...;
var list = dataConnection.Query<YourType>(
    "select * from SomeTable where Foo = @foo", new { foo }).ToList();