Converting a DataSet to a Generic List

• Download demo - 729 KB

Introduction

In this article, I present a set of classes that can be used to create a generic List<T> from a specified DataSet object. This could be useful in cases where we have data for a specified entity loaded into a DataSet and need to easily construct a generic List<> of that entity type.

To demonstrate the usage, we will consider the following scenario: We have to get an Address Book application running. We are using a database for storage, and have created the following tables:

• Contact - Contains details about a contact
• Address - Contains details about an address
• ContactAddress - Maps a contact to multiple addresses

With this data model in mind, we proceed to write two stored procs, one to get all contacts in the system, and one to get the contact details given the contact ID. While listing all contacts, we only need the contact ID and the contact name to be displayed. While getting the contact details, we need all the contact details and the list of addresses the contact has.

• GetContactById - Gets details of a contact. Also gets all the addresses belonging to that contact.
SQL

CREATE PROCEDURE dbo.GetContacts
AS
    SET NOCOUNT ON
    -- Select all contacts
    SELECT     ContactId, ContactName
    FROM         Contact 
    RETURN

• GetContactById - Gets details of a contact. Also gets all the addresses belonging to that contact.
SQL

CREATE PROCEDURE dbo.GetContactById
(
    @ContactId UNIQUEIDENTIFIER
)
AS
    SET NOCOUNT ON
    -- The first result set contains the contact details
    SELECT     ContactId, ContactEmail, ContactName
    FROM         Contact
    WHERE     (ContactId = @ContactId)
    
    --The second result set contains the address details    
    SELECT     Address.AddressId, Address.AddressLine2, 
               Address.AddressLine1, Address.AddressLine3, Address.AddressType
    FROM         Address INNER JOIN
                         ContactAddress ON Address.AddressId = ContactAddress.AddressId
    WHERE     (ContactAddress.ContactId = @ContactId)

We have simple entity types defined for Contact, Address, and ContactInfo. The Citrus.Data.Core namespace includes helper classes that will allow us to easily map these entity types with the results of these Stored Procedures.

In our context, an entity is anything that represents a business object. Contact, ContactInfo, and Address are entity types. An entity type can contain other entity types (either as a singular instance or as a list). The Contact entity contains a list of Address entities.

Pseudo Logic

The core classes handle all the details of iterating through the DataTables found in the DataSet and mapping DataColumns to the entity properties. It provides functions that can then load any given entity object from a specified DataSet.

While the core classes can be used as is with your existing entity classes, you can also alter their behaviour if required by marking up the properties of your entities with Data* attributes. This is described in detail in the next section. For example, you can let the EntityLoader know that a particular property maps to a specific data column using the DataColumn attribute on that property.

The key method for the EntityLoader class is Load<T>(DataSet dataSet). This method requires the DataSet with the necessary data and the type T for which we need to create an entity list. We start the load process with the first data table in the data set:

1. Inspect the provided entity type and get a list of all its properties.
2. For every property, check if we can map the property to a specified column in the current data table. This is called a column property map.
3. Create a new list of the entity type.
4. For every data row in the current data table:
• Create an entity of type T.
• Load all simple properties (string, int, bool, double, Guid etc.) of the new entity based on the data in the current row, by using the map we created.
• For all complex properties contained in the current entity (another entity or list of entities):
• If the property is an entity, the data to be loaded would be present in the current data row itself, so get the map for the contained entity and populate it.
• If the property is an entity list, the current data table will not contain the data. If the data table is specified from where this contained entity list can be loaded, do steps 1-4 with that data table.
• Once all the properties of the entity have been loaded, add it to the entity list.
5. Return the entity list.

Complete Example with Supported Features

The download sample contains a sample website application which demonstrates the setup and usage of the EntityLoader class.

Note: The download sample contains the core assemblies, sample web application. and the standalone Address Book database. It also includes binaries off of Microsoft Enterprise Library 4.1 (for Data Access). The solution was created in VS 2008 Express Edition.

In the sample application for the Address Book, we have the following entities:

/// <summary>
/// Entity to get information about all contacts
/// </summary>
public class ContactInfo
{
    [DataColumn("ContactId")]
    public Guid Id { get; set; }

    [DataColumn("ContactName")]
    public string Name { get; set; }
}

/// <summary>
/// A simple contact entity
/// </summary>
public class Contact
{
    [DataColumn("ContactId")]
    public Guid Id { get; set; }

    [DataColumn("ContactName")]
    public string Name { get; set; }

    /// <summary>
    /// Note that email address has a private set
    /// EntityLoader will not be able to load the
    /// email address in this case
    /// </summary>
    [DataColumn("ContactEmail")]
    public string EmailAddress { get; private set; }

    /// <summary>
    /// The list of addresses if from the next table
    /// </summary>
    [DataTable(DataTableSource.Next)]
    public List<Address> Addresses { get; set; }
}

/// <summary>
/// In this entity we use implicit column mapping
/// EntityLoader will map the column names from the data
/// set against the property names
/// </summary>
public class Address
{
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string AddressLine3 { get; set; }
    public string AddressLine4 { get; set; }

    /// <summary>
    /// This is an example of custom data conversion
    /// The address type is stored in the database as
    /// an int, but the entity uses a string form
    /// </summary>
    [DataConverter(typeof(AddressTypeConverter))]
    public string AddressType { get; set; }
}

The three classes show various combinations of usage of the allowed attributes. In the ContactInfo class, we use the DataColumn attribute to specify which column a property should map to explicitly. In the Contact class, we use the DataTable attribute to let the EntityLoader know that the List of Address data exists in another data table (in this case, the next one). In the Address entity class, we see an example of implicit column mapping based on the property name. We also use the DataConverter attribute to specify that a property needs extra work to be populated - the AddressType is stored in the database as an int, but the Address entity has a string value for it. The implementation of the AddressTypeConverter class is shown below:

/// <summary>
/// This is a converter class that allows the address type
/// property of the address entity to be loaded
/// </summary>
public class AddressTypeConverter : DataConverterBase<string, int>
{
    public override string GetEntity(int entityData)
    {
        switch (entityData)
        {
            case 1: return "Home";
            case 2: return "Office";
            default: return "Unknown";
        }
    }

    public override int GetEntityData(string entity)
    {
        switch (entity)
        {
            case "Home": return 1;
            case "Office": return 2;
            default: return -1;
        }
    }
}

These attributes help in establishing the mapping between data columns and entity properties.

Data Access Layer

The sample application has a simple data access layer built on top of the Microsoft Enterprise Library 4.1 Data Access Application Block.

The data access layer is defined as:

/// <summary>
/// The simple data access layer
/// </summary>
public static class AddressBook

and has a static constructor like so:

/// <summary>
/// The static contructor for our data access object
/// initializes the entity definition provider and adds
/// all the entity types that make up our data model
/// </summary>
static AddressBook()
{
    CachedEntityDefinitionProvider addressBookEntityProvider = 
                            new CachedEntityDefinitionProvider();

    addressBookEntityProvider.AddEntityType(typeof(ContactInfo));
    addressBookEntityProvider.AddEntityType(typeof(Contact));
    addressBookEntityProvider.AddEntityType(typeof(Address));
    
    // Set our custom provider to the inspector
    EntityInspector.EntityProvider = addressBookEntityProvider;
}

The CachedEntityDefinitionProvider is a simple extension class that is used to tell the entity loading system which types are entity types in the system. In our example, we specify that Contact, ContactInfo, and Address types are entity types. You can create your own version of an entity definition provider and hook it up with the EntityInspector's EntityProvider property by implementing the IEntityDefinitionProvider interface.

The core method that utilizes EntityLoader is implemented as an extension method on the Database type as follows:

/// <summary>
/// Simple extension for Database type to call EntityLoader Load method
/// </summary>
static List<T> LoadEntity<T>(this Database db, 
       string commandName, params object[] commandArguments)
{
    return EntityLoader.Load<T>(db.ExecuteDataSet(commandName, commandArguments));
}

You can change this in the sample if you do not have EL4.1.

And finally, the data access methods:

public static List<ContactInfo> GetContacts()
{
    return DbContext.LoadEntity<ContactInfo>(StoredProcedures.GetContacts);
}

public static List<Contact> GetContactById(Guid id)
{
    return DbContext.LoadEntity<Contact>(StoredProcedures.GetContactsById, id);
}

Note that all methods return a list of the specified entity.

Data Attributes

This section enumerates all the attributes available for use:

• DataColumn - Applied on a property. Specifies which data column in a data table maps to this property.
• DataTable - Applied on a list<> property. Specifies the source data table from where the list should be populated. Use as DataTable(DataTableSource.Next) or DataTable(2) to specify the relative table position.
• DataConverter - Applied to a property. Tells the EntityLoader system that a specific data converter class needs to be used to load the property data.
• EntityLoader - Applied to the entity class. This controls the way in which properties are checked to be loaded with data from a DataSet. By default, all properties of a given entity will be used for column mapping and data load. You can change this behaviour by specifying an InspectionPolicy parameter to the EntityLoader attribute. Possible uses are EntityLoader(InspectionPolicy.OptIn) or EntityLoader(InspectionPolicy.OptOut). If OptIn is specified, you need to mark properties explicitly with the DataInclude attribute to ensure that EntityLoader will use that property. If OptOut is specified, all properties will be inspected, unless marked with DataExclude. OptOut is the default behaviour.

Improvements

Of course, this is just a very early implementation. There are lots more to do in terms of argument validation, better exception handling, and so on.

Also included in the Citrus.Data.Core assembly is a utility class that can do the opposite thing of what we have seen here, convert a List<T> to a DataSet.. It's not complete yet, but works for simple entities (ones that do not contain properties that are lists of entities).

In case you can suggest some, please do.

References

Where possible, I have included references in the code itself. Some to look at are:

• http://geekswithblogs.net/Madman/archive/2008/06/27/faster-reflection-using-expression-trees.aspx
• http://thecodeslinger.wordpress.com/2007/12/03/dynamic-object-instantiation-performance-part-ii/