Sometimes you encounter projects where stored procedures are referenced by string literals wherever they are invoked, e.g.:

1

cmd.CommandName = "FindStudentsByName";

If the name is mistyped or renamed, the developer would usually only find out through runtime exceptions.

To solve this, I wrote a T4 snippet that creates a C# class of stored procedure names as string constants. This provides intellisense hints for the developer as well as compile time checking of stored procedure names.

The section at the top is for configuring project specific information (e.g., namespace, class name, database, connection string). There’s also a naming filter function where you can define how special characters get translated into a permissible C# variable name.

1
2
3
4

Func<string, string> applyNamingFilters = x => x
  .Replace(" ", "_")
  .Replace("-", "_")
  .Replace(".", "$");

The template executes independent from the rest of the project, so the block that follows is responsible for setting up the environment to grab the connection string out of the Web.config. T4 does not generate automatically in VS 2010. After a stored procedure is added to the database, right-click on the template and select “Run custom tool” to regenerate the constants.

The source is availble on github: Stored Procedure Constants