I first had a contact with this tool, few years ago. My first impression was not good at all. In that moment I didn’t find it useful, especially the templates that comes with it. After some time I saw a very good template for procedures that was some 70% of my expectations. I decide to spend and have a deep look at the template and realized that it is not that hard to learn and write my own templates. That is how all started. After several years of using it, have such a large help in some big projects, now I think that if you work on some large project with some very large database then this or some other code generation tool is a must have for every developer. On fifth November 2009 I had some presentation on the regular monthly meeting organized by local .NET group. I presented what this tool is capable for and I present my several templates that I use every day. I mainly use four templates. First one is for stored procedures. Template is used only with SQL Server database but it requires very small change especially in data types in order to be used with some other types of database. This template knows to generate several useful procedures:
· Delete procedure
· Insert procedure
· Update procedure
· SelectAll procedure
· SelectByKey is a procedure that knows to generate the correct primary key parameter and to return one table’s record
· SelectBy??? are procedures if required for every foreign key in the table
· SelectByUniqueKey are procedures that selects table’s record by the unique keys
· SelectPaged and SelectDinamic that are not complete procedures like previous ones but are starting 80% of the final procedure.
One condition is required for all templates, and that is “table must have primary key”. What are the main characteristics of the three Delete, Insert and Update procedures. The template has some additional properties that are used for generating procedures. WindowsAuthentication property if true assumes windows authentication and if false sql authentication. Later this property is used to decide how to create the log part of DML actions. I can generate two types of log actions. One that is created if AddActionLogging and will write only the name of procedure, user who executed it, date of execution and the primary key for the record in one LogAction table. Second type of logging is full table logging in parallel table with identical design as the main table if AddTableLogging is set to true. Mainly the first type is used, but if the complete logging is required for some mainly very important table, then this type of logging is used. IncludeDropStatements and IsolationLevel are standard properties in procedures templates that decide whether a drop statements before create statements will be generated and the second will decide which isolation level will be used. All the procedures are schema aware and are created in the same schema as the main table. And some other properties which are language related (are we use English or Macedonian names for tables) and did I have some read only columns, and if I have, then they are not generated as input parameters. It looks like I have too many properties, but large number of features provided by the template requires some properties for configuring it before usage. My templates are divided in cst file (the main template) and the code behind or CodeTemplate file where only C# methods are located.
My other templates are for Business Data Layer class which will call all these procedures, BusinessObject that is object presentation for record in the table and BussinessCollection which is collection for BussinessObject.
Additionally I have a template for generating calling C# code for existing procedure in the database. Of course I have some additional templates used for my specific needs not mentioned in this post.
I have attached all important templates, and hopefully will be very simple to use others, without explanation. In fact only the one for procedures and for DataLayer have so many properties. Others are very simple with one or two properties. If you have any questions about my templates please write me a note or send me an email.
Although i haven't worked on such a big (db) projects, i found the presentation to be highly valuable.
First of all, it showed us a segment of everyday working problems and a really elegant solution to it.
It also gave us a nice overview of code generation systems in general. Using an Asp.Net to generate various code outputs was an eye-opener for me.
I wish we have more presentations like this - tools & techniques from our everyday work. For real professionals i think this is 100 times more valuable than presentations of latest and greatest technologies we all have problem coping with.
The author of mkdot.net has written an excellent article. You have made your point and there is not much to argue about. It is like the following universal truth that you can not argue with: There?s no such thing as a "dirty" word; words only mean what the listener thinks they mean. Thanks for the info.