Dynamically Map SQL Resultset to Business Object

Introduction

Comments and votes are always appreciated! Also help AweSam.Net in supporting struggling Non-Profit Organizations and charities by joining our network. AweSam.Net is a supporter of Open Source Software so look for many great .NET libraries to come! Follow us on twitter at @AweSamNet or on Facebook.

A common issue in our company is working with non-type-safe data. Our DBA writes very efficient stored procedures which often join with related tables to reduce DB calls. Our developers must in turn cycle through the result sets and manually map each column to an appropriate class and property. The issue is the amount of redundant code and room for human error. For example, there may be 3 different stored procedures that all return rows of the School table:

A Stored Procedure called to get a school
A Stored Procedure to get a student with the linked school information
A Stored Procedure to get a teacher with the linked school information

For each of these stored procedures a coder might write a method to map the result sets to their corresponding classes.

The coder must also ensure that he is properly casting and handle any potential cast exceptions each time. This leaves more responsibility in the hands of the developer than most developers want.

The DynamicResultSet library allows you to use the data returned from one stored procedure to populate any number of DynamicClasses. For example, a stored procedure that returns students linked to teachers linked to schools would be processed as such:

DynamicResultSet drs = new DynamicResultSet(myDataTable);


List<Student> students = drs.GetList<Student>();
List<Teacher> teachers = drs.GetList<Teacher>();
List<School> schools = drs.GetList<School>();

//or access the School directly from the properties:
School myScool = students[0].Teacher.School;

Through Reflection, we loop through the class' properties and read all mappings indicated in the attributes of this property. We then make sure the column exists in the table and if so, we do our casting and populate the property based on the result.

Using the code

Before using this code, it is recommended that you install the C# snippets included.

The snippets to install are:

DynamicClassSnippet.snippet
DynamicPropertySnippet.snippet

The first task is to include the Utilities.DynamicClasses.dll in your project references. Once this is done, be sure to add a:

using Utilities.DynamicClasses;
using System.Data;

to your .cs file.

Now we are ready to create our first class. All classes generated by a DynamicResultSet must inherit the Utilities.DynamicClasses.BusinessLogicBase.

Overrides

By inheriting BusinessLogicBase, you must override three functions.

OnLoaded() - this function is executed by the DynamicResultSet once the row has been fully parsed into the new class. Any variables that must be set or methods that must be executed once all the data is loaded should be done in this method.
IsEmpty() - this function is what tells the DynamicResultSet to ignore a record or to return it in the GetList(). For example, a stored procedure may be executing an Outer Join in which case it is very possible that all values of a linked table are null. In this case, you would use IsEmpty() to return true if certain ID fields are not included in a record.
ToString() - This field is simply to force our developers to properly set the ToString() so that the BusinessLogicBase default comparers can be properly used for sorting.

DynamicProperty Attribute

If you have the snippets installed, the quickest way for us to generate a DynamicProperty (a property that is dynamically mapped to a DataTable column), is to type the snippet shortcut "dynamicProperty" and press tab. Once the Dynamic property is generated, you can tab through the snippet value placeholders to change them. There are 4 values you need to update:

Name - this is the property name. E.g., StudentID
Type - this is the data type of this property. This must be a nullable type to properly represent a value that might be returned from a data source. E.g., int?
ColumnName - this is the name of the column in the datatable that will be mapped to this property
SqlDbType - this is the datatype that is expected (or as close as possible) to be returned from the data source.

Once all four variables have been properly set, press enter and you will be left with something that will resemble the following if we were creating a Student class with a StudentID, StudentName, SchoolID and a TeacherID:

#region StudentID
private int? _StudentID;

[DynamicProperty(ColumnName = "st_studentId", DatabaseType = SqlDbType.Int)]
public int? StudentID
{
    get
    {
        return _StudentID;
    }
    set
    {
        IsStudentIDSet = true;
    }
}


public bool IsStudentIDSet { get; set; }
#endregion

#region StudentName
private String _StudentName;

[DynamicProperty(ColumnName = "st_studentName", DatabaseType = SqlDbType.NVarChar)]
public String StudentName
{
    get
    {
        return _StudentName;
    }
    set
    {
        IsStudentNameSet = true;
    }
}


public bool IsStudentNameSet { get; set; }
#endregion

#region SchoolID
private int? _SchoolID;

[DynamicProperty(ColumnName = "st_schoolId", DatabaseType = SqlDbType.Int)]
public int? SchoolID
{
    get
    {
        return _SchoolID;
    }
    set
    {
        IsSchoolIDSet = true;
    }
}


public bool IsSchoolIDSet { get; set; }
#endregion

#region TeacherID
private int? _TeacherID;

[DynamicProperty(ColumnName = "st_teacherId", DatabaseType = SqlDbType.Int)]
public int? TeacherID
{
    get
    {
        return _TeacherID;
    }
    set
    {
        IsTeacherIDSet = true;
    }
}

public bool IsTeacherIDSet { get; set; }
#endregion

#region TeacherName
private String _TeacherName;

[DynamicProperty(ColumnName = "st_teacherName", DatabaseType = SqlDbType.Int)]
public String TeacherName
{
    get
    {
        return _TeacherName;
    }
    set
    {
        IsTeacherNameSet = true;
    }
}


