Almost every programmer knows (unless you have been living under a rock for the last five years or so) that you should be using classes for all of your programming. You should also be using collections of objects instead of using a Data Reader, a DataSet, or a DataTable in your applications. The reasons for using collections are many and are explored in this article.

In this article, you will learn to use a DataTable to fill a collection of objects. You will then see how to use a DataReader to fill the same collection of objects and how to use LINQ to iterate over a DataTable to build your entity collection. Finally, you will learn the use of nullable and non-nullable data types and how to use reflection to fill your collections.

Use Classes to Represent a Row of Data

We all retrieve data from a table at some time or another. There are many ways of getting this data, but how you work with the data within your program should be done with a class. There are many reasons for using a class. For instance, when using a DataRow, you must remember the column name; in your class, you get IntelliSense on the property name. If you mistype the column name when using a DataRow, you get a runtime error, unlike mistyping a property name on a class, which earns you a compile-time error. With a class, you get strong typing of data values where all values coming from a DataRow or a data reader are exposed as the object data type. Using a class makes it easy to control the serialization of the class data because you can add attributes to each column. This is just not possible with a DataRow or with a data reader. When you use a class, you can inherit from a base class to add extra properties and functionality to each class.

Why Use Collections

Most likely, you are not just reading a single row of data all the time, but instead are retrieving many rows of data. This means you need a collection of objects. You can use LINQ to SQL or the Entity Framework to generate code that creates these collections of objects for you. I think it is important to understand what these code generators do becausethe more you know, the better programmer you are. I you know how to build your own object collections using tried and true technology, you don’t have to rely on Microsoft. Using ADO.NET for data access and building classes with properties and methods has not changed since .NET 1.0, so that is always a stable choice.

Build an Entity Class

An “entity class” is a class most often used to represent the values from a single row of a database table. You need to create a property in C# or Visual Basic for each column defined in your table to build an entity class. In Figure 1, you can see the definition of a Product table and the corresponding Product class created in C#. You create each property with the appropriate .NET data type for the SQL Server data type.

Figure 1: For each table in SQL Server, you create a corresponding class in .NET.

When you read the data from the Product table, you create an instance of the Product class for each row in the table, as shown in Figure 2.

Figure 2: Read data from SQL Server and put it into a collection of objects in .NET.

You’ll need a little bit of code (in the snippet) to createthe T-SQL definition of the product table shown in Figure 1 and Figure 2. In the sample code that comes with this article, you’ll find a file named Product.sql that contains this table definition and also loads this table with over 6200 rows of data. The product data repeats, but I wanted to have enough data so I could perform timings to show the differences between the various methods that I present in this article.

CREATE TABLE Product
(
  ProductId int PRIMARY KEY NONCLUSTERED
                IDENTITY(1,1) NOT NULL,
  ProductName varchar(50) NOT NULL,
  IntroductionDate datetime NULL,
  Cost money NULL,
  Price money NULL,
  IsDiscontinued bit NULL
)

The Product class, shown in Listing 1, is an example of creating a class that matches each column in the Product table. In this first sample, you use public properties instead of a full property definition. Feel free to add a private variable and then use a complete property get/set to return and set the value of the private variable, if you want. You might also notice that I marked this class as partial. Marking a class as partial gives you the flexibility to use a code generator to create this class and if you change the schema of your table, you can regenerate the class. You can create another file with the other part of the Product class and add any additional properties or methods you want there. That way, you won’t have to worry about overwriting these additional elements if you regenerate the code.

Reading Data into a Generic List

Now that you have a table and a class in which to put each row of data from the table, let’s look at various ways to load up the collection of objects. To start, you will learn to use a SqlDataAdapter and a DataTable object. In Listing 2 you create a SqlDataAdapter to fill a DataTable object. Loop through each row of data in the DataTable and create a new instance of a Product class. Retrieve each individual column, convert the value into the appropriate data type and set the property of the Product object.

The DataConvert Class

If you are using normal data types for your properties rather than nullable types, you need to check on whether or not the data from the column is a null value. You need to come up with a default value to put into the property if the data is null. For example, in the case of DateTime, you might use the MinValue property of the DateTime class. For a decimal value, you might use a zero (0). This is just one approach you might take to handle nulls in your data classes, I am sure you can think of many others. Later in this article, you will look at using nullable types. Listing 3 shows the complete DataConvert class and the one generic method called ConvertTo.

Using a DataReader

