Optimizing Code Reuse Between an Application and Database

Every good developer strives to reduce the amount of repeated code.  One area that has been difficult to tackle is the sharing of code between the application and the database.  Certain pieces of logic are typically repeated in the database. But when SQL Server 2005 was released, the ability to write .NET code in the database became a possibility, thus reducing the amount of repeated code.

An often repeated code between the application and the database are enumerations, a set of related name constants.  Enumerations can be used to define a piece of data’s current state.  For example, in a system with workflow, a page can be in one of many states: submitted, approved, published or archived.  In VitalSite, Geonetric’s content management solution, we maintain the meaning of the state in two places: an enumeration in the code and a table in the database.  The problem arises when the list of possible states changes; at that time, both the application and the database must be updated. If negelected, issues can arise.

My co-worker at Geonetric has consistently challenged me with finding a way to optimize code reuse between  the application and the database, in order to reduce issues that can impact clients.  The solution we came up with is by using CLR User-Defined Types to wrap the enumerations. A CLR User-Defined Type requires the implementation of certain methods and properties.

To start creating a CLR User-Defined Type, you need to create a SQL Server project within Visual Studio.  Once the project is created, you add the file containing the enumeration – as a link – from the application project.  Now that the enumeration is added, you can create a new CLR User-Defined type.

After implementing the required methods and properties, you are ready to start wrapping the enumeration in your data type.  To do this, create static properties for each value in the enumeration.  The following code snippet shows how to do this for an enumeration that contains the directions, North, South, East, and West:

public static SqlInt64 North
{
get { return (Int64)Directions.North; }
}

public static SqlInt64 South
{
get { return (Int64)Directions.South; }
}

public static SqlInt64 East
{
get { return (Int64)Directions.East; }
}

public static SqlInt64 West
{
get { return (Int64)Directions.West; }
}

Now you are ready to deploy your new User Defined-type to SQL Server.  This new data type can be used as a column data type or a variable data type.  From within the SQL code you can reference properties and methods from the new data type with the following syntax Schema.DataTypeName::PropertyName or Schema.DataTypeName::Method(variables).  An example with the directions enumeration would be dbo.Directions::West.

This solution still requires you to update code in two spots if the enumeration changes. But if one spot is missed (and this is the best part), there will be build errors, preventing the issue from reaching clients, which was our goal.

The SQL CLR is a major improvement to SQL Server. With the release of SQL Server 2008 this past year, a lot more functionality has been made available.  However, proceed with caution. Make sure to not implement too much functionality in the CLR.

Plusone Twitter Facebook Email Stumbleupon Pinterest Linkedin Digg Delicious Reddit
This entry was posted in Content Management (CMS) by Jason Adams. Bookmark the permalink.
Jason Adams

About Jason Adams

Jason brings more than nine years of experience in database development and administration to Geonetric as a devoted SQL Developer. His expertise is guiding our database modeling and design as well as database server configuration and setup. Jason diligently works to improve query performance on the database, and also creates database objects such as tables, stored procedures, and user-defined functions. For Geonetric clients, he provides detailed data reports. Jason has experience in Visual Basic 6.0 and SQL Server 6.5, .NET, SQL Server 7.0, and SQL Server 2000.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.