We all use DataReader to fill or populate our custom collection. Usually the code is pretty repetetive since we are filling different collections with different objects. In my opinion there should be one Fill<T> method which should fill all the collections provided with the correct parameters. First take a look at the common way of filling a collection:
private static void FillCategoryListWithoutHelper()
{
List<Category> categoryList = new List<Category>();
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("SELECT CategoryID,CategoryName,Description FROM Categories", myConnection);
myConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
Category category = new Category();
category.CategoryID = (int) reader["CategoryID"];
category.CategoryName = reader["CategoryName"] as String;
category.Description = reader["Description"] as String;
categoryList.Add(category);
}
myConnection.Close();
myCommand.Dispose();
}
The above method displays the common method of filling the List<Category>. Now, if I am using a Customer object and need to fill a customer list then I need to implement the same code again with a different select command and stuff. This is where the problem is we need to implement a single method and pass the reader, the type of list to fill, the fields to fill the list from and the type of object the list will contain and it should automatically fill the list. Below is small piece of code that shows how to fill a generic list.
// The Fill<T> method is used to fill the generic list with the column data
public static void Fill<T>(IDataReader reader, IList<T> list, Type type , string[] fields)
{
int index = 0;
// run the reader
while (reader.Read())
{
// create an instance of the type
T item = (T)Activator.CreateInstance(type);
// get all the properties of the type
PropertyInfo[] properties = ((Type) item.GetType()).GetProperties();
for (int j = 0; j < fields.Length; j++)
{
// get the index of the property
index = FindProperyIndexByColumnName(fields[j], properties);
// set the value of the property
properties[index].SetValue(item, reader[fields[j]], null);
}
// add the item to the list
list.Add(item);
}
}
// This method finds the index of the property to be filled
private static int FindProperyIndexByColumnName(string columnName,PropertyInfo[] prop)
{
int index = -1;
for (int i = 0; i < prop.Length; i++)
{
if (prop[i].Name.Equals(columnName))
{
index = i;
break;
}
}
if (index == -1) throw new ArgumentOutOfRangeException("Column was not found");
return index;
}
And here is how you can call and fill up your custom list:
private static void FillCategoryListWithHelper()
{
string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
string query = @"SELECT CategoryID,CategoryName,Description FROM Categories";
SqlCommand myCommand = new SqlCommand(query, myConnection);
string[] columns = { "CategoryName", "Description" };
List<Category> categoryList = new List<Category>();
myConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
DataReaderHelper.Fill(reader, categoryList, typeof(Category), columns);
// close the connection
myConnection.Close();
myCommand.Dispose();
// print out the list
foreach (Category c in categoryList)
{
Console.WriteLine(c.CategoryID);
Console.WriteLine(c.CategoryName);
Console.WriteLine(c.Description);
Console.WriteLine("----------------");
}
}
The code will run fine and fill the List<Category>. Offcourse this only works for a very simple scenario and will throw an exception when you try to use nested types. But I think that through the power of Generics we can create a Generic Fill<T> method that fills the list or a collection with custom objects.