codesmith tutorial

Introduction CodeSmith is a FREEWARE template based code generator for any ASCII language. It uses a syntax very similar to ASP.NET. This tutorial describes the process of building a template to generate SELECT/INSERT/UPDATE/DELETE stored procedures. Background CodeSmith is inspired by DevelopMentor's Gen<X> product. This product was discontinued and also was pretty expensive. I hope that by offering a free template based code generator we will be able to build a community of users and templates and put an end to repetative coding. Getting Started All templates must include a CodeTemplate directive like this one. <%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Generates SELECT/INSERT/UPDATE/DELETE stored procedures." %> Here we tell CodeSmith that we plan to use C# as our template language, we plan to generate code for the T-SQL language and we also provide a description for what the template does. The Language attribute tells CodeSmith what language we will use to write our template code and can be C# (C#, CS, CSharp), VB.NET (VB, VBS, VisualBasic, VBScript) or JScript.NET (JS, JScript, javascript). The TargetLanguage attribute can be anything, but setting it to T-SQL will cause the template to be grouped together with other T-SQL templates in CodeSmith Explorer's target language view. Also, in the future, this attribute will be used to enable syntax highlighting. And finally, the Description attribute allows us to provide a brief description of the template that will be shown in CodeSmith Explorer. In order to be able to generate code based on a database table, the template must somehow know about this table. CodeSmith allows us to provide this contextual information to the template by specifying Property directives. <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the stored procedures should be based on." %> The Name attribute tells CodeSmith what we want the property to be named in our template. This is the name that we will use to access the property in our template. The Type attribute tells CodeSmith what type our new property should be. This can be any .NET type that has a designer (most built-in .NET types have designers). In this case we use a type from CodeSmith's included Schema Explorer library. This type allows us to pick a table from a database and will then tell us anything we want to know about that table. We will use this information to drive our template. The Category attribute simply allows us to put our new property into a group of properties with the same category. And lastly, the Description attribute allows for a brief description of what the property will be used for in the template. CodeSmith allows the flexibility for anyone to create a new .NET type and, as long as they also create a designer for that type, it can be used in your templates. The included Schema Explorer library is an example of this flexibility. CodeSmith does not know anything about Schema Explorer, so we must instruct our template on how to find the SchemaExplorer.TableSchema type. We do this by adding an Assembly directive. <%@ Assembly Name="SchemaExplorer" %> This just tells our template that we plan to make use of code found in an external assembly. The Name attribute must correspond to an assembly (without the .dll extension) in either the same directory as the template or in the same directory as the CodeSmith executable. Writing Our Template Now that we have setup everything that we will need to base our template on, we can begin writing our template content. It is always best to start with an example of what you want the output to look like. Here is an example UPDATE stored procedure for the Northwind..Products table. ----------------------------------------------------------------- -- Date Created: Thursday, January 02, 2003 -- Created By: Eric J. Smith ----------------------------------------------------------------- CREATE PROCEDURE dbo.UpdateProducts @ProductID int, @ProductName nvarchar(40), @SupplierID int, @CategoryID int, @QuantityPerUnit nvarchar(20), @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint, @ReorderLevel smallint, @Discontinued bit AS UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID Now we must begin to make this into a dynamic template based on our SourceTable property. Let's start with a simple change to make the Date Created comment dynamic. -- Date Created: <%= DateTime.Now.ToLongDateString() %> Just like in ASP.NET we use the <%= %> construct. When the template is executed, this expression will be expanded to the current date in long date form. Now we need to figure out how to build our list of parameters for the stored procedure. We will derive this list from the information in the SourceTable.Columns property. This property is a collection of ColumnSchema objects that represent each column in the selected table. Here is a simple example of outputing each column in the table. <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %> <%= SourceTable.Columns[i].Name %> <% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% } %> In this example we use the <% %> construct to add logic to the template. What language you selected in the Language attribute from above will determine the language that you will need to use for your template logic. In our example, we have specified C# as our language so that is what we use here. The output from this example when the selected table is Northwind..Products looks like: ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued This is a nice start, but we have a little more work to do before this looks like a real set of parameters. Since the logic for each parameter will be somewhat complex, we should go ahead and create a method to output each parameter. To do this we will need to use a <script> block. Again, this is just like ASP.NET. <script runat="template"> public string GetSqlParameterStatement(ColumnSchema column) { string param = "@" + column.Name + " " + column.NativeType; switch (column.DataType) { case DbType.Decimal: { param += "(" + column.Precision + ", " + column.Scale + ")"; break; } default: { if (column.Size > 0) { param += "(" + column.Size + ")"; } break; } } return param; } </script> Instead of setting the runat attribute to server like we would do in ASP.NET, we set it to template. This causes the CodeSmith compiler to process this script block as code instead of passing it on as part of the template content. This distinction allows the creation of templates that output ASP.NET code with server <script> blocks. Also, on that note, I should mention that you will need to escape your ASP.NET constructs if you plan to have them output in a template (ie. <%% expression %%> results in this output <% expression %>). Here is a look at our final code for the UPDATE stored procedure: ----------------------------------------------------------------- -- Date Created: <%= DateTime.Now.ToLongDateString() %> -- Created By: Generated by CodeSmith ----------------------------------------------------------------- CREATE PROCEDURE dbo.Update<%= SourceTable.Name %> <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %> <%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% } %> AS UPDATE [<%= SourceTable.Name %>] SET <% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %> [<%= SourceTable.NonPrimaryKeyColumns[i].Name %>] = @ <%= SourceTable.NonPrimaryKeyColumns[i].Name %> <% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %> <% } %> WHERE <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %> <% if (i > 0) { %>AND <% } %> [<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %> <% } %> Generate The Code Now that you have built your template, you can use CodeSmith to execute it and see the results. You can execute the template by double-clicking the codesmith_sp.cst file that is included in the download at the top of this page. You will then see a screen that looks like this: Click the elipses next to the SourceTable property. Now click the elipses next to the Data Source drop down. Select Add. Enter the Data Source information. Name can be anything but you should make it something that represents the database in your connection string since this the name that will be used to select the Data Source in the future. Provider Type can be SqlSchemaProvider or ADOXSchemaProvider. SqlSchemaProvider should be used for any SQL Server or MSDE database and ADOXSchemaProvider should be used for any other database that has an ADOX provider. The Connection String is the same as any other connection string you would specify in your application. See the SqlConnection documentation for SqlSchemaProvider and the ADODB.Connection documentation for the ADOXSchemaProvider. Here we specify the Northwind database located in the .NET SDK sample MSDE instance. These settings should work for anyone who has installed the .NET SDK QuickStart samples. Click OK once you have entered all the information. Then click Close on the Data Source Manager screen. Now you should see a list of tables in the Northwind database. Select the Products table. Select Generate and see the results! After you generate the code, you can click the Copy Template Output button at the top and the output will be copied to your clipboard. Another great way to generate code once you have finished building your template is using the CodeSmith Explorer window. This is the window that is displayed if CodeSmith is started with no parameters. It allows you to see all templates in a given directory. You can then drag a template to any application that supports dropping text (this includes VS.NET). When you do so the template properties window will display, you will set all required properties and when you click Generate the output will be added to wherever you dragged the template. Conclusion CodeSmith allows the use of templates to avoid repetative coding tasks. We used a previous version of CodeSmith in our last project and were able to generate over 60% of our code. This included generating stored procedures, business objects, collection classes, ASPX pages and codebehind files. CodeSmith is being released as freeware in an attempt to build a strong community of users and templates. We hope that this will have an end result of all us programmers doing a little less repetative boring work! :-)