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

Marc Gravell · Accepted Answer · 2012-05-19 11:40:09Z

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();

Darren · Accepted Answer · 2012-05-20 12:53:54Z

2

If you are using C# 2.0. or greater use Generics rather than ArrayList.

You could use Reflection to the get the properties of the type you pass in:

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); // get the type of T (The paramter you passed in, i.e. Organisations)

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

        foreach(var field in fields) // Loop round the fields 
        {
            field.setValue(obj, reader[i]); // set the fields of T to the readers 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(obj);
    }
}

To invoke it:

 FillArrayList(grid, reader, list);

Where list is a List type of Organisations

 List<Organisations> list = new List<Organisations>();

http://msdn.microsoft.com/en-us/library/ms379564(v=vs.80).aspx

edited May 20, 2012 at 12:53

answered May 19, 2012 at 11:47

Darren

71.2k24 gold badges141 silver badges146 bronze badges

3 Comments

amarsha4 Over a year ago

This answer has definitely got me the furthest, but Visual Studio doesn't like the list.Add(obj); line. The compiler is attempting to convert from 'Object' to 'T'. Am I missing a line where I specify that the Object will always be of type T? Thanks for your help.

Darren Over a year ago

@amarshs Im currently using my phone to reply so my answer will be short until I can access a computer. I believe the error is referring to the List you are passing in is not the same type as Object. Rather than using Object obj = new Object(); you could do Organisations obj = new Organisations(); then add it to the list. You could also change Organisations to the type (T) you are passing

amarsha4 Over a year ago

Hi Darren, Thanks for your reply. I don't want to use any reference to Organisation in this method, as I want to have the method take a data reader and fill a list of any object. Before your reply, I tried replacing the line in question with list.Add((T)obj); but this generated a run-time error: Unable to cast object of type 'System.Object' to type 'QSurveillanceTestHarness.Organisation'. I thought an 'Object' class could refer to any type of object. Now I'm more confused. Thanks again, Andy

Waqar · Accepted Answer · 2012-05-19 11:29:48Z

1

If you create the properties of your class with same name as the columns in the returned data set then you can use reflection to construct any type of object from data reader or data table (if columns of data reader or data table and properties of object are matched).

You can see followin this link. It shows how to convert data table to collection of custom class. Working with data reader would be same.

answered May 19, 2012 at 11:29

Waqar

2,60119 silver badges15 bronze badges

Comments

Jay · Accepted Answer · 2012-05-19 12:07:54Z

0

Couldn't you also use DataContext and give your connection string and then use Linq?

http://msdn.microsoft.com/en-us/library/bb399375.aspx

answered May 19, 2012 at 12:07

Jay

3,3751 gold badge30 silver badges40 bronze badges

Collectives™ on Stack Overflow

Fill any ArrayList with results of an SQL statement

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related