Database-Driven Business Layer Templates

• Download Templates & Generated Code - 179.6 KB

Table of Contents

1. Introduction
2. SQL Extended Properties Primer
3. Putting it all Together
4. Building Enums From Tables
5. Room for Improvement
6. About the Source Files
7. Revision History

Introduction

Seeing the words "Build succeeded" is always nice, but seeing "Build succeeded" after adding thousands of lines of new code in a matter seconds is a thing of beauty. Code generation has saved me countless hours of work over the past few years, but it has also caused some headaches. I've used some templates that generate code which requires post-generation changes in order to compile. Other templates I've used generate a complete class, with "space reserved" for custom code, so you have to be careful to not overwrite the custom code when you regenerate the object. Overall, the times when I can just run a template, compile, and go have been few and far between.

As a result, my #1 goal with the business layer templates for my current project was to have "one-click code generation". In other words, I wanted to run a program that regenerates all my business entities based on the current database state, doesn't affect any of my custom code, and doesn't break anything, except as the result of database schema changes. In addition, I wanted the business entities to be clean and intuitive, so my teammates could jump right in and code without needing any help from me.

I ended up using a tool called MyGeneration for code generation in combination with the EntitySpaces business layer framework (and templates). This article addresses issues that aren't specific to EntitySpaces, but the templates I customized and am using in my examples are EntitySpaces templates. The EntitySpaces templates in combination with MyGeneration address the one-click requirement right out of the box. MyGeneration allows you to save settings from previous runs and input them into the template, so you can regenerate your business layer in a single click. In addition, the EntitySpaces templates create separate files for generated and custom code using partial classes, so you never have to worry about overwriting custom code when you regenerate business entities. If you're using a template that doesn't use partial classes and multiple files, you really need to find something else. Generated code does not belong in the same file with custom code--it causes unnecessary problems.

With requirement #1 out of the way, I spent most of my time modifying the templates to suit personal preferences and feature needs. I wanted to generate enums for certain tables, a way to organize generated files into sub-folders, so they would be easier to browse in Visual Studio, and a way to change the names of certain foreign reference properties, but foremost on my list was a way to name classes something other than the tables names. I prefer plural table names ("Select * From User" doesn't sound like you're selecting multiple users), but class names should be singular ("Users u = new Users()" doesn't sound like it represents a single user). The most logical place to store the table-to-class name map is in the database, since that's what controls the code generation. My original thought was to create custom tables in the database that would store information about the tables and columns, but then I discovered that it's already been done in SQL Server. Hello, extended properties!

SQL Extended Properties Primer

The easiest way to manage extended properties in your database is just to right-click on a database, table, or column in Management Studio's object explorer, select "Properties", then select "Extended Properties" within the Properties dialog. This interface allows you to easily add and update extended property values on individual objects. If you want to manipulate property values programmatically, you can access them via several system stored procedures and functions:

sp_addextendedproperty
    @name, @value,
    @level0type, @level0name,
    @level1type, @level1name,
    @level2type, @level2name

sp_updateextendedproperty
    @name, @value,
    @level0type, @level0name,
    @level1type, @level1name,
    @level2type, @level2name

sp_dropextendedproperty
    @name,
    @level0type, @level0name,
    @level1type, @level1name,
    @level2type, @level2name

fn_listextendedproperty
    @name,
    @level0type, @level0name,
    @level1type, @level1name,
    @level2type, @level2name

To add an extended property to a table, use the following parameters:

exec sp_addextendedproperty
    '[PropertyName]',
    '[PropertyValue]',
    'user',
    'dbo',
    'table',
    '[TableName]',
    null,
    null

To add an extended property to a column, use the following parameters:

exec sp_addextendedproperty
    '[PropertyName]',
    '[PropertyValue]',
    'user',
    'dbo',
    'table',
    '[TableName]',
    'column',
    '[ColumnName]'

fn_listextendedproperty returns a table ("objtype", "objname", "name", "value"), so it can be used in several different ways. You can either pass in the name of a table, column, and property name to get the value of that property, or you can leave nulls here or there, which in most cases function as a wild card. Here are some examples:

-- Get all table-level extended properties in the database
Select * From
fn_listextendedproperty (null, 'user', 'dbo', 'table', null, null, null)

-- Get all column-level extended properties for the Employees table
Select * From
fn_listextendedproperty (null, 'user', 'dbo', 'table', 'employees', 'column', null)

-- Get the value of the "Biz.Class" property for every table in the database
Select objname, value From
fn_listextendedproperty ('Biz.Class', 'user', 'dbo', 'table', null, null, null)

-- Get all extended properties defined on the database itself
Select * From
fn_listextendedproperty (null, null, null, null, null, null, null)

