Click here to Skip to main content
13,247,494 members (64,158 online)
Click here to Skip to main content
Articles » Languages » C# » Utilities » Revisions
Add your own
alternative version


141 bookmarked
Posted 7 Feb 2012

Dynamically Map SQL Resultset to Business Object

, 5 Mar 2012
Rate this:
Please Sign up or sign in to vote.
Dynamically map a DataTable to type-safe business objects using reflection and generics.
This is an old version of the currently published article.


Comments and votes are always appreciated!

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.


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 4 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
        return _StudentID;
        IsStudentIDSet = true;

public bool IsStudentIDSet { get; set; }

#region StudentName
private String _StudentName;

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

public bool IsStudentNameSet { get; set; }

#region SchoolID
private int? _SchoolID;

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

public bool IsSchoolIDSet { get; set; }


#region TeacherID
private int? _TeacherID;

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

public bool IsTeacherIDSet { get; set; }

#region TeacherName
private String _TeacherName;

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

public bool IsTeacherNameSet { get; set; }

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 3 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;

[DynamicClass(typeof(School), "SchoolID")]
public School RelatedSchool
        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;


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");


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";


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.


  • 2012-02-07 - Initial Posting


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sam Lombardo
Software Developer (Senior)
Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

Discussions on this specific version of this article. Add your comments on how to improve this article here. These comments will not be visible on the final published version of this article.
QuestionCompared to Dapper? Pin
pt14012-Dec-15 5:49
memberpt14012-Dec-15 5:49 
AnswerRe: Compared to Dapper? Pin
Sam Lombardo14-Jan-16 18:51
memberSam Lombardo14-Jan-16 18:51 
GeneralThoughts Pin
PIEBALDconsult7-Jul-14 8:26
memberPIEBALDconsult7-Jul-14 8:26 
GeneralRe: Thoughts Pin
Sam Lombardo7-Jul-14 10:07
memberSam Lombardo7-Jul-14 10:07 
GeneralRe: Thoughts Pin
PIEBALDconsult7-Jul-14 10:52
memberPIEBALDconsult7-Jul-14 10:52 
GeneralRe: Thoughts Pin
Sam Lombardo7-Jul-14 11:25
memberSam Lombardo7-Jul-14 11:25 
GeneralRe: Thoughts Pin
Sam Lombardo10-Jul-14 18:06
memberSam Lombardo10-Jul-14 18:06 
GeneralMy vote of 4 Pin
Joshua Hightower21-Feb-13 7:14
memberJoshua Hightower21-Feb-13 7:14 
GeneralMy vote of 5 Pin
Rutuanie20-Feb-13 22:04
memberRutuanie20-Feb-13 22:04 
GeneralRe: My vote of 5 Pin
Sam Lombardo21-Feb-13 4:54
memberSam Lombardo21-Feb-13 4:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 5 Mar 2012
Article Copyright 2012 by Sam Lombardo
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid