Database Edition Custom Rule
One of the coolest feature of Visual Studio Database Edition is the ability to run Rules against a database project, like fxcop against a c# project. And the coolest part about this feature is that is really simple to write a custom rule.
Rules are important, I’ve seen project where there is no naming rule for objects in database, and so you will end with tables with column like, ablr_xxxx or xxxxx_xxx_Authorname etc etc, and the whole database looses consistency over time. Thanks to database edition I can create a custom rule that force all developers to use for example a standard naming scheme for the column.
Creating a rule is really simple, first of all create a c# project strongly signed, then add a class like this one.
To give visual studio full details about your new rule, simply use the right constructor.
As you can verify is really simple to define details of the rule, just give a the namespace where the rule resides then a unique code, name and description and some other information like help location.
A rule is a simple class that inherits from Microsoft.Data.Schema.StaticCodeAnalysis.Rule, then you simply need to specify what kind of object you want to check.
This specific rule has the purpose to enforce a specific naming of all columns of database. Each column must in fact contain 4 letters (table code) then an underscore and then the name of the column. If we have the table Customer the id column will be cust_id. This naming scheme could not be the best one, but I want it to be enforced into the database, because I really dislike that each table has is own naming scheme. All the check is done in the Analyze method
This code is really simple, the important part is that each object has a name subdivided in parts, a column has three parts: schema, table and column name, so I check the last part (column name) and I validate against a regular expression. If the validation fails, I simply create a Problem object. The Analyze method must in fact return a list of Problem object, each one describing a single and unique problem of the object being analyzed.
Problem object have several properties that can be used, but the most important one is the Severity, that is needed to specify if this problem is a warning or an Error. Since I want that my convention is enforced for every table, I set Problem to Error Severity.
Once you have this class ready you need to create an xml file that contains data about the rule
You must sign your assembly, and specify full qualified name in the xml, then you need to copy this xml file, along with the assembly that contains the rule, into the directory program files \Microsoft Visual Studio 9.0\VSTSDB\Extensions. Now you can reopen visual studio and verify that the rule gets loaded correctly
To verify the rule I’ve simply created one simple table with one valid column and one not valid column.
If you build the project you will get.
As you can see my custom rule gets executed, and the project will not compile until all columns are not named correctly.