Quick and Efficient Database Library

In recent projects the need to continually re-write database access routines came up over and over.  I got tired of redoing the same code and so I wrote these quick database functions and extensions.  They are fully unit-tested and I'll include those at the end.

I think they could be made slightly more efficient using lambda expressions to handle the try-catch block, but that is a future enhancement that I might make.

For now, here's the first function: ExecuteScalar.  The power of these functions is that they are not tied to a specific database type such as SqlServer.  The use the generic System.Data interfaces for all external objects and even when they throw an exception they throw the generic DbException (from which exceptions such as SqlException inherit).  This allows you to theoretically connect and execute commands on a wider range of relational database types (though I have not tested it against MySql, Oracle, etc).

The function parameters are quite simple and straight-forward.  Pass in a connection, the name of the stored procedure (or the inline sql if you wish).  Optional arguments include a parameter list, an error message to include if an exception is thrown, and if you wish to trap any exceptions you can.

Code Snippet

29. public object ExecuteScalar(IDbConnection dbConnection, string storedProcedureName, IEnumerable<IDataParameter> parameters = null,
30. string errorMessage = "", bool throwException = true)
31. {
32.   object result = null;
33.   try
34.   {
35.     using (IDbCommand dbCommand = dbConnection.CreateCommand())
36.     {
37.       SetupDbCommand(dbConnection, dbCommand, storedProcedureName, parameters);
38.       dbCommand.Connection.Open();
39.       result = dbCommand.ExecuteScalar();
40.       dbCommand.Connection.CleanupConnection();
41.     }
42.   }
43.   catch (DbException ex)
44.   {
45.     dbConnection.CleanupConnection(); 
46.     if (throwException)
47.       throw;
48.   }
49.  
50.   return result;
51. }

This works great if all you are expected to be returned is a row count or a single value, but you need more if you expect an actual data-set. In that situation we have:

Code Snippet

141. public T ExecuteQuery<T>(IDbConnection dbConnection, string storedProcedureName, Func<IDataReader, T> translateFunction, IEnumerable<IDataParameter> parameters = null, string errorMessage = "", bool throwException = true) where T : class, new()
142. {
143.   if (translateFunction == null) throw new ArgumentNullException("translateFunction");
144.  
145.   try
146.   {
147.     using (IDbCommand dbCommand = dbConnection.CreateCommand())
148.     {
149.       SetupDbCommand(dbConnection, dbCommand, storedProcedureName, parameters);
150.       dbConnection.Open();
151.       using (IDataReader dbReader = dbCommand.ExecuteReader())
152.       {
153.         var result = new T();
154.         if (dbReader != null)
155.           result = translateFunction.Invoke(dbReader);
156.  
157.         dbCommand.Connection.CleanupConnection();
158.  
159.         return result;
160.       }
161.     }
162.   }
163.   catch (DbException ex)
164.   {
165.     dbConnection.CleanupConnection();
166.  
167.     if (throwException)
168.       throw;
169.   }
170.  
171.   return null;
172. }

This function takes a data reader and passes it to a custom translation function you pass in.  The translation function is responsible for retrieving the data from the reader and creating the object or objects you wish to return.  In most cases I have used this by creating a static translate function on the class itself.  I have also created a variant of this function to handle returning an IEnumerable<> of the object type.

Finally, one key component these functions don't handle is transaction management.  For that, for a unit of work, you'd need to wrap the calls in your own transaction statements complete with commit and rollback handling.

Enjoy and as usual I welcome and encourage feedback!

Using Lamda's and Generics to encapsulate a Try-Catch-Finally block

I recently had a legacy (.NET 2.0) project that I was asked to bring up to date with .NET 4.5.  In the process I came across numerous functions that followed the same format wrapping their functionality in a try-catch-finally code block or some variant (try-catch or try-finally).

This is a normal process in many apps, of course, but in many of these cases the catch and finally logic were duplicated.  The more I worked with it, the more I felt like there had to be a way to create a re-usable function to handle it for me.  What I came up with works pretty darn well, though it does make the code a little more complicated.

