IntroductionCodeSmith 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. BackgroundCodeSmith 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 StartedAll templates must include a <%@ 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 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 Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the stored procedures should be based on." %> The 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 <%@ Assembly Name="SchemaExplorer" %> This just tells our template that we plan to make use of code found in an external assembly. The Writing Our TemplateNow 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 -- 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 <% 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 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 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 CodeNow 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. ConclusionCodeSmith 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! :-) |