As you probably know, the SqlDataAdapter uses a SqlDataReader to build the collection of DataRow objects that make up a DataTable. So, if you want, you can use a SqlDataReader to load your collections. Using a SqlDataReader is slightly faster than using a DataTable, so you might want to take advantage of that extra speed.

When using a SqlDataReader, you must close the data reader after you are done with it. You write a try…catch…finally statement and close the data reader in the finally block, or you can utilize the using statement. I like the using statement because you do not have to write as much code. In my tests with Visual Studio 2010, both ways run just as fast using the sample data for this article. Listing 4 shows you how to use a SqlDataReader to load your collection of product classes.

Listing 4 is fairly straightforward and very similar to the code you used to load the collection using a DataTable. After building a data reader object, loop through each row and retrieve each column of data. Convert the data coming from the column into an appropriate value based on the data type. Remember when reading from a DataRow or from a column in the SqlDataReader that the data comes in as an “object” data type. So you must convert it in order to put it into a strongly typed property in your Product object. Of course, you must still handle null values when using a data reader, so you use the DataConvert class just like before.

Using LINQ with your DataTable

As I already mentioned, using a DataTable is just a little slower than using a DataReader, but in most cases the difference is in milliseconds. For instance, in my sample data, I am loading 6,261 records from the Product table and it took 45 milliseconds, on average, to load those records into an entity collection using a DataTable. It took only 33 milliseconds, on average, to load the same entity collection using a DataReader.

Let’s look at one advantage of using a DataTable. A lot of developers today use LINQ. After loading data into a DataTable, you can iterate using a foreach statement as shown previously, or you can use LINQ to create a collection of entity objects.

Let’s use a LINQ query (Listing 5) to iterate over the collection of DataRow objects within a DataTable. After building your DataTable using a SqlDataAdapter, use the AsEnumerable method on the DataTable to turn the collection of DataRow objects into an enumerable list. LINQ requires the use of an enumerable list. The LINQ statement creates a new Product object and uses the DataConvert class to check for valid data and convert the column data into a value that can be stored into each property.

Use the DataRow Field Method

In the last two examples, you use a DataConvert class to convert data from an object data type to either an actual value or a default value if the column data was a null. But, sometimes you wish to know that the data was null and show a blank value instead of DateTime.MinValue or a Zero (0). In order to accomplish this, you can do two things; change your Product class to use nullable types or use the Field extension method on the DataRow class. Listing 6 shows you the Product class defined with nullable types.

Listing 7 uses LINQ with a DataTable to create a collection of Product objects where the properties are nullable types. Instead of using the DataConvert class, you use the Field extension method specifying the nullable data type to use for that property. If the data coming from the data row is null, a null is put into the property, otherwise the actual value is placed into the property. Notice that the ProductName property is only a string because there is no nullable string type. Because Field is an extension method, you need to add a reference to the System.Data.DataSetExtensions.dll in your project in order to use the Field method.

Use Reflection to Generalize your Code

In the last few samples, you wrote a single line of code for each property that you need to load from a column in your table. If you use reflection, you can shrink this code quite a bit. Yes, we all know that reflection is slow and probably should be avoided in most cases. But what I have found is that loading over 6200 product records into an entity collection still takes less than a second when using reflection.

To start this discussion, let’s review how to use reflection. In the code shown below, you can see the usual method of setting the ProductName property on an instance of the Product class:

C#
Product entity = new Product();
entity.ProductName = "A New Product";
    
Visual Basic
Dim entity as New Product()
entity.ProductName = "A New Product"

With reflection, you can write code that uses a string value of the property name, ProductName, to set the value “A New Product” into that property. The code below uses the InvokeMember method to call the Set property of the Product class:

C#
Product entity = new Product();
typeof(Product).InvokeMember("ProductName",
  BindingFlags.SetProperty,
    Type.DefaultBinder, entity,
     new Object[] { "A New Product" });
    
Visual Basic
Dim entity as New Product()
GetType(Product).InvokeMember("ProductName", _
   BindingFlags.SetProperty, _
     Type.DefaultBinder, entity, _
        New Object() { "A New Product" })

The InvokeMember is a method of the System.Type class. Using typeof() in C# or GetType() in Visual Basic returns an instance of the Type class which contains metadata about the Product class.

You pass five parameters to the InvokeMember method. The first parameter is the name of the property you wish to set. The second parameter is the name of the property or method you wish to invoke; in this case, the Set property. The third parameter tells InvokeMember that you are using the default binder. The fourth parameter is the variable that contains a reference to an instance of the class specified by the type (in this case, the Product object). The last parameter is an object array of whatever you need to pass to the method or property that you are invoking.

