Something clicked in my head a few weeks ago, and ever since, I’ve found myself using more and more functional programming techniques. Even with C# 2.0, there are a lot of things you can do to make your code cleaner and easier to read. I recently came up with an interesting pattern for database access using ADO.NET, so I thought I’d share it. Essentially, it’s a way to create a quick-and-dirty object/relational mapper via a simple pattern rather than by using a framework.
First, a disclaimer: I highly recommend that, instead of using this pattern, you look into a real ORM framework like NHibernate. The only reason I’m using this at all is because I developed it for the .NET compact framework, and as such, I don’t have the luxury of real ORM. If you prefer doing data access by hand, this pattern can help structuralize it, though!
Also, if you looked at the length of this post and your tl;dr alarm went off, don’t worry, the bulk is mostly code, and the payoff is worth it. :)
First, let’s consider a simple database table:
1: create table Users (
2: UserName varchar(20) primary key,
3: FirstName varchar(50) not null,
4: LastName varchar(50) not null,
5: Email varchar(100) null
6: );
We could easily represent this as an entity in our code using the following simple type (property get/set methods are omitted for brevity):
1: class User {
2: private string _userName;
3: private string _firstName;
4: private string _lastName;
5: private string _email;
6:
7: public string UserName { ... }
8: public string FirstName { ... }
9: public string LastName { ... }
10: public string Email { ... }
11: }
This is a common pattern that’s been around for a long time. When you want to load a user from the database, you run a SELECT statement, and use an IDataReader to load the information from the query result, and populate the User object:
1: public User GetUser(string userName)
2: {
3: string sql = "select UserName, FirstName, LastName, Email "
4: + "from Users where UserName = @UserName";
5:
6: using (SqlConnection connection = new SqlConnection(...))
7: using (SqlCommand command = new SqlCommand(sql, connection))
8: {
9: command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
10: using (SqlDataReader reader = command.ExecuteReader())
11: {
12: if (!reader.Read())
13: return null;
14:
15: User user = new User();
16: user.UserName = reader.GetString(0);
17: user.FirstName = reader.GetString(1);
18: user.LastName = reader.GetString(2);
19: user.Email = reader.GetString(3);
20:
21: return user;
22: }
23: }
24: }
This works just fine, but there are a couple of problems. First, our data retrieval code is coupled to the code that populates the object (the “ORM” code). This means that if we wanted to add a method that returned a list of all users with a given last name, we would have to duplicate the ORM code in the other method. Second, there’s a hidden bug in the ORM code… the Email column is nullable, but we’re not checking it with IsDBNull before we call GetString. This is easily fixed, but if you’ve duplicated the code in your search method, you have to be sure to update all copies of it.
This is an example of a violation of the Single Responsibility Principle (as it pertains to methods). A simple way to fix this is to pull the ORM code out into a separate method and call it. This would let us re-use it in our search method:
1: public User GetUser(string userName)
2: {
3: string sql = "select UserName, FirstName, LastName, Email "
4: + "from Users where UserName = @UserName";
5:
6: using (SqlConnection connection = new SqlConnection(...))
7: using (SqlCommand command = new SqlCommand(sql, connection))
8: {
9: command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
10: using (SqlDataReader reader = command.ExecuteReader())
11: {
12: if (!reader.Read())
13: return null;
14: else
15: return ReadUser(reader);
16: }
17: }
18: }
19:
20: public List<User> GetAllUsersByLastName(string lastName)
21: {
22: List<User> results = new List<User>();
23:
24: string sql = "select UserName, FirstName, LastName, Email "
25: + "from Users where LastName = @LastName";
26:
27: using (SqlConnection connection = new SqlConnection(...))
28: using (SqlCommand command = new SqlCommand(sql, connection))
29: {
30: command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastName;
31: using (SqlDataReader reader = command.ExecuteReader())
32: {
33: while (reader.Read())
34: results.Add(ReadUser(reader));
35: }
36: }
37:
38: return results;
39: }
40:
41: private User ReadUser(IDataRecord record)
42: {
43: User user = new User();
44:
45: user.UserName = record.GetString(0);
46: user.FirstName = record.GetString(1);
47: user.LastName = record.GetString(2);
48: user.Email = record.GetString(3);
49:
50: return user;
51: }
This is getting better, but we’ve still got a bunch of boilerplate code. How do we get rid of it?
First, a quick introduction to the functional programming capabilities in C# 2.0. There are a few basic delegate definitions built into the System namespace. If you haven’t used them before, here they are:
1: delegate void Action<T>(T obj);
2: delegate bool Predicate<T>(T obj);
3: delegate TOutput Converter<TInput, TOutput>(TInput input);
These drive the functional capabilities that are seeping into the .NET base class library. For example, List<T> has a ForEach method, which accepts an Action<T> and executes it for each item in the list.
What might not be immediately apparent is that many existing methods already match the signatures of these delegates. In our case, our ReadUser method is actually a Converter<IDataRecord, User>, which just means that it’s a method that takes an IDataRecord as input, and returns a User. Combined with the automatic delegate creation feature of C# 2.0, this means that anywhere a Converter<IDataRecord, User> is expected, we can pass in ReadUser.
Let’s try to flex these newfound functional programming muscles to solve our boilerplate code problem. Let’s create a “helper” class that can abstract out what we want to do:
1: static class DbHelper
2: {
3: public static T ExecuteAndReadOne<T>(DbCommand command, Converter<IDataRecord, T> readMethod)
4: {
5: using (DbDataReader reader = command.ExecuteReader())
6: {
7: if (!reader.Read())
8: return default(T);
9: else
10: return readMethod(reader);
11: }
12: }
13:
14: public static List<T> ExecuteAndReadAll<T>(DbCommand command, Converter<IDataRecord, T> readMethod)
15: {
16: List<T> results = new List<T>();
17:
18: using (DbDataReader reader = command.ExecuteReader())
19: {
20: while (reader.Read())
21: results.Add(readMethod(reader));
22: }
23:
24: return results;
25: }
26: }
Consider for a moment what the readMethod parameter actually represents in ExecuteAndReadOne and ExecuteAndReadAll. We’ll be passing in a method, which will be called to do the actual object creation. Using DbHelper means that our original code is simplified significantly:
1: public User GetUser(string userName)
2: {
3: string sql = "select UserName, FirstName, LastName, Email "
4: + "from Users where UserName = @UserName";
5:
6: using (SqlConnection connection = new SqlConnection(...))
7: using (SqlCommand command = new SqlCommand(sql, connection))
8: {
9: command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
10: return DbHelper.ExecuteAndReadOne<User>(command, ReadUser);
11: }
12: }
13:
14: public List<User> GetAllUsersByLastName(string lastName)
15: {
16: string sql = "select UserName, FirstName, LastName, Email "
17: + "from Users where LastName = @LastName";
18:
19: using (SqlConnection connection = new SqlConnection(...))
20: using (SqlCommand command = new SqlCommand(sql, connection))
21: {
22: command.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastName;
23: return DbHelper.ExecuteAndReadAll<User>(command, ReadUser);
24: }
25: }
26:
27: private User ReadUser(IDataRecord record)
28: {
29: User user = new User();
30:
31: user.UserName = record.GetString(0);
32: user.FirstName = record.GetString(1);
33: user.LastName = record.GetString(2);
34: user.Email = record.GetString(3);
35:
36: return user;
37: }
See how we’re passing in the ReadUser method to the Execute calls? When you build this code, the compiler actually creates a Converter<IDataRecord, User> delegate for ReadUser and passes that into the Execute methods of the DbHelper type.
This is great so far, but we can do better still. Remember the problem with the Email column being nullable? Adding IsDBNull() calls to our reader methods gets old fast, and makes the code look pretty ugly:
1: private User ReadUser(IDataRecord record)
2: {
3: User user = new User();
4:
5: user.UserName = record.GetString(0);
6: user.FirstName = record.GetString(1);
7: user.LastName = record.GetString(2);
8: user.Email = (record.IsDBNull(3) ? null : record.GetString(3));
9:
10: return user;
11: }
Doesn’t look too bad now, but in a realistic database table with 50 columns, 25 of which are nullable, it’ll get irritating. Also, if we change the nullability of a field, we have to always remember to add the check to our ReadUser method. (What? A database that changes? Never! :)
However, consider the GetString method of the IDataRecord interface (which our SqlDataReader implements). It takes an integer (the index of the column) and returns a string. That means it qualifies as a Converter<int, string>!
We can use this knowledge to add a couple methods to DbHelper to abstract out the DBNull checks:
1: static class DbHelper
2: {
3: // ...
4:
5: public static T SafeRead<T>(IDataRecord record, int index, Converter<int, T> readMethod)
6: {
7: return SafeRead<T>(record, index, readMethod, default(T));
8: }
9:
10: public static T SafeRead<T>(IDataRecord record, int index, Converter<int, T> readMethod, T defaultValue)
11: {
12: if (record.IsDBNull(index))
13: return defaultValue;
14: else
15: return readMethod(index);
16: }
17: }
The first SafeRead overload will return the default value for the specified type if the field is null. The second overload lets you specify your own value to return on null instead. This is useful in cases when you want to return a special flag value for null entries — for example, if it’s an integer column, but zero is a valid value.
Now, we don’t need to worry about nulls in our ReadUser method:
1: private User ReadUser(IDataRecord record)
2: {
3: User user = new User();
4:
5: user.UserName = DBHelper.SafeRead<string>(record, 0, record.GetString);
6: user.FirstName = DBHelper.SafeRead<string>(record, 1, record.GetString);
7: user.LastName = DBHelper.SafeRead<string>(record, 2, record.GetString);
8: user.Email = DBHelper.SafeRead<string>(record, 3, record.GetString);
9:
10: return user;
11: }
We’re actually passing the GetString method of our record object to the SafeRead method. The SafeRead method will do the null check, and if the field is not null, it will call the GetString method to get its value. If the field contains a null value, GetString will not be called.
There’s one final thing to implement, and in my opinion, is pretty friggin’ cool. There’s a static type in the System namespace called Convert, which contains a bunch of static methods that do conversion. For example, there’s a method called Convert.ToDateTime takes an object and returns a DateTime. Get it? It’s a Converter<object, DateTime>!
Here’s a couple additional methods to add to DbHelper:
1: static class DbHelper
2: {
3: //...
4:
5: public static T ExecuteScalar<T>(DbCommand command, Converter<object, T> converter)
6: {
7: return ExecuteScalar<T>(command, converter, default(T));
8: }
9:
10: public static T ExecuteScalar<T>(DbCommand command, Converter<object, T> converter, T defaultValue)
11: {
12: object result = command.ExecuteScalar();
13: return (result == DBNull.Value) ? defaultValue : converter(result);
14: }
15: }
These methods work essentially like the SafeRead methods, only calling the provided conversion method if the scalar value returned by the query is not null. Now, if you want to run a query that returns a single value (say a string), which may be null, it’s easy:
1: public string GetEmail(string userName)
2: {
3: string sql = "select Email "
4: + "from Users where UserName = @UserName";
5:
6: using (SqlConnection connection = new SqlConnection(...))
7: using (SqlCommand command = new SqlCommand(sql, connection))
8: {
9: command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
10: return DbHelper.ExecuteScalar<string>(command, Convert.ToString);
11: }
12: }
If you’re used to a static language, it takes some time to get used to treating methods more like variables. However, as you can see, it can make your code cleaner and more elegant — not to mention smarter and less prone to bugs!
If you’d like to download the DbHelper class used in this project, it’s available here.
