Moses Yap
:-) A happy IT Guy

CodeSmith: Simple example on generating an update SQL from a database table

May 4, 2008 14:51 by mosesyap

This example isn't so original.  In fact, its taken from two sources:

Building a template to generate stored procedures
http://community.codesmithtools.com/blogs/tutorials/archive/2006/02/15/StoredProcedures.aspx

and

Video Tutorial
http://community.codesmithtools.com/blogs/videotutorials/archive/2006/02/16/StoredProcedures.aspx

 

The example above had some syntax error.  It is still an excellent article.  Here is a working version:

 

<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Src="" 
    Inherits="" Debug="False" Description="UPDATE stored procedures." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" 
    Category="Context" Description="Table the stored proc will be based on" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>

-- Date Created: <%= DateTime.Now.ToLongDateString() %>

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.Columns.Count; i++) 
   { %>
      [<%= SourceTable.Columns[i].Name %>] = @[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.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 %>
     <% } %>
    
<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>

 

Some Notes:

- We specify input parameters to the template by using the <Property> tag.  In this example, we are using the SchemaExplorer.TableSchema as the input.  With this, we would need to enter a table for it to connect to.

The SchemaExplorer.TableSchema object has a lot of properties you can use to build the output. Included are Name (name of the table), PrimaryKey (a collection of columns that makes up the primary key) and Columns (a collection of columns for the table).

The next article would show how to automate generation by using a .NET project similar to how Subsonic (subcommander) does.


Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Tags:
Categories: CodeSmith
Actions: E-mail | Permalink | Comments (0) | Comment RSSRSS comment feed

Related posts

Add comment


(Will show your Gravatar icon)  

  Country flag

[b][/b] - [i][/i] - [u][/u]- [quote][/quote]



Live preview

November 21. 2008 14:36