Creating Collections of Entity Objects

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. If 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 create the 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.

Listing 1: A product class with one property per column from the Product table

C#
Public partial class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public DateTime IntroductionDate { get; set; }
    public decimal Cost { get; set; }
    public decimal Price { get; set; }
    public bool IsDiscontinued { get; set; }
}

Visual Basic
Partial Public Class Product
    Public Property ProductId As Integer
    Public Property ProductName As String
    Public Property IntroductionDate As DateTime
    Public Property Cost As Decimal
    Public Property Price As Decimal
    Public Property IsDiscontinued As Boolean
End Class

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.

Listing 2: Load a collection of objects using a DataTable

C#
public List<Product> GetProducts()
{
    List<Product> ret = new List<Product>();
    Product entity;
    SqlDataAdapter da;
    DataTable dt = new DataTable();

    da = new SqlDataAdapter("SELECT * FROM Product",
        "Server=Localhost;Database=Sandbox;
            Integrated Security=Yes");

    da.Fill(dt);

    foreach (DataRow dr in dt.Rows)
    {
        entity = new Product();

        entity.ProductId = Convert.ToInt32(dr["ProductId"]);
        entity.ProductName = dr["ProductName"].ToString();
        entity.IntroductionDate = DataConvert.ConvertTo<DateTime>(
            dr["IntroductionDate"], default(DateTime));
        entity.Cost = DataConvert.ConvertTo<decimal>(dr["Cost"],
            default(decimal));
        entity.Price = DataConvert.ConvertTo<decimal>(dr["Price"],
            default(decimal));
        entity.IsDiscontinued = DataConvert.ConvertTo<bool>(dr["IsDiscontinued"],
            default(bool));

        ret.Add(entity);
    }

    return ret;
}

Visual Basic
Public Function GetProducts() As List(Of Product)
    Dim ret As New List(Of Product)()
    Dim entity As Product
    Dim da As SqlDataAdapter
    Dim dt As New DataTable()

    da = New SqlDataAdapter("SELECT * FROM Product", _
        "Server=Localhost;Database=Sandbox;
            Integrated Security=Yes")

    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        entity = New Product()

        entity.ProductId = Convert.ToInt32(dr("ProductId"))
        entity.ProductName = dr("ProductName").ToString()
        entity.IntroductionDate = DataConvert.ConvertTo( _
            Of DateTime)(dr("IntroductionDate"), DateTime.MinValue)
        entity.Cost = DataConvert.ConvertTo( _
            Of Decimal)(dr("Cost"), 0D)
        entity.Price = DataConvert.ConvertTo( _
            Of Decimal)(dr("Price"), 0D)
        entity.IsDiscontinued = DataConvert.ConvertTo( _
            Of Boolean)(dr("IsDiscontinued"), False)

        ret.Add(entity)
    Next

    Return ret
End Function

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.

Listing 3: The DataConvert class helps with loading data and defaulting null values to a specified value

C#
public class DataConvert
{
    public static T ConvertTo<T>(object value, 
        object defaultValue) where T : struct
    {
        if (value.Equals(DBNull.Value))
            return (T)defaultValue;
        else
            return (T)value;
    }
}

Visual Basic
Public Class DataConvert
    Public Shared Function ConvertTo(Of T As Structure) _
        (value As Object, defaultValue As Object) As T
        If value.Equals(DBNull.Value) Then
            Return DirectCast(defaultValue, T)
        Else
            Return DirectCast(value, T)
        End If
    End Function
End Class

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: Use a SqlDataReader to load your collection classes and get the best performance

C#
private List<Product> GetProducts()
{
    SqlCommand cmd = null;
    List<Product> ret = new List<Product>();
    Product entity = null;

    cmd = new SqlCommand("SELECT * FROM Product");
    using (cmd.Connection = new SqlConnection(
        "Server=Localhost;Database=Sandbox;Integrated
        Security=Yes"))
    {
        cmd.Connection.Open();
        using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                entity = new Product();

                entity.ProductId = Convert.ToInt32(rdr["ProductId"]);
                entity.ProductName = rdr["ProductName"].ToString();
                entity.IntroductionDate =
                    DataConvert.ConvertTo<DateTime>(
                        rdr["IntroductionDate"], default(DateTime));
                entity.Cost =
                    DataConvert.ConvertTo<decimal>(rdr["Cost"],
                        default(decimal));
                entity.Price =
                    DataConvert.ConvertTo<decimal>(rdr["Price"],
                        default(decimal));
                entity.IsDiscontinued = DataConvert.ConvertTo<bool>(
                        rdr["IsDiscontinued"], default(bool));

                ret.Add(entity);
            }
        }
    }

    return ret;
}

Visual Basic
Private Function GetProducts() As List(Of Product)
    Dim cmd As SqlCommand = Nothing
    Dim ret As New List(Of Product)()
    Dim entity As Product = Nothing

    cmd = New SqlCommand("SELECT * FROM Product")
    Using cnn As SqlConnection = _
        New SqlConnection( _
            "Server=Localhost;Database=Sandbox;Integrated
                Security=Yes")
        cmd.Connection = cnn
        cmd.Connection.Open()
        Using rdr As SqlDataReader = _
            cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While rdr.Read()
                entity = New Product()

                entity.ProductId = Convert.ToInt32(rdr("ProductId"))
                entity.ProductName = rdr("ProductName").ToString()
                entity.IntroductionDate = _
                    DataConvert.ConvertTo(Of DateTime) _
                    (rdr("IntroductionDate"), DateTime.MinValue)
                entity.Cost = DataConvert.ConvertTo(Of Decimal) _
                    (rdr("Cost"), 0D)
                entity.Price = DataConvert.ConvertTo(Of Decimal) _
                    (rdr("Price"), 0D)
                entity.IsDiscontinued = _
                    DataConvert.ConvertTo(Of Boolean) _
                    (rdr("IsDiscontinued"), False)

                ret.Add(entity)
            End While
        End Using
    End Using

    Return ret
End Function

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.

Listing 5: You can use LINQ to build your collection of product objects

C#
public List<Product> GetProducts()
{
    DataTable dt = new DataTable();
    SqlDataAdapter da = null;

    da = new SqlDataAdapter("SELECT * FROM Product",
        AppSettings.Instance.ConnectString);

    da.Fill(dt);

    var query = (from dr in dt.AsEnumerable()
        select new Product
        {
            ProductId = Convert.ToInt32(dr["ProductId"]),
            ProductName = dr["ProductName"].ToString(),
            IntroductionDate =
                DataConvert.ConvertTo<DateTime>(
                dr["IntroductionDate"], default(DateTime)),
            Cost = DataConvert.ConvertTo<decimal>(
                dr["Cost"], default(decimal)),
            Price = DataConvert.ConvertTo<decimal>(
                dr["Price"], default(decimal)),
            IsDiscontinued = DataConvert.ConvertTo<bool>(
                dr["IsDiscontinued"], default(bool))
        });

        return query.ToList();
}


Visual Basic
Public Function GetProducts() As List(Of Product)
    Dim dt As New DataTable()
    Dim da As SqlDataAdapter = Nothing

    da = New SqlDataAdapter("SELECT * FROM Product", _
        AppSettings.Instance.ConnectString)

    da.Fill(dt)

    Dim query = (From dr In dt.AsEnumerable() _
                 Select New Product() With { _
    .ProductId = Convert.ToInt32(dr("ProductId")), _
    .ProductName = dr("ProductName").ToString(), _
    .IntroductionDate = DataConvert.ConvertTo(Of _
        DateTime)(dr("IntroductionDate"), DateTime.MinValue), _
    .Cost = DataConvert.ConvertTo(Of Decimal)(dr("Cost"), 0D), _
    .Price = DataConvert.ConvertTo(Of Decimal) _
                (dr("Price"), 0D), _
    .IsDiscontinued = DataConvert.ConvertTo(Of _
        Boolean)(dr("IsDiscontinued"), False) _
    })

    Return query.ToList()
End Function

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 6: Create a Product class with nullable types

C#
public partial class Product
{
    public int? ProductId { get; set; }
    public string ProductName { get; set; }
    public DateTime? IntroductionDate { get; set; }
    public decimal? Cost { get; set; }
    public decimal? Price { get; set; }
    public bool? IsDiscontinued { get; set; }
}

Visual Basic
Partial Public Class Product
    Public Property ProductId() AsNullable(Of Integer)
    Public Property ProductName() As String
    Public Property IntroductionDate() As Nullable(Of DateTime)
    Public Property Cost() As Nullable(Of Decimal)
    Public Property Price() As Nullable(Of Decimal)
    Public Property IsDiscontinued() As Nullable(Of Boolean)
End Class

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.

Listing 7: Use the Field method to load data into your nullable properties

C#
public List<Product> GetProducts()
{
    DataTable dt = new DataTable();
    SqlDataAdapter da = null;

    da = new SqlDataAdapter("SELECT * FROM Product",
                            AppSettings.Instance.ConnectString);

    da.Fill(dt);

    var query = (from dr in dt.AsEnumerable()
        select new Product
        {
            ProductId = dr.Field<int?>("ProductId"),
            ProductName = dr.Field<string>("ProductName"),
            IntroductionDate = dr.Field<DateTime?>("IntroductionDate"),
            Cost = dr.Field<decimal?>("Cost"),
            Price = dr.Field<decimal?>("Price"),
            IsDiscontinued = dr.Field<bool?>("IsDiscontinued")
        });

    return query.ToList();
}


Visual Basic
Public Function GetProducts() As List(Of Product)
    Dim dt As New DataTable()
    Dim da As SqlDataAdapter = Nothing

    da = New SqlDataAdapter("SELECT * FROM Product", _
                            AppSettings.Instance.ConnectString)

    da.Fill(dt)

    Dim query = (From dr In dt.AsEnumerable() _
        Select New Product() With { _
            .ProductId = dr.Field(Of Nullable(Of _
                Integer))("ProductId"), _
            .ProductName = dr.Field(Of String)("ProductName"), _
            .IntroductionDate = dr.Field(Of Nullable(Of _
                DateTime))("IntroductionDate"), _
            .Cost = dr.Field(Of Nullable(Of Decimal))("Cost"), _
            .Price = dr.Field(Of Nullable(Of Decimal))("Price"), _
            .IsDiscontinued = dr.Field(Of Nullable(Of _
                Boolean))("IsDiscontinued") _
        })

    Return query.ToList()
End Function

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.

Listing 8: Take advantage of reflection to reduce the amount of code you have to write

C#
public List<Product> GetProducts()
{
    SqlCommand cmd = null;
    List<Product> ret = new List<Product>();
    Product entity = null;

    // Get all the properties in Entity Class
    PropertyInfo[] props = typeof(Product).GetProperties();

    cmd = new SqlCommand("SELECT * FROM Product");
    using (cmd.Connection = new
        SqlConnection(AppSettings.Instance.ConnectString))
    {
        cmd.Connection.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                // Create new instance of Product Class
                entity = new Product();

                // Set all properties from the column names
                // NOTE: This assumes your column names are the
                //       same name as your class property names
                foreach (PropertyInfo col in props)
                {
                    if (rdr[col.Name].Equals(DBNull.Value))
                        col.SetValue(entity, null, null);
                    else
                        col.SetValue(entity, rdr[col.Name], null);
                }

                ret.Add(entity);
            }
        }
    }

    return ret;
}


Visual Basic
Public Function GetProducts() As List(Of Product)
    Dim cmd As SqlCommand = Nothing
    Dim ret As New List(Of Product)()
    Dim entity As Product = Nothing

    ' Get all the properties in Entity Class
    Dim props As PropertyInfo() = _
        GetType(Product).GetProperties()

    cmd = New SqlCommand("SELECT * FROM Product")
        Using cnn = New _
                    SqlConnection(AppSettings.Instance.ConnectString)
            cmd.Connection = cnn
            cmd.Connection.Open()
            Using rdr = cmd.ExecuteReader()
                While rdr.Read()
                    ' Create new instance of Product Class
                    entity = New Product()

                    ' Set all properties from the column names
                    ' NOTE: This assumes your column names are the
                    '       same name as your class property names
                    For Each col As PropertyInfo In props
                        If rdr(col.Name).Equals(DBNull.Value) Then
                            col.SetValue(entity, Nothing, Nothing)
                        Else
                            col.SetValue(entity, rdr(col.Name), Nothing)
                        End If
                    Next

                    ret.Add(entity)
                End While
            End Using
        End Using

        Return ret
End Function

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.

Listing 9: Use a base class and a generic method to simplify your data class code

C#
public class ManagerBase
{
    public List<T> BuildCollection<T>(Type typ, SqlDataReader rdr)
    {
        List<T> ret = new List<T>();
        T entity;

        // Get all the properties in Entity Class
        PropertyInfo[] props = typ.GetProperties();

        while (rdr.Read())
        {
            // Create new instance of Entity
            entity = Activator.CreateInstance<T>();

            // Set all properties from the column names
            // NOTE: This assumes your column names are the
            //       same name as your class property names
            foreach (PropertyInfo col in props)
            {
                if (rdr[col.Name].Equals(DBNull.Value))
                    col.SetValue(entity, null, null);
                else
                    col.SetValue(entity, rdr[col.Name], null);
            }

            ret.Add(entity);
        }

        return ret;
    }
}


Visual Basic
Public Class ManagerBase
    Public Function BuildCollection(Of T)(typ As Type, _
        rdr As SqlDataReader) As List(Of T)
        Dim ret As New List(Of T)()
        Dim entity As T

        ' Get all the properties in Entity Class
        Dim props As PropertyInfo() = typ.GetProperties()

        While rdr.Read()
        ' Create new instance of Entity
        entity = Activator.CreateInstance(Of T)()

        ' Set all properties from the column names
        ' NOTE: This assumes your column names are the
        '       same name as your class property names
        For Each col As PropertyInfo In props
            If rdr(col.Name).Equals(DBNull.Value) Then
                col.SetValue(entity, Nothing, Nothing)
            Else
                col.SetValue(entity, rdr(col.Name), Nothing)
            End If
        Next

        ret.Add(entity)
        End While

        Return ret
    End Function
End Class

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.

Listing 10: Call the base class to build your collection of Product objects

C#
public class ProductManager : ManagerBase
{
    public List<Product> GetProducts()
    {
        SqlCommand cmd = null;
        List<Product> ret = null;

        cmd = new SqlCommand("SELECT * FROM Product");
        using (cmd.Connection = new
                SqlConnection(AppSettings.Instance.ConnectString))
        {
            cmd.Connection.Open();
            using (var rdr = cmd.ExecuteReader())
            {
                // Build Collection of Entity Objets using Reflection
                ret = BuildCollection<Product>(typeof(Product), rdr);
            }
        }

        return ret;
    }
}

Visual Basic
Public Class ProductManager
    Inherits ManagerBase

    Public Function GetProducts() As List(Of Product)
        Dim cmd As SqlCommand = Nothing
        Dim ret As List(Of Product) = Nothing

        cmd = New SqlCommand("SELECT * FROM Product")
        Using cnn = New _
            SqlConnection(AppSettings.Instance.ConnectString)
            cmd.Connection = cnn
            cmd.Connection.Open()
            Using rdr = cmd.ExecuteReader()

            ' Build Collection of Entity Objets using Reflection
            ret = BuildCollection(Of Product)( _
                GetType(Product), rdr)

            End Using
        End Using

        Return ret
    End Function
End Class

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. (Editor's note, the Haystack URL no longer works.) 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.