Code Snippet

101. public static TResult TryCatch<TParam1, TResult, TException>(this Func<TParam1[], TResult> executeFunc, Func<TException, TResult> errorAction = null, Action<TParam1[]> finalAction = null, params TParam1[] args) where TException : Exception
102. {
103.     if (executeFunc == null)
104.         throw new ArgumentNullException("executeFunc");
105.  
106.     try
107.     {
108.         return executeFunc.Invoke(args);
109.     }
110.     catch (TException ex)
111.     {
112.         if (errorAction != null)
113.             return errorAction.Invoke(ex);
114.     }
115.     finally
116.     {
117.         if (finalAction != null)
118.             finalAction.Invoke(args);
119.     }
120.     throw new ArgumentException("No return value specified");
121. }

As you can see the function takes a variety of inputs to handle the guts of the code itself as well as the catch and finally blocks. The key parts of this function are:

1. TParam1 - The type of parameter object
2. TResult - The type of the return object
3. TException - The type of exception expected to be caught
4. executeFunc - The function to execute in the try block
5. errorAction - An action to perform if an exception of the given type is caught
6. finalAction - An action to perform on Finally
7. args - An array of arguments passed to the executeFunc (if any)

Code Snippet

10. [Test]
11. public void TryCatch_Test()
12. {
13.     var callback = false;
14.  
15.     Func<object[], bool> func = x =>
16.         {
17.             callback = true;
18.             return true;
19.         };
20.     Func<Exception, bool> showError = exception => false;
21.  
22.     var result = func.TryCatch(showError);
23.  
24.     Assert.That(result, Is.True);
25.     Assert.That(callback, Is.True);
26. }
27.  
28. [Test]
29. public void TryCatch_Exception_Test()
30. {
31.     var callback = false;
32.  
33.     Func<object[], bool> func = x => { throw new Exception("Test Exception"); };
34.  
35.     Func<Exception, bool> showError = exception =>
36.         {
37.             callback = true;
38.             return false;
39.         };
40.  
41.     var result = func.TryCatch(showError);
42.  
43.     Assert.That(result, Is.False);
44.     Assert.That(callback, Is.True);
45. }
46.  
47. [Test]
48. public void TryCatch_DifferentType_Test()
49. {
50.     var callback = false;
51.     var finalCallback = false;
52.  
53.     Func<int[], int> func = ints =>
54.         {
55.             callback = true;
56.             return ints.Sum();
57.         };
58.     Func<Exception, int> showError = exception => 0;
59.     Action<int[]> final = ints => { finalCallback = true; };
60.  
61.     var result = func.TryCatch(showError, final, 2, 5, 8);
62.  
63.     Assert.That(result, Is.EqualTo(15));
64.     Assert.That(callback, Is.True);
65.     Assert.That(finalCallback, Is.True);
66. }

I included these three unit tests as I felt like they displayed the uses of this function quite nicely.

This code creates a nice re-usable function that gives us some real power and flexibility. No need to wrap your functions into a try-catch block. And since its an extension method you can write your Func and then tack the .TryCatch() call to the end.

NCalc and Custom Expressions

I've been a fan of MUDs (multi-user dungeons) since I was introduced to the internet back in 1993.  Back then I just played them, but after a while I started programming on them.  Since 2000 I've tinkered with various forms of them on my own, often using them as a platform for learning or for practicing coding techniques.

Recently I decided to take an existing code-base that was written in ANSI C and port the entire project to C# (for those who might be interested, the project is called SmaugCS).  Yes, that is pretty crazy I know, but its been a very fun challenge.  Even more of a challenge I think than creating a MUD from the ground up.

