Handling DAL Exceptions - Solution

Roger Torres - January 22nd, 2009
3 Comments

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

Roger Torres - January 16th, 2009
Comments

A couple of readers have asked me about how we handle exceptions inside the CoNatural.Data framework. In general, DAL exceptions should not be bubbled to the upper layers for security reasons, but during the development phase, we need to show exception details to the developers or users testing the application.

The way I have been dealing with DAL exceptions is by catching them inside the controller layer, and deciding right there if I want to bubble it or replace it with some other “more meaningful and secured message”. But I admit there is room for rationalization here, and I will spend some time in the next post to explain an alternative that will centralize this issue, taking care of exception handling policies by specific data commands, exception logging, and notifications.

Stay tuned.

How to write a VS-2008 Item Template and Wizard for CoNatural Data Commands

Roger Torres - January 10th, 2009
Comments

Although writing software is a very creative and enjoyable venture, it’s not free from boring and repetitive activities. Software designers should always try to find mechanisms to streamline the development process in order to get rid of these activities. This will not only save us time and money, but will ultimately help us to build a more robust, reusable, and extensible product.

Visual Studio offers multiple extensibility mechanisms aimed to accelerate the development process, including project and item templates as one of the simpler and most effective ways to jump-start a project without having to create all the items from scratch.

A template is basically a compressed .zip file containing:

  1. All the source code files, embedded resources, project files, etc required to recreate and item or project.
  2. A .vstemplate file containing the metadata that provides Visual Studio with the information it needs to display the template in the New Project and Add New Item dialog boxes and create a project or item from the template.


Visual Studio automatically displays all templates (placed in the proper folder) in the My Templates section of the New Project and Add New Item dialog boxes.

Templates are great for applying coding conventions to our classes, or to enforce a consistent project or solution structure, but we must be careful not to overdo it. Templates must be static elements in general, since changing a template in a very advanced project could potentially trigger an immense amount of manual changes in all the derived items that have been modified.

In this post I will show how to define a very simple item template for CoNatural Data Commands, following the naming conventions and rules specified by the CoNatural Data Framework. CoNatural Commands are defined by a pair of files, one implementing a SQL script, and the other (nested file) implementing the “code-behind’ in charge of mapping parameters and other data transformation operations. The template should enforce the following rules:

  1. Both files must have the same name (the name of the command). File extensions are .sql and .cs for CSharp projects.
  2. The code-behind file must be nested inside the SQL script file.
  3. The SQL script file must be configured as an embedded resource inside the project.

Visual Studio Wizard

Sometimes you need to run custom code when creating a project or item from a template. Some of the operations we might need are:

  1. Display a custom UI to collect user input to parameterize the template.
  2. Add additional files to the template.
  3. Perform any actions allowed by the Visual Studio automation object model on a project.

Visual Studio provides the IWizard interface as another extensibility mechanism to customize your projects. The IWizard interface methods are called at various times while the project is being created, starting as soon as a user clicks OK on the New Project dialog box.

In our example we will use this interface to nest the code-behind file and configure the SQL scripts as an embedded resources.

You can find more information about Visual Studio templates and wizards at http://msdn.microsoft.com/en-us/library/6db0hwky(VS.80).aspx

The Solution

Let’s start by creating a new C# Visual Studio 2008 class library project like the one below:

CoNatural Visual Studio Wizard

CoNatural Visual Studio Wizard

Here we are implementing our VS Wizard component inside the “TemplateWizard” namespace, but we will also hold the item template and metadata files in the internal “Files” folder for convenience. In case you want to modify the template, this is the place to go. Note that we have four files in this folder:

  1. Command.cs: The template for the code-behind.
  2. Command.sql: The template for the sql script.
  3. Command.ico: The icon VS will use to show our template.
  4. Command.vstemplate: The template metadata.


All these files must be configured with the “Build Action” set to “None” since they are not part of the Wizard component and they will not compile.

Our template is very simple, I will just define a common header in all my SQL script files with the name of the command, who created it and the time of creation. Visual Studio templates support parameter substitution to enable replacement of key parameters, such as class names and namespaces, when the template is instantiated. You can find a list of parameters at http://msdn.microsoft.com/en-us/library/eehb4faa(VS.80).aspx.

The template for the SQL script is defined as follows:

1
2
3
4
5
6
7
8
9

-------------------------------------------------------
-- $itemname$
--
-- Created by $username$
-- Created on $time$
-------------------------------------------------------
 
-- TODO Write your command's sql statement here
-- e.g.  SELECT a, b, c FROM tableName WHERE id = @id

There is nothing very sophisticated here, just the header and a comment with a simple example showing how to write a script. You can modify this template with the information you would normally want your developers to have.

The template for the code-behind file also includes a header section, but there are a couple of important details:

  1. We are declaring our class inside the namespace where the command has been defined.
  2. Our class is derived from CoNatural.Data.Command.
1
2
3
4
5
6
7
8
9
10
11
12
13

//-------------------------------------------------------
//-- $itemname$
//--
//-- Created by $username$
//-- Created on $time$
//-------------------------------------------------------
using System;
 
namespace $rootnamespace$ {
	public class $safeitemname$ : CoNatural.Data.Command {
		// TODO Write your parameters and other helper methods here
	}
}

The last file defines the template metadata:

