Click here to Skip to main content
11,496,146 members (697 online)
Click here to Skip to main content

Dynamically Map SQL Resultset to Business Object

, 8 Jul 2014 Eclipse 78.7K 2.2K 143
Dynamically map a DataTable to type-safe business objects using reflection and generics
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.

UPDATE

This project is now available on NuGet and GitHub! Feel free to contribute any ideas or enhancements on GitHub and I will be sure to review them. To find it in NuGet, a simple search for "AweSamNet" Or "DynamicClasses" should be good enough.

Introduction

Comments and votes are always appreciated! 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 AweSamNet.Data.DynamicClasses.dll in your project references. Once this is done, be sure to add a:

using AweSamNet.Data.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 AweSamNet.Data.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 its 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.

History

  • 2012-02-07 - Initial posting
  • 2012-09-28 - Added new methods to return arrays of attributes for a given property
  • 2014-07-07
    • Added AweSam.Net branding
    • Added Nuget package link
    • Added GitHub project link

License

This article, along with any associated source code and files, is licensed under The Eclipse Public License 1.0

Share

About the Author

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

Comments and Discussions

 
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 
GeneralMy vote of 5 Pin
tikkababy17-Dec-12 8:37
membertikkababy17-Dec-12 8:37 
GeneralRe: My vote of 5 Pin
Sam Lombardo17-Dec-12 9:25
memberSam Lombardo17-Dec-12 9:25 
QuestionDoes it work with LINQ2SQL and Entity Framework Pin
Tridip Bhattacharjee2-Oct-12 7:10
memberTridip Bhattacharjee2-Oct-12 7:10 
AnswerRe: Does it work with LINQ2SQL and Entity Framework Pin
Sam Lombardo2-Oct-12 9:08
memberSam Lombardo2-Oct-12 9:08 
AnswerRe: Does it work with LINQ2SQL and Entity Framework Pin
PIEBALDconsult8-Jul-14 6:47
memberPIEBALDconsult8-Jul-14 6:47 
GeneralMy vote of 5 Pin
Daniel Cruz27-Sep-12 13:08
memberDaniel Cruz27-Sep-12 13:08 
GeneralRe: My vote of 5 Pin
Sam Lombardo28-Sep-12 7:44
memberSam Lombardo28-Sep-12 7:44 
GeneralMy vote of 5 Pin
Eric Lapouge25-Sep-12 15:11
memberEric Lapouge25-Sep-12 15:11 
GeneralRe: My vote of 5 Pin
Sam Lombardo26-Sep-12 5:59
memberSam Lombardo26-Sep-12 5:59 
GeneralRe: My vote of 5 Pin
Eric Lapouge26-Sep-12 8:58
memberEric Lapouge26-Sep-12 8:58 
GeneralRe: My vote of 5 Pin
andegre21-Feb-13 8:25
memberandegre21-Feb-13 8:25 
GeneralRe: My vote of 5 Pin
Eric Lapouge21-Feb-13 22:45
memberEric Lapouge21-Feb-13 22:45 
GeneralRe: My vote of 5 Pin
Eric Lapouge20-May-13 1:06
memberEric Lapouge20-May-13 1:06 
QuestionWhat about performance? Pin
Kausik J. Nag10-Jun-12 7:07
memberKausik J. Nag10-Jun-12 7:07 
AnswerRe: What about performance? Pin
Sam Lombardo11-Jun-12 5:42
memberSam Lombardo11-Jun-12 5:42 
AnswerRe: What about performance? Pin
Kelqualyn8-Oct-12 0:05
memberKelqualyn8-Oct-12 0:05 
GeneralRe: What about performance? Pin
PIEBALDconsult8-Jul-14 6:58
memberPIEBALDconsult8-Jul-14 6:58 
GeneralRe: What about performance? Pin
Sam Lombardo8-Jul-14 7:09
memberSam Lombardo8-Jul-14 7:09 
SuggestionMy vote of 5 + IEnumerable support Pin
Akram El Assas8-Jun-12 15:15
memberAkram El Assas8-Jun-12 15:15 
GeneralRe: My vote of 5 + IEnumerable support Pin
Sam Lombardo11-Jun-12 5:27
memberSam Lombardo11-Jun-12 5:27 
GeneralMy vote of 5 Pin
JF20157-Jun-12 18:47
memberJF20157-Jun-12 18:47 
GeneralRe: My vote of 5 Pin
Sam Lombardo8-Jun-12 7:09
memberSam Lombardo8-Jun-12 7:09 
SuggestionExactly what I was looking for! Pin
Andrew Hanigan28-Mar-12 5:52
memberAndrew Hanigan28-Mar-12 5:52 
GeneralRe: Exactly what I was looking for! Pin
Sam Lombardo29-Mar-12 5:07
memberSam Lombardo29-Mar-12 5:07 
GeneralRe: Exactly what I was looking for! Pin
Akram El Assas8-Jun-12 15:18
memberAkram El Assas8-Jun-12 15:18 
GeneralMy vote of 5 Pin
Naerling27-Feb-12 8:44
memberNaerling27-Feb-12 8:44 
GeneralRe: My vote of 5 Pin
Sam Lombardo27-Feb-12 11:52
memberSam Lombardo27-Feb-12 11:52 
GeneralMy vote of 5 Pin
Sachin_coder22-Feb-12 20:23
memberSachin_coder22-Feb-12 20:23 
GeneralRe: My vote of 5 Pin
Sam Lombardo27-Feb-12 6:44
memberSam Lombardo27-Feb-12 6:44 
GeneralMy vote of 5 Pin
Dean Oliver22-Feb-12 9:39
memberDean Oliver22-Feb-12 9:39 
GeneralRe: My vote of 5 Pin
Sam Lombardo27-Feb-12 6:44
memberSam Lombardo27-Feb-12 6:44 
GeneralMy vote of 5 Pin
Member 866545521-Feb-12 7:10
memberMember 866545521-Feb-12 7:10 
GeneralRe: My vote of 5 Pin
Sam Lombardo21-Feb-12 7:33
memberSam Lombardo21-Feb-12 7:33 
AnswerExcelente Pin
craliaga17-Feb-12 9:41
membercraliaga17-Feb-12 9:41 
GeneralRe: Excelente Pin
Sam Lombardo20-Feb-12 5:01
memberSam Lombardo20-Feb-12 5:01 
GeneralMy vote of 5 Pin
Dean Oliver9-Feb-12 20:34
memberDean Oliver9-Feb-12 20:34 
GeneralRe: My vote of 5 Pin
Sam Lombardo14-Feb-12 8:58
memberSam Lombardo14-Feb-12 8:58 
QuestionMy Vote 5 Pin
infobeena9-Feb-12 0:46
memberinfobeena9-Feb-12 0:46 
AnswerRe: My Vote 5 Pin
Sam Lombardo9-Feb-12 6:24
memberSam Lombardo9-Feb-12 6:24 
GeneralMy vote of 5 Pin
tigercont7-Feb-12 21:52
membertigercont7-Feb-12 21:52 
GeneralRe: My vote of 5 Pin
Sam Lombardo9-Feb-12 6:24
memberSam Lombardo9-Feb-12 6:24 
GeneralMy vote of 5 Pin
mariazingzing7-Feb-12 12:52
membermariazingzing7-Feb-12 12:52 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 8 Jul 2014
Article Copyright 2012 by Sam Lombardo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid