Introduction
Remember, if you like this article, please rate it and feel free to leave any comments!
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.
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>();
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. StudentIDType
- 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 propertySqlDbType
- 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
{
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. RelatedSchoolType
- this is the data type of this property. This must be a type that inherits from BusinessLogicBase. e.g. SchoolIDFieldName
- 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;
[ScriptIgnore]
[DynamicClass(typeof(School), "SchoolID")]
public School RelatedSchool
{
get
{
return _RelatedSchool;
}
private set
{
_RelatedSchool = value;
if (value == null)
this.SchoolID = null;
else
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>();
School myScool = students[0].RelatedSchool;
What we've done is populated 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. Basically, 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.
History
- 2012-02-07 - Initial Posting
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.