<VSTemplate Version="2.0.0" xmlns="http://schemas.microsoft.com/developer/vstemplate/2005" Type="Item">
  <TemplateData>
    <DefaultName>Command.cs</DefaultName>
    <Name>CoNatural.Data.Command Item</Name>
    <Description>An empty CoNatural data command definition.</Description>
    <ProjectType>CSharp</ProjectType>
    <SortOrder>10</SortOrder>
    <Icon>Command.ico</Icon>
  </TemplateData>
  <TemplateContent>
    <ProjectItem TargetFileName="$fileinputname$.sql" ReplaceParameters="true">Command.sql</ProjectItem>
    <ProjectItem TargetFileName="$fileinputname$.cs" ReplaceParameters="true">Command.cs</ProjectItem>
  </TemplateContent>
  <WizardExtension>
    <Assembly>CoNatural.Data.VisualStudio, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1c0fcfe5189a65ef</Assembly>
    <FullClassName>CoNatural.Data.VisualStudio.TemplateWizard.CommandWizard</FullClassName>
  </WizardExtension>
</VSTemplate>

You can find more information about Visual Studio template metadata files at http://msdn.microsoft.com/en-us/library/xsxc3ete(VS.80).aspx.

Enforcing the rules

Now we just need to enforce the rules imposed by our framework, but a simple item template cannot do it. The third section of the metadata describes the WizardExtension component that will take care of our custom code when the template is instantiated. Going back to our project, you will find the CommandWizard class implementing the IWizard interface as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.VisualStudio.TemplateWizard;
 
namespace CoNatural.Data.VisualStudio.TemplateWizard {
  public class CommandWizard : IWizard {
    private EnvDTE.ProjectItem sqlFile;
    private EnvDTE.ProjectItem csFile;
 
#region IWizard Members
 
    public void BeforeOpeningFile(EnvDTE.ProjectItem projectItem) {}
    public void ProjectFinishedGenerating(EnvDTE.Project project) {}
 
    public void ProjectItemFinishedGenerating(EnvDTE.ProjectItem projectItem) {
      if (projectItem.Name.EndsWith(".sql"))
        sqlFile = projectItem;
      else
        csFile = projectItem;
    }
 
    public void RunFinished() {
      if (csFile != null && sqlFile != null) {
        // nest code-behind file
        string filename = csFile.get_FileNames(0);
        sqlFile.ProjectItems.AddFromFile(filename);
 
        // enforce build action as embedded resource   
        sqlFile.Properties.Item("BuildAction").Value = "3";
      }
    }
 
    public void RunStarted(object automationObject, Dictionary<string, string> replacementsDictionary, Microsoft.VisualStudio.TemplateWizard.WizardRunKind runKind, object[] customParams) {}
 
    public bool ShouldAddProjectItem(string filePath) {
      return true;
    }
 
#endregion
  }
}

Here we identify our project items when Visual Studio is generating the template. Before Visual Studio finishes, we make sure the SQL script file (with extension .sql) is configured as an embedded resource, and the code-behind file is “nested” inside the script. If you look at the project file after adding a few commands, you will understand the magic behind all this. Visual Studio takes care of preparing the project metadata and displaying our files as expected. Here is a section of the project I’m using as the DAL to test with AdventureWorks. Note how the code-behind files are linked to their corresponding scripts using the DependentUpon elements:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

<ItemGroup>
    <Compile Include="AdventureWorksConnectionProvider.cs" />
    <Compile Include="dbo\uspGetEmployeeManagers.cs">
      <DependentUpon>uspGetEmployeeManagers.sql</DependentUpon>
    </Compile>
    <Compile Include="dbo\uspGetManagerEmployees.cs">
      <DependentUpon>uspGetManagerEmployees.sql</DependentUpon>
    </Compile>
    <Compile Include="HumanResources\uspUpdateEmployeeHireInfo.cs">
      <DependentUpon>uspUpdateEmployeeHireInfo.sql</DependentUpon>
    </Compile>
    <Compile Include="HumanResources\uspUpdateEmployeeLogin.cs">
      <DependentUpon>uspUpdateEmployeeLogin.sql</DependentUpon>
    </Compile>
    <Compile Include="Properties\AssemblyInfo.cs" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="HumanResources\uspUpdateEmployeeHireInfo.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="dbo\uspGetEmployeeManagers.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="dbo\uspGetManagerEmployees.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="HumanResources\uspUpdateEmployeeLogin.sql" />
  </ItemGroup>

Deploying the template

The assembly implementing the Wizard must be installed in the GAC, so we will configure the project to take care of this in the pre-build and post-build actions as follows:

Pre-Build Steps

Pre-Build Steps

Post Build Steps

Post Build Steps

The gacutil.exe can also be found at C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe

Now we can compile the project and make sure the new assembly has been registered in the GAC.

CoNatural Wizard Assembly

CoNatural Wizard Assembly

Once the wizard is compiled and installed in the GAC, we can verify that the public key token assigned to our assembly matches the one we described in the template metadata.

Finally, let’s Zip our template files into CoNatural.Data.Command.Template.zip and copy it to the Visual Studio templates folder at:

\My Documents\Visual Studio 2008\Templates\ItemTemplates\Visual C#

Restart Visual Studio and the new CoNatural Data Command template will be there for you to enjoy it!

But this is not enough!

This example will help you create new CoNatural Data Commands in a snap, taking care of the configuration details and conventions you might want to use. But what would happen to my command if I rename one of the files?

I will describe one solution to this problem in a future post. Stay tuned.