public bool IsTeacherNameSet { get; set; }
#endregion

DynamicClass Attribute

A DynamicClass property is another type that inherits BusinessLogicBase that is included in the current class as a property. So for example working with our above Student class, a stored procedure may return a school that is joined to the student record on the same row of the student data. In this case we have enough data to populate the student and it's related school. In this case we would include a Student.RelatedSchool property and would add the DynamicClass attribute to tell the DynamicResultSet to parse the school data into the Student.RelatedSchool property.

Creating a DynamicClass property of a class is very similar to creating a DynamicProperty when using the dynamicClass snippet. Type "dynamicClass" and press tab. Once the Dynamic Class is generated, you can tab through the snippet value placeholders to change them. There are 3 values you need to update:

Name - this is the property name. E.g. RelatedSchool
Type - this is the data type of this property. This must be a type that inherits from BusinessLogicBase. E.g. School
IDFieldName - this is the field that links the DynamicClass property to the class we are creating. E.g. SchoolID

Once all three variables have been properly set, press enter and you will be left with something that will resemble the following if we were creating Student.RelatedSchool property that is linked by the above SchoolID property:

#region RelatedSchool
private School _RelatedSchool;

[ScriptIgnore]
[DynamicClass(typeof(School), "SchoolID")]
public School RelatedSchool
{
    get
    {
        return _RelatedSchool;
    }
    private set
    {
        _RelatedSchool = value; // Set cached variable to the value
        if (value == null) // If it's null set the identifier to null
            this.SchoolID = null;
        else // Otherwise set the identifier to the value's ID
            this.SchoolID = value.ID;
    }
}
#endregion

DynamicResultSet

Now that we have our classes defined, it's time to see how we connect our data to them. The DynamicResultSet constructor accepts a DataTable as a parameter. For the sake of this demonstration we will manually create a DataTable instead of getting it from a stored procedure.

DataTable table = new DataTable("Students");
table.Columns.Add("st_studentId");
table.Columns.Add("st_schoolId");
table.Columns.Add("st_teacherId");

table.Columns.Add("sch_schoolId");
table.Columns.Add("sch_schoolName");


DataRow row = table.NewRow();
row["st_studentId"] = 1;
row["st_studentName"] = "Sam Jr.";
row["st_schoolId"] = 23;
row["st_teacherId"] = 54;

row["te_teacherId"] = 54;
row["te_teacherName"] = "Mr. Smith";

row["sch_schoolId"] = 23;
row["sch_schoolName"] = "Pleasantview High School";

DataRow row2 = table.NewRow();
row2["st_studentId"] = 1;
row2["st_schoolId"] = 232;
row2["st_teacherId"] = 86;

row2["sch_schoolId"] = 232;
row2["sch_schoolName"] = "Colonel Saunders Military High School";

table.Rows.Add(row);
table.Rows.Add(row2);

Now that we have our DataTable, we can pump it into the DynamicResultSet and generate our objects. Notice how above we declared a Student.TeacherName property, however we did not include the appropriate column in the DataTable. The DynamicResultSet will not break if there are columns that are not expected or if there are columns that are null or excluded. Assuming we have created both the School class and the Teacher class, let's take a look at how the DynamicResultSet works.

DynamicResultSet drs = new DynamicResultSet(table);

List<Student> students = drs.GetList<Student>();
List<Teacher> teachers = drs.GetList<Teacher>();
List<School> schools = drs.GetList<School>();

//or access the School directly from the properties:
School myScool = students[0].RelatedSchool;

What we've done is populate the table into a new DynamicResultSet. We then use the generic method GetList<T>() to parse the passed datatable and return all possible objects of the generic type.

Notice that on the last line, we are able to access the linked school directly by accessing the RelatedSchool property (which as we remember above, has the DynamicClass attribute). This is because the DynamicResultSet recognized the attribute and used the DataRow to populate the RelatedSchool property as best as it could.

Is[property name]Set

If you notice the code generated for a DynamicProperty, it creates a second boolean property associated to the main property to store the proper value. This property is the Is[PropertyName]Set property. This property is what lets you know that this column was or was not returned from the stored procedure. In our above case, we declared a property called Student.TeacherName, however we did not add the associated column to the DataTable. Since the Student.TeacherName property was never set, the Student.IsTeacherNameSet will be false.

Update:

Upon request, I have added some new features for convenience.

GetPropertyAttributes

The method:

public static T[] GetPropertyAttributes<T>(this object source,  String propertyName) where T : Attribute

has been added as an extension method for all classes that derive from object. It returns an array of type T where T is a System.Attribute.

BusinessLogicBase.GetDynamicPropertyAttributes

The method:

public DynamicProperty[] GetDynamicPropertyAttributes(String propertyName)

has been added to the class BusinessLogicBase. This method uses the generic method GetPropertyAttributes to return an array of DynamicProperty for a given property name.

BusinessLogicBase.GetDynamicClassAttributes

The method:

public DynamicProperty[] GetDynamicClassAttributes(String propertyName)

has been added to the class BusinessLogicBase. This method uses the generic method GetPropertyAttributes to return an array of DynamicClass for a given property name.