This past fall I found the need to evaluate expressions that are commonly used in games such as D&D.  These are often represented in short-hand form and appear as "2d4+2" or "12d10-6" where the "d" is short-hand for "die".  So 2d4 would tell the system to roll two 4-sided dice twice and add the results together.  In the Smaug MUD code this formula often incorporates statistics or values from within the game and appear as "2d4+L+4" where the L represents the level of a character or monster.  Parsing expressions can be challenging, but the addition of a variety of values made it even more so.

While working on a solution I came across a third-party library called NCalc - Mathematical Expressions Evaluator for .NET.  This appeared to have what I needed so I began working on integrating it, but I ran into a problem with the "L".  How to turn that into a custom function that NCalc could understand?

The solution it turns out was a mixture of regular expressions and the almight Func<>.  To begin with I download the NCalc package using Nuget.  This was the easiest way and ensures you have the latest version.  Then I created an ExpressionParser class with an Execute function that took the expression I want to parse as a string parameter.

Code Snippet

36. public int Execute(string expr)
37. {
38.     Validation.IsNotNullOrEmpty(expr, "expr");
39.  
40.     string newExpr = ReplaceExpressionMatches(expr);
41.  
42.     Expression exp = new Expression(newExpr);
43.     exp.EvaluateFunction += delegate(string name, FunctionArgs args)
44.       {
45.         if (_expressionTable == null)
46.           return;
47.  
48.         CustomExpression customExpr = _expressionTable.Get(name);
49.         if (customExpr != null && customExpr.ExpressionFunction != null)
50.           args.Result = customExpr.ExpressionFunction.Invoke(args);
51.       };
52.  
53.     object result = exp.Evaluate();
54.  
55.     Int32 outResult;
56.     Int32.TryParse(result.ToString(), out outResult);
57.     return outResult;
58. }

This may look a bit odd, but follow my logic here.  The important part is that the function must convert my syntax (in this case the "L" in the expression) to a function that the NCalc library can parse and that function must have logic associated with it.  So, in the case of the "L" we first want to transform it into something such as "Level()".  The expression started as "2d4+L+4" and becomes "2d4+Level()+4".  This is done using a custom ExpressionTable that we pass into the ExpressionParser during construction.  I'll get to the table and to the ReplaceExpressionMatches() function in a moment.

To continue with the logic of the Execute function, after we replace "L" with "Level()" we create a NCalc Expression object and then a delegate that will be called when that expression is evaluated.  This delegate will invoke each custom function defined within the expression tree and replace it with the result.  So, if the character's level statistic was 16 at the time the expression was evaluated then the debugged expression might look like "2d4+16+4".

Finally, we tell the expression to evaluate itself and then try to parse the result.  That's it!  Pretty clean and straight-forward.  What about the regular expressions and your custom functions you ask?  Well, let's go back to that.

Within the Execute() function we made a call to another function in the class called ReplaceExpressionMatches() that looks like this:

Code Snippet

65. private string ReplaceExpressionMatches(string expr)
66. {
67.     if (_expressionTable == null || _expressionTable.Keys.IsEmpty())
68.         return expr;
69.  
70.     string newStr = expr;
71.     foreach (CustomExpression customExpr in _expressionTable.Values)
72.     {
73.       Regex regex = customExpr.Regex;
74.       int originalLength = newStr.Length;
75.       int lengthOffset = 0;
76.  
77.       foreach (Match match in regex.Matches(newStr))
78.       {
79.         string firstPart = newStr.Substring(0, match.Index 
80.           + lengthOffset);
81.         string secondPart = newStr.Substring(match.Index 
82.           + lengthOffset + match.Length,
83.         newStr.Length - (match.Index + lengthOffset + match.Length));
84.         newStr = firstPart + customExpr.ReplacementFunction.Invoke(match) 
85.           + secondPart;
86.         lengthOffset = newStr.Length - originalLength;
87.       }
88.     }
89.  
90.     return newStr;
91. }

What does this do?  Well, we loop through the CustomExpression objects within the ExpressionTable and do a regex match and replace on every match found (since there could potentially be more than one "L" in our expression).  And then we return the newly updated expression which turned "2d4+L+4" into "2d4+Level()+4".

The last piece is our expression table itself.  Here's what an entry looks like.  The ExpressionFunction is what is called when NCalc does the actual evaluation of the expression (e.g. getting the value of Level()) while the ReplacementFunction tells the ExpressionParser what to replace the regex match with.

Code Snippet

18. ExpressionTable table = new ExpressionTable();
19. table.Add(new CustomExpression
20. {
21.     Name = "Level",
22.     RegexPattern = @"^?\b\w*[l|L]\w*\b$?",
23.     ExpressionFunction = LevelFunction,
24.     ReplacementFunction = ReplaceLevelCall
25. });

The final two pieces, the contents of the ExpressionFunction and the ReplacementFunctions.

Code Snippet

108. private static int LevelFunction(FunctionArgs args)
109. {
110.     return GameManager.CurrentCharacter.Level;
111. }
112. private static string ReplaceLevelCall(Match regexMatch)
113. {
114.     return "Level()";
115. }

Both functions could probably be handled more elegant, perhaps in a static dictionary, but my primary goal was to make the system flexible.  So the CustomExpression object maintains a reference to each of these functions, one which tells the Replace method what to replace the match with (the word "Level()" in this case) and another which tells NCalc what to do when it encounters the Level() function in an expression (in this case, call the GameManager and get the CurrentCharacter's level).

That about covers it.  There are also unit tests covering the ExpressionTable and the ExpressionParser (giving it 96% coverage in fact).  All of this was packaged into two self-contained libraries for each portability and reference.

I welcome any feedback, suggestions, comments on the code and if you want to see more (including the unit tests) please let me know.

BiDirectional Dictionary

I had the need for a dictionary in a personal project recently, but I ran into a bit of a snag.  I needed to be able to look up values from either direction.  That is, both forward (Key to Value) and backward (Value to Key).  Your standard C# Dictionary out of the box doesn't support this behavior.

Fortunately I happened across a good solution from another blogger, Tim Barrass (Bidirectional Lookup).  So, I began implementing it and then writing unit tests to cover it.  In the process I uncovered a couple issues with adding new items and removing them that I corrected.  So, here's the updated dictionary:

The changes are pretty straight-forward:

1. Changed the secondList.ToList().Add(second); and firstList.ToList().Add(first); calls on lines 49 and 50 to Append to the IEnumerable<> and also to re-assign to the internal dictionaries.
2. Rewrote the entire Remove() function to remove from the internal lists and if its the last entry remove the keys as well.

Code Snippet

13. public class BidirectionalDictionary<TFirst, TSecond>
14. {
15.     private IDictionary<TFirst, IEnumerable<TSecond>> Forward { get; set; }
16.     private IDictionary<TSecond, IEnumerable<TFirst>> Backward { get; set; }
17.  
18.     private static readonly IEnumerable<TFirst> EmptyFirstList = new List<TFirst>();
19.     private static readonly IEnumerable<TSecond> EmptySecondList = new List<TSecond>();
20.  
21.     public BidirectionalDictionary(IDictionary<TFirst, IEnumerable<TSecond>> forward, IDictionary<TSecond, IEnumerable<TFirst>> backward)
22.     {
23.         Forward = forward;
24.         Backward = backward;
25.     }
26.  
27.     public BidirectionalDictionary()
28.     {
29.         Forward = new Dictionary<TFirst, IEnumerable<TSecond>>();
30.         Backward = new Dictionary<TSecond, IEnumerable<TFirst>>();
31.     }
32.  
33.     public void Add(TFirst first, TSecond second)
34.     {
35.         IEnumerable<TSecond> secondList;
36.         if (!Forward.TryGetValue(first, out secondList))
37.         {
38.             secondList = new List<TSecond>();
39.             Forward[first] = secondList;
40.         }
41.  
42.         IEnumerable<TFirst> firstList;
43.         if (!Backward.TryGetValue(second, out firstList))
44.         {
45.             firstList = new List<TFirst>();
46.             Backward[second] = firstList;
47.         }
48.  
49.         Forward[first] = secondList.Append(second);
50.         Backward[second] = firstList.Append(first);
51.     }
52.  
53.     public IEnumerable<TSecond> GetByFirst(TFirst first)
54.     {
55.         return Forward.ContainsKey(first) ? Forward[first] : EmptySecondList;
56.     }
57.  
58.     public IEnumerable<TFirst> GetBySecond(TSecond second)
59.     {
60.         return Backward.ContainsKey(second) ? Backward[second] : EmptyFirstList;
61.     }
62.  
63.     public void Remove(TFirst first, TSecond second)
64.     {
65.         IEnumerable<TSecond> secondList;
66.         if (Forward.TryGetValue(first, out secondList))
67.         {
68.             if (secondList.Contains(second))
69.             {
70.                 secondList.ToList().Remove(second);
71.                 if (secondList.Any())
72.                     Forward[first] = secondList;
73.                 else
74.                     Forward.Remove(first);
75.             }
76.             else
77.                 Forward.Remove(first);
78.         }
79.  
80.         IEnumerable<TFirst> firstList;
81.         if (Backward.TryGetValue(second, out firstList))
82.         {
83.             if (firstList.Contains(first))
84.             {
85.                 firstList.ToList().Remove(first);
86.                 if (firstList.Any())
87.                     Backward[second] = firstList;
88.                 else
89.                     Backward.Remove(second);
90.             }
91.             else
92.                 Backward.Remove(second);
93.         }
94.     }
95. }

And now the unit tests surrounding this code. I used NUnit for this project and as you can see covered nearly all of the behavior within the new class.

Code Snippet

8. [TestFixture]
9. public class BidirectionalDictionaryTests
10. {
11.     private static BidirectionalDictionary<string, string> _dictionary;
12.         
13.     [SetUp]
14.     private void OnSetup()
15.     {
16.         _dictionary = new BidirectionalDictionary<string, string>();
17.         _dictionary.Add("FirstValue", "FirstLookupValue");
18.     }
19.  
20.     [TestCase("FirstValue", "FirstLookupValue", true)]
21.     [TestCase("SecondValue", "FirstLookupValue", false)]
22.     public void GetByFirstTest(string firstValue, string secondValue, bool expectedResult)
23.     {
24.         var results = _dictionary.GetByFirst(firstValue);
25.  
26.         Assert.That(results.ToList().Contains(secondValue), Is.EqualTo(expectedResult));
27.     }
28.  
29.     [TestCase("SecondValue", "FirstLookupValue", true)]
30.     [TestCase("SecondLookupValue", "FirstLookupValue", false)]
31.     public void GetBySecondTest(string secondValue, string firstValue, bool expectedResult)
32.     {
33.         var results = _dictionary.GetBySecond(secondValue);
34.  
35.         Assert.That(results.ToList().Contains(firstValue), Is.EqualTo(expectedResult));
36.     }
37.  
38.     [Test]
39.     public void Remove_Test()
40.     {
41.         _dictionary.Remove("FirstValue", "FirstLookupValue");
42.  
43.         var results = _dictionary.GetByFirst("FirstValue");
44.  
45.         Assert.That(results.ToList().Contains("FirstValue"), Is.False);
46.  
47.         results = _dictionary.GetBySecond("FirstLookupValue");
48.  
49.         Assert.That(results.ToList().Contains("FirstLookupValue"), Is.False);
50.     }
51. }

Again, I want to thank Tim Barrass for the initial work on this dictionary. I hope this is helpful to anyone else as its worked great for me.

First Post - Introduction

This is my very first blog post, so here goes.  I'm not entirely sure what I'll be doing with this blog yet, but I suspect it may become a mixture of code, game design, writing, and perhaps even ramblings.  Maybe in the future it will gain more focus.  However, for now here it is.