CODEWITHSUNDEEP
c#.netsqlarraylistsqldatareader
amarsha4
amarsha4

Reputation: 481

Fill any ArrayList with results of an SQL statement

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

Upvotes: 3

Views: 6850

Answers (4)

Darren
Darren

Reputation: 70728

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

Upvotes: 2

Jay
Jay

Reputation: 3355

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

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063013

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

Upvotes: 6

Waqar
Waqar

Reputation: 2601

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.

Upvotes: 1

Related Questions