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>();
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; 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 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.
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