The update procedure works exactly the same as add. The only difference with the syntax of the drop procedure is that it doesn't seem to allow nulls for the property, table, or column name, so you can only delete one property at a time. Apparently Microsoft wants you to be very specific about what to drop.

Putting it all Together

So now that you know how to use extended properties, it's time to put it all together. The next step is to modify your code generation templates to run the queries above against your database, retrieve the property values, and generate code based on those values. You could really go any number of directions with the properties you choose to implement, based on the needs of your business layer. I decided to stick to the following properties. You can find the exact values for each table in the attached NorthwindExtendedProperties.csv file.

• Biz.Class - The class name to use for this table; typically the singular form of the plural table name.
• Biz.Path - The sub-path under which to create the file, organized by application/database features. You could also use this for namespaces, but EntitySpaces expects all your entities to be in the same namespace, so I didn't touch the namespace generation. I organized Northwind into "Customers", "Employees", "Orders", and "Products" groups.
• Biz.Enum (optional) - The name to use for an enum based on data from this table.
  • Biz.Enum.NameColumn - The name of the column whose values represent the enum value names.
  • Biz.Enum.ValueColumn - The name of the column whose values represent the numerical enum values.

Modifying the templates themselves was fairly straightforward. I created a method to load the extended properties for a given table or view and cache them, then return them as a KeyValueCollection. Elsewhere in the templates, I replaced the custom EntitySpaces methods for generating object and property names with my own methods that use the cached property values. Below you can see the method used to load extended property values, and an example of a function that uses those values. I believe the DatabaseSpecific class is part of the EntitySpaces helper library, but you could easily replace it with a SqlCommand that calls fn_listextendedproperty.

System.Collections.Generic.Dictionary<object,KeyValueCollection> props =
    new System.Collections.Generic.Dictionary<object,KeyValueCollection>();

private KeyValueCollection GetExtendedProperties(ITable tbl,IView vw)
{
    object key = ((object)tbl) ?? ((object)vw);
        
    if( !props.ContainsKey(key) )
    {
        MyMeta.Sql.DatabaseSpecific dbs =
            new MyMeta.Sql.DatabaseSpecific();
        KeyValueCollection kvc;
        if( tbl != null )
            kvc = dbs.ExtendedProperties(tbl);
        else
            kvc = dbs.ExtendedProperties(vw);
        props.Add(key,kvc);
    }
        
    return props[key];
}
    
private string EntityName(ITable tbl, IView vw)
{
    KeyValueCollection kvc = GetExtendedProperties(tbl,vw);
    foreach(KeyValuePair kvp in kvc)
        if(kvp.Key == "Biz.Class")
            return kvp.Value;
    return "MissingEntityName";
}

private string EntityPath(ITable tbl,IView vw)
{
    string path = "";
    KeyValueCollection kvc = GetExtendedProperties(tbl,vw);
    foreach(KeyValuePair kvp in kvc)
        if(kvp.Key == "Biz.Path")
            path = kvp.Value;
    return path.Replace(".","");
}

Below are a few examples of how I modified the templates to use extended property values in the generated code.

public partial class <%=EntityName(table,view)%> : <%=esEntityName(table,view)%> 
// was formerly
public partial class <%=esPlugIn.Entity(source)%> : <%=esPlugIn.esEntity(source)%>

string objName = EntityName(tr.ForeignTable, null) + "By" + tr.PrimaryColumns[0].Name;
// was formerly
string objName = esPlugIn.EntityRelationName(tr.ForeignTable, tr.PrimaryColumns[0], tr.IsSelfReference);

// Modification to write business entity files to sub-folders based on Biz.Path setting
string filename = input["txtPath"].ToString();
if (!filename.EndsWith("\\") ) 
    filename += "\\";
if(!String.IsNullOrEmpty(EntityPath(table,view)))
    filename += EntityPath(table,view);

I tweaked a few other minor things in the EntitySpaces templates to suit my purposes, such as removing the "UpTo" prefix from foreign key properties that reference parent rows, but I don't actually recommend doing that if you are an EntitySpaces user. While the "UpTo" prefix can make intellisense/auto-complete a little less helpful (typing "OrdersBy", doesn't find the UpToOrdersByCustomerID property), it is a helpful reminder of the direction of the relation, and it's a necessity for self-referential tables.

Building Enums From Tables

