Handling DAL Exceptions - Solution

Roger Torres - January 22nd, 2009

It’s generally OK to bubble application exceptions to the GUI layer in order to present to the end users a detailed description of what went wrong; but exceptions generated inside the DAL should be handled with care since displaying too much information about the underlying database could compromise the entire application. The system should also notify administrators when something goes wrong, logging exception details for later review, and sometimes swallowing the original exception and continuing with the execution as if nothing had happened.

In order to fulfill all of the above, I made a small modification to the CoNatural.Data framework to intercept all DAL exceptions by invoking a special method inside the Connection class. This method accepts the Original Exception and the CoNatural Command that generated it, and returns a customized exception that the framework will re-throw to the upper layers. By default, the same original exception is returned, but this method can be overridden by the designers to meet their specific requirements. The most common scenarios we will need to cover are:

  1. Perform some custom logging/notifications before returning the same original exception if there is nothing harmful in the details.
  2. Same as 1, but replacing the original exception with a new one that makes more sense to the end users while hiding sensitive information.
  3. Same as 1, but returning a null value. The DAL will shallow the original exception and continue working normally as if nothing wrong happened.

Let’s illustrate this concept with the specialized connection class I use to test with AdventureWorks. Here I override HandleException to implement the following rules:

a) Log all exceptions to the Console (this is just an example ;-)
b) Notify the admin when the exception was generated by a command in the dbo schema.
c) Return a user friendly exception to the upper layer with the name of the command.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

public class AdventureWorksConnection : CoNatural.Data.Connection {
  public AdventureWorksConnection(string connectionString) : 
    base(connectionString, new AdventureWorksDbProvider()) {
  }
 
  protected override Exception HandleException(CoNatural.Data.Command command, Exception dalException) {
    // Log exception to console
    Console.WriteLine(dalException.Message);
 
    // Email Exception to system admin if command comes from dbo
    if (command.CommandName.StartsWith("dbo.")) {
      // TODO email exception details
      Console.WriteLine("Exception emailed to admin.");
    }
 
    // Return user friendly exception
    return new Exception("Exception ocurred when executing command " + command.CommandName);
  }
}

Although this solution is very simple, it’s all I need … for now.

« Handling DAL Exceptions
CoNatural.Data Source Code »

3 Comments

  1. Anthony February 24th, 2009

    Hi Roger

    I’ve really enjoyed the CoNatural.Data series and I have learned a lot of new techniques and practices from looking through your code. A BIG thanks!

    I’ve been able to get all the concepts very clearly up until I saw your source code for AdventureWorksConnection above. I notice that it calls its base, passing a connection string and an AdventureWorksDbProvider instance. I think there is something about the use of these patterns that I’m just not getting.

    The “provider” that I had coded this was just a static class with a single method that returns a full instance of Connection. That is, within that static method, I was reading the config, getting a new SqlDbProvider, newing up a connection and returning it.

    So does your AdventureWorksDbProvider inherit from SqlDbProvider, or does it just implement IDbProvider? If it inherits, doesn’t this kind of defeat the goal of loose coupling? If it just implements IDbProvider, what advantage do you gain by having to re-implement all of the methods already contained in SqlDbProvider? And… more importantly, how exactly do you do that? Can you provide your source code for your version of AdventureWorksDbProvider? That would be very helpful! Thanks again!

    Anthony

  2. Roger Torres February 24th, 2009

    Hi Anthony

    I’m glad you enjoyed the article, and I hope you find a good practical application for it.

    Regarding the AdventureWorksDbProvider, this is just a subclass of the default SqlDbProvider class. I wanted to follow the SQL schema conventions used in AdventureWorks to generate the stored procedures, that’s why I had to override the method GenSpHeadName. There is another method I override to execute stored procedures with the credentials of the caller (GenSpHeadOptions - only for the Human Resources Schema). This is a very simple example, but it shows how you can encapsulate the logic to generate stored procedures in dedicated providers. You can use the same techniques to inject security and other type of validations automatically. I hope you understand the benefits of this approach, specially the flexibility and maintainability aspects.

    Here is the source code:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    namespace AdventureWorks.DAL {
   public class AdventureWorksDbProvider : CoNatural.Data.SqlClient.SqlDbProvider {
      public override string GenSpHeadName(CoNatural.Data.Command command) {
         string[] parts = command.CommandName.Split('.');
         string schema = parts[parts.Length - 2];
         string name = parts[parts.Length - 1];
         return string.Format("[{0}].[Adv_{1}]", schema, name);
      }
 
      public override string GenSpHeadOptions(CoNatural.Data.Command command) {
         if (command.CommandName.Contains("HumanResources"))
            return "\nWITH EXECUTE AS CALLER\n";
         return string.Empty;
      }
   }
}

    Regards

    Roger

  3. Anthony February 24th, 2009

    Perfect example, thank you. Now that I see the code, I think my confusion really stemmed from my own semantics of how I use the word “provider”. Something I picked up from Microsoft’s examples was the use of abstract classes as in MembershipProvider -> SqlMembershipProvider. I generally stick with that naming convention when I write a platform-specific provider. So as I adopt your framework, I’ll expect to see things like SqlAdventureworksProvider in my own code.

    Anthony

Leave a comment