Manual ORM via ADO.Net when other platforms aren't available
I wrote this article before I had heard of or come across the excellent Dapper library. Nowadays I highly recommend that. :)
Sometimes you come across situations where it is inappropriate to use a higher-level ORM utility such as EntityFramework, NHibernate or others. There are many very good situations in which these tools can be used, and this article is not an evaluation of them nor does it prescribe situations in which they should not be used. Rather, if you find yourself in a situation where this occurs you may still want to make your database access work with your strongly-typed classes using ADO.Net. With a little bit of “helper” code, you can make this a snap.
This post will show how to turn ADO.Net DataTables and DataSets into strongly typed custom .Net classes using a little bit of reflection. I will focus on the ADO.Net methods that return a result set of some sort or another. The starting point for my approach (and the initial idea) came from an article I read in CODE Magazine by Paul D Sheriff titled Creating Collections of Entity Objects.
You may have come across information that states that reflection is slow and can cause performance problems. I work with a fairly large-scale enterprise application and we use code like this quite a bit without any real perceived pain. Unless you are dealing with very heavy loads running through code like this, you probably won’t even notice a difference between it and more traditionally hard-coded ADO.NET DataTable work. A general rule for me is to “program it easily first and evaluate performance when needed.” (see Rule 8 of my Guiding Principles).
For this discussion, I will start with an example and then expand it to additional cases.
The example is that I want to execute a stored procedure and have its result set returned as a strongly type list to a calling method. For simplicity I’ll just be discussing from the standard AdventureWorks database.
Here is a custom stored proc that I’ll start with:
1CREATE PROCEDURE [Person].[spGetSomeContacts]
2AS
3SELECT TOP 100
4 c.ContactID
5 ,c.FirstName
6 ,c.LastName
7 ,c.EmailAddress
8 ,c.Phone
9 ,c.ModifiedDate
10FROM Person.Contact c
This is admittedly a simple and not-likely-production proc, but it will serve our example just fine. So now (again, just as an example), let’s say we have a C# class that looks something like this:
1public class CustomContact
2{
3 public int ContactId { get; set; }
4 public string FirstName { get; set; }
5 public string LastName { get; set; }
6 public string EmailAddress { get; set; }
7 public DateTime ModifiedDate { get; set; }
8 public bool IsBrandNew { get; set; }
9}
Couple of key points to note here:
- The
Phonefield selected in the stored proc doesn’t seem to have a corresponding property on the class. - The
IsBrandNewproperty on the class doesn’t seem to have a corresponding field that will be coming from the database. - All of the other properties match up by name (case-insensitive) between the stored proc and the class.
In order for us to leverage ADO.Net and get this data into our class, we will use its SqlDataAdapter.Fill method which gives us a DataSet with one or more DataTables in it.
The first step in our process involves creating a couple of simple overloads — one which can return a DataSet and one which can return a DataTable. See the overloads below.
1ublic void Execute(out DataSet resultSet, out int affectedRows)
2{
3 resultSet = new DataSet();
4 affectedRows = 0;
5
6 try
7 {
8 using (var sqlDataAdapter = new SqlDataAdapter(Command))
9 {
10 affectedRows = sqlDataAdapter.Fill(resultSet);
11 }
12 }
13 catch (SqlException sqlErr)
14 {
15 throw new StoredProcException("SQL Exception occurred!!", Command.CommandText, InputsString, sqlErr);
16 }
17 catch (Exception ex) // some other kind of exception occurred!!
18 {
19 throw new StoredProcException("General Exception occurred!!", Command.CommandText, InputsString, ex);
20 }
21}
22
23public void Execute(out DataTable resultSet)
24{
25 DataSet ds;
26 int affectedRows;
27
28 Execute(out ds, out affectedRows);
29 resultSet = ds.Tables[0];
30 ds.Dispose();
31}
Now that let’s write a method that can take a DataTable and return a list of a generic type — this is the meat of everything that will follow.
1public class CollectionHelper
2{
3 public static List<T> BuildCollection<T>(Type type, DataTable table)
4 {
5 var ret = new List<T>();
6
7 var props = type.GetProperties();
8
9 foreach (DataRow row in table.Rows)
10 {
11 var entity = Activator.CreateInstance<T>();
12
13 //Set all properties from the column names
14 //NOTE: This assumes your column names are the same name as your class property names
15 foreach (var col in props)
16 {
17 if (!table.Columns.Contains(col.Name))
18 continue; // nothing to do with this column
19
20 try
21 {
22 if (!col.PropertyType.FullName.StartsWith("System"))
23 {
24 DeserializeColumnOutputIntoCustomType(row, col, entity);
25 continue;
26 }
27
28 if (row[col.Name].Equals(DBNull.Value))
29 {
30 col.SetValue(entity, null);
31 continue;
32 }
33
34 if (col.PropertyType == typeof (bool))
35 {
36 col.SetValue(entity, (row[col.Name].ToString() == "1" || row[col.Name].ToString() == bool.TrueString));
37 continue;
38 }
39
40 if (IsTypeNumeric(col.PropertyType))
41 {
42 col.SetValue(entity, string.IsNullOrEmpty(row[col.Name].ToString()) ? 0 : Convert.ChangeType(row[col.Name].ToString(), col.PropertyType), null);
43 continue;
44 }
45
46 if (col.PropertyType.Name.StartsWith("Nullable"))
47 {
48 var colType = Nullable.GetUnderlyingType(col.PropertyType);
49 if (IsTypeNumeric(colType))
50 {
51 col.SetValue(entity, Convert.ChangeType(row[col.Name].ToString(), colType), null);
52 continue;
53 }
54
55 if (colType == typeof (bool))
56 {
57 col.SetValue(entity, row[col.Name].ToString() == "1");
58 continue;
59 }
60
61 //non-numeric, non-bool nullables
62 col.SetValue(entity, Convert.ChangeType(row[col.Name].ToString(), Nullable.GetUnderlyingType(col.PropertyType)), null);
63 continue;
64 }
65
66 col.SetValue(entity, col.PropertyType.IsEnum ? Enum.ToObject(col.PropertyType, Convert.ToInt32(row[col.Name].ToString()))
67 : Convert.ChangeType(row[col.Name].ToString(), col.PropertyType), null);
68
69 }
70 catch (Exception ex)
71 {
72 throw new Exception(string.Format("Failed building collection. Setting Property{0} to value {1}", col.Name, row[col.Name]), ex);
73 }
74 }
75
76 ret.Add(entity);
77 }
78
79 return ret;
80 }
81
82 private static bool IsTypeNumeric(Type type)
83 {
84 return type == typeof(int) || type == typeof(short) || type == typeof(long) || type == typeof(double) || type == typeof(decimal) || type == typeof(float);
85 }
86
87 private static void DeserializeColumnOutputIntoCustomType<T>(DataRow row, PropertyInfo col, T entity)
88 {
89 if (string.IsNullOrEmpty(row[col.Name].ToString()))
90 return; // nothing to deserialize
91
92 var outputType = col.PropertyType.UnderlyingSystemType;
93 var handler = new XmlSerializer(outputType);
94 using (var reader = new StringReader(row[col.Name].ToString()))
95 col.SetValue(entity, handler.Deserialize(reader));
96
97 }
98}
It’s definitely worth explaining some of the above method a bit. The first things the method does is to create an empty list of type T and get
the list of properties on type T (in the props variable). Then in loops through the DataTable’s rows and creates a new T to add to the list. At this
point it loops through the properties on the object to see if the DataTable contains a column with the same name as the current property. If it doesn’t,
it simply moves on to the next property. If it does, we set the value of the current T’s property to the value of the data column. Note that there is
different logic for different C# types, as well as some specific handling for nullable types and null values. Of additional note is that if
you have a complex type as one of your properties and that is returned by a stored procedure as xml text, this function will
attempt to deserialize the object and set the property.
Now that we have a collection builder, now we can add an overload to our stored proc wrapper that will execute the proc and return us a strongly-typed generic list.
1public void Execute<T>(out List<T> outList)
2{
3 DataTable dt;
4 Execute(out dt);
5 outList = CollectionHelper.BuildCollection<T>(typeof (T), dt);
6}
So now the application code that calls to get a list of CustomContacts (our initial example case) becomes very simple:
1List<CustomContact> contactList;
2exsp.Execute(out contactList);
3// "exsp" is actually an instance of the stored proc wrapper class that exposes the "Execute" overloads above.
Note that we can use the exact same technique for any stored proc returning a list of objects now. Sweet!!!
To go further we can add a couple of additional overloads which may help us.
- Get a single object
1public void Execute<T>(out T output, Tid myTid = null)
2{
3 List<T> tempList;
4 Execute(out tempList);
5 return tempList.FirstOrDefault();
6}
- Updating the properties on an already-instantiated object. This is a little trickier, but is relatively simple using the approach laid out above. Stay tuned for a more complete discussion of the entire stored proc wrapper — which will include the exception handling hinted at by the code above as well as handling of input parameters. 🙂