When required to represent a data type that's made up of a fixed set of options such as the Categories or Region table in Northwind, people tend to exclusively use either a separate database table (linked by foreign key), or a plain numeric column and an enum in code to interpret it. I say why not do both? Sometimes you want the power of a database table to represent additional information beyond a name and value, but you also want to be able to access specific values within the collection (ex: Project.Status = ProjectStatuses.Complete). If you already have a table with the options you need, you're only a few code-generation steps away from an enum. With the help of a code snippet from the friendly folks at EntitySpaces, I was able to add extended property-based enum generation to my templates. All you have to do is specify the enum name as well as the columns to use for the numeric and textual enum values in extended properties, and the following template code will do the rest. Note that the EntityEnum, EnumNameColumn, and EnumValueColumn methods have trivial implementations, similar to the functions above.

private System.Collections.Generic.List<string> EnumValues(ITable tbl)
{
    if( tbl == null )
        return null;
    
    System.Collections.Generic.List<string> values =
        new System.Collections.Generic.List<string>();
    
    string nameColumn = EnumNameColumn(tbl);
    string valueColumn = EnumValueColumn(tbl);
    if( nameColumn == null || valueColumn == null )
        return null;
    
    string strSQL = string.Format(
        "SELECT {0}, {1} FROM {2} ORDER BY {1}",
        nameColumn, valueColumn, tbl.Name);

    IDatabase database = MyMeta.Databases[databaseName];
    ADODB.Recordset rs = database.ExecuteSql(strSQL);
        
    if( rs != null && rs.RecordCount > 0 )
    {
        while( !rs.EOF )
        {
            values.Add(string.Format("{0} = {1}",
                System.Text.RegularExpressions.Regex.Replace(
                rs.Fields[0].Value.ToString().Trim(),
                "[^A-Za-z0-9]", "_"),
                rs.Fields[1].Value.ToString()));
            rs.MoveNext();
        }
        rs.Close();
        rs = null;
    }
    
    return values;
}


<%if (EntityEnum(table) != null) {%>
/// <summary>
/// Value enumeration for <%=table.Name%> table.
/// </summary>
public enum <%=EntityEnum(table)%>
{<%
    System.Collections.Generic.List<string> enumValues = EnumValues(table);
    for(int index = 0; index < enumValues.Count - 1; index++ ) {%>
    <%=enumValues[index]%>,<%}%>
    <%=enumValues[enumValues.Count - 1]%>
};
<%}%>

The code template snippet above generated the following enum for the Northwind Categories table.

/// <summary />
/// Value enumeration for Categories table.
/// </summary />
public enum Categories
{
    Beverages = 1,
    Condiments = 2,
    Confections = 3,
    Dairy_Products = 4,
    Grains_Cereals = 5,
    Meat_Poultry = 6,
    Produce = 7,
    Seafood = 8
};

Improving NCover Coverage Analysis

On an unrelated note to database-driven code templates, I thought I'd point out another helpful feature in my modified code templates. My team has a performance metric based on code coverage that requires us to cover a certain percentage of our custom business layer code. With so much generated code, it was very difficult to determine the actual numbers for coverage of custom code, because the generated was also being measured. As a result, I added [GeneratedCode] attributes to all members of the generated classes. You can instruct NCover to ignore members with a given attribute using the //ea command line argument, so using //ea DAK.CustomESN.BusinessLayer.GeneratedCodeAttribute to run coverage analysis on CustomESNBusinessLayer will give you the coverage percentage for your custom code only. While it's not really related to the topic at hand, I thought I'd mention it because it's helpful to know, and it may confuse an uninformed reader who stumbles across the attribute while browsing the templates.

Room for Improvement

One thing I would improve about my modified templates if I had time would be to add extended property support for columns. I considered it early on, but never had a problem with the column names and types the EntitySpaces templates generated, so I didn't change anything. The framework is there to make it easy to get extended property values from columns. It would just take a while to go through all the template files and replace all instances of esPlugin.PropertyName with my own PropertyName function. Also note that my modified templates are based off the July 2007 release of EntitySpaces. If you're using a newer version, you'll probably need to merge my modified templates with the latest release version.

I hope you enjoyed this brief look building dynamic code templates using extended properties. If you have any difficulties implementing similar functionality using MyGeneration, EntitySpaces, or another similar technology, I'd be happy to assist in any way that I can.

About the Source Files

I didn't include a client with the source code because it wouldn't really illustrate anything relevent to this article. You'll need a recent version of EntitySpaces in order to compile the business layer, but compilation isn't necessary; the code is mostly just there to show the results of the code generation. The most useful pieces are the modified MyGeneration templates in the Templates folder, and the Northwind extended property values in NorthwindExtendedProperties.csv.

Revision History

2008.01.03 - Initial Version
2008.01.11 - Removed \bin and \obj folders from source zip file. Since the generated code is for demonstration only and cannot be built without EntitySpaces, these folders are unnecessary.

Licence notes

The code related to EntitySpaces may contain usage terms in the download files themselves. All other code in this article is covered by the license below.