looking for suggesions re: accessing data and populating List/arraylist/list

Question

Hello All - I am looking to do something like this:

1. access records in db with a datareader. I won't know how many records will come back - somewhere between 1 and 300 most of the time. The data will look something like this "0023","Eric","Harvest Circle", "Boston" for each record.
2. I wanted to immediately populate something (array, list?) and close the connection.
3. Pass the filled object back to my webservice where I will convert it to JSON before sending on to client.

The problem I am running into is what object/objects to populate with data from the datareader. I started out using an ArrayList (and have now read about using List instead), but although I could get back all the records (in this case just 2 Items), I could not access the individual fields inside each Item (Eric, Boston, etc).

Enter Plan B: foreach record in datareader, add the individual column values to an array and then add each array to the List. I think this will work, but I am not sure how to instantiate an array when I don't know how many would need to be instantiated. In other words, I would normally do this string[] myarray = new string[]{"eric", "boston", "etc"};

But if I have multiple records, what does that look like? Populate array, add to List, clear original array and then repopulate it, add it to List, etc?

Any help would be greatly appreciated! Also, I am very open to alternative ways of doing this.

Thanks!

Answer 1

The standard answer here, short of using a persistence layer like the Entity Framework, is to create "entity" classes with the fields you need. Something like:

class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

... and then collect these up in a collection of some kind. A List<Customer> is fine.

List<Customer> customers = new List<Customer>();

string connectionString = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("GetData");
    using (IDataReader reader = command.ExecuteReader())
    {
     while (reader.Read())
     {
      Customer c = new Customer();
      c.Id = (int)reader["ID"];
      c.FirstName = (string)reader["FirstName"];
      c.LastName = (string)reader["LastName"];
      customers.Add(c);
     }
    }
}

If you have a large number of tables, or a mutable list of fields, this can get hard to maintain. For simple tasks, though, this works fine.

Answer 2

Create an object that has properties that matches the fields that will be coming back in the datareader:

public class MyClass
{
   public int Id{get;set;}
   public string Name{get;set;}
   ....
}

Then, foreach over all the results in the datareader. Then, create an object of this type and set all the properties with the values in the columns for that records. Then, add them to a List<MyClass>.

Answer 3

I would recommend looking into LINQ to SQL (if you have a very simple database), or NHibernate (if you have a more complex database). These are OR mappers that can greatly simplify the process of turning a SQL query into objects that can be used in your domain, passed through services, etc.

Answer 4

Michael's answer is usually your best bet. Once things start getting complicated, you can add abstractions (you'll need object factories that take in sql readers and various other fun things) and start passing around objects and factories. I've found this strategy is pretty helpful, and a well-written datalayer class can be used throughout your database-related projects.

One alternative is to use an SqlDataAdapter and to convert your output to a DataSet. In my experience this is pretty unwieldy and generally a pretty bad idea. That said, if your tendency is to grab a chunk of data, manipulate it (update pieces, add new pieces, delete pieces), this can be a nice way to do it in memory with C# code. Think twice before trying this technique. This is basically equivalent to grabbing data and populating a List of rows, though a DataSet has a bit more stuff in it than an array (e.g. it includes column names).

I feel like using an SqlDataAdapter to populate a DataSet is the closest answer to giving you what you are asking for in your question but the worst answer in terms of giving you what you actually need.

Answer 5

I think a simple solution is in order here. I would create a DataAdapter and then fill a DataTable with the data. You can then close the connection, and the data table will remain populated, since, unlike ADO, ADO.NET defaults to disconnected recordsets.

You can then easily access the data by fieldname, e.g.,

myDataTable["FirstName"].ToString()

If all you are going to do is convert the data to JSON, creating an object that maps properties to the database fields seems unnecessary since ADO.NET gives you this out of the box.

Answer 6

Odds are you don't need a concrete class like a List or array. An IEnumerable is probably good enough. With that in mind, this is my favorite pattern:

public IEnumerable<IDataRecord> GetData()
{
    using (var cn = getOpenConnection(connectionString))
    using (var cmd = new SqlCommand("GetData", cn))   
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            yield return (IDataRecord)rdr;
        }
    }
}

You could also modify that to create strongly-typed business objects, but I generally keep that in a separate layer/tier. Iterator blocks will do lazy evaluation/deferred execution and so the extra translation tier will not require an additional iteration of the results.