For setting the ProductName property, you only need a single object array of the string you are setting. If you were invoking a method of the Product class with two parameters, you would create an array of two values and those values would be passed to the parameters of the method.

A Better Way to Set Property Values

Although the InvokeMember method works for setting a property, it is actually quite slow. A more efficient way to set a property using reflection is to use the SetValue method on a PropertyInfo object. You call the GetProperty method on the Type class to retrieve a PropertyInfo object for the property you are interested in, such as ProductName. This PropertyInfo object has a SetValue method that you use to set the value on the specific property to a specific value. Below is an example of calling the SetValue method that does the same as the code presented earlier:

C#
Product entity = new Product();
    
typeof(Product).GetProperty("ProductName").
  SetValue(entity, "A New Product", null);
    
Visual Basic
Dim entity As New Product()
    
GetType(Product).GetProperty("ProductName"). _
  SetValue(entity, "A New Product", Nothing)

I find the SetValue method to be a little easier to understand than the InvokeMember method. Another big benefit of SetValue is it is over 100% faster than InvokeMember. That is a big difference and you should take advantage of it!

Apply Reflection to Loading Collections

Let’s now use the SetValue method to load collections of objects. For this sample, you will use the Product class that uses nullable types, as shown in Listing 6. In order for this reflection code to work, you need to make sure that your property names are exactly the same name as the column names in your table. Listing 8 shows you how to rewrite the code to use reflection and the SetValue method to load your collection of Product objects.

The code in Listing 8 uses a data reader, but you could just as well have used a DataTable. Before you loop through the rows in your data reader, you gather a collection of all the properties on your Product class into an array of PropertyInfo objects using the GetProperties method on the Type class.

For each row of data, you will loop through the PropertyInfo array and use the property name to retrieve the corresponding column in the data reader. You then use that property name in the call to the SetValue method to set the property from the column in the data reader. Notice that you have to first check to see if the column data is a null. If the data is null, set the property to a null value. This works because you are using the Product class with the nullable data types.

Create a Generic Base Class

Although the code in Listing 8 did simplify things quite a bit, you can cut down the code even more by creating a base class with a generic method to build the collection of entities. Listing 9 contains the complete code to the ManagerBase class with the generic method called BuildCollection. This BuildCollection method allows you to generically specify the type of entity, symbolized by <T>, that this method will create a collection of and return. Pass into this method an instance of the Type class (this will be the metadata for the Product class), and a SqlDataReader object.

The Build Collection method retrieves the collection of PropertyInfo objects from the instance of the Type class (Product). Next it loops through the data reader and creates a new instance of the entity using the Activator class’ CreateInstance method. A loop through the collection of PropertyInfo objects is then performed to gather the data from the reader and put the data into the corresponding property on the entity object. The newly created and populated entity is added to the generic List<T> collection. When all records have been processed, the generic list is returned from this method.

Use the ManagerBase Class

To use this base class, create a ProductManager class that inherits from the ManagerBase class (Listing 10). The GetProducts method is now only responsible for creating the SqlDataReader and then passing the type of Product and the data reader to the BuildCollection method in the base class. You can see that this significantly reduces the amount of code you need to write.

Using a Code Generator

As I mentioned before, you could use a code generator to generate all of the code shown in this article. Using Haystack (www.CodeHaystack.com) or CodeSmith (www.CodeSmithTools.com) you could easily create templates to generate all of the code. Again, be sure to use partial classes so you can regenerate code if your table schema changes. This gives you the flexibility to add additional properties and methods to your entity classes without losing the ability to regenerate your code.

Summary

In this article, you learned how to create an entity class and a collection of entity classes using DataTables and data readers. You can use collections of entity classes in your programming instead of data tables. Entity collections give you more flexibility with classes than you get with loosely-typed objects contained in data tables and data readers. You can create your own DataConvert class to be used to help you convert null data into a useable default value. Take advantage of the Field extension method on the DataRow class if you are working with nullable types. Reflection can be used to cut down the amount of code you write, but at the expense of a little speed. If you do use reflection, use the SetValue method instead of the InvokeMember method. You should also look at using code generation to generate much of this code.

NOTE: You can download the complete sample code at my website. http://www.pdsa.com/downloads. Choose “PDSA Articles”, then "Code Magazine - Creating Collections of Entity Objects" from the drop-down list.