CodeSmith自动生成Insert/Delete/Update/Select存储过程模板

<%--PrcHelpre.cs--%>

using System;
using System.IO;
using System.Xml;
using System.Data;
using SchemaExplorer;
using System.Web;
using CodeSmith.BaseTemplates;

/// <summary>
/// PK strategy of a table
/// </summary>
public enum PKStrategy {
    Identity, Customize
}

public class SQLHelper:OutputFileCodeTemplate
{ 
 public string ReturnDirectory(string str)
 {
  int index=str.LastIndexOf('/x5C');
     return str.Substring(0,index+1);
 }
}

<%--GeneratorCode.cst-%>

<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Src="HelpFile/PrcHelpre.cs" Inherits="SQLHelper" Debug="true" Description="Generates a update store procedure." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Default="" Optional="false" Category="Context" Description="Table that the stored procedure should be based on." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.IO" %>

<%this.OutputFile = "D://GeneratorCode"+"//" + "procedure.sql";%>
<% if(!System.IO.Directory.Exists(OutputFile)) System.IO.Directory.CreateDirectory(ReturnDirectory(OutputFile));%>
<%-- <%=ReturnDirectory(OutputFile)%>--%>

<%System.Diagnostics.Process.Start(ReturnDirectory(OutputFile));%> <%-- Open directory --%>
<script runat="template">
// My methods here.
#region setParam
 public string GetSqlParameterStatement(ColumnSchema column)
 {
       string param = "@" + column.Name + " " + column.NativeType;
 
       switch (column.DataType)
       {
             case DbType.Decimal:
             {
                   param += "(" + column.Precision + ", " + column.Scale + ")";
                   break;
             }
   case DbType.Int32:  //DbType.int
   case DbType.Int16:  //DbType.smallint
   case DbType.DateTime:  //DbType.DateTime
   case DbType.Byte:     //DbType.tinyint
   case DbType.Double:   //DbType.double/float
   {
    break;
   }
             default:
             {
                   if (column.Size > 0)
                   {
                         param += "(" + column.Size + ")";
                   }
                   break;
             }
       }
// Debugger.Break(); not use bugger
       return param;
 }
#endregion setParam

 </script>
 -----------------------------------------------------------------
 -- Date Created: <%= DateTime.Now.ToLongDateString() %>
 -- Name: procedure
 -- Author: qianjin
 -- Description:prc_Insert<%=SourceTable.Name%>、prc_Delete<%=SourceTable.Name%>、prc_Update<%=SourceTable.Name%>、prc_Select<%=SourceTable.Name%>、
 ----------------------------------------------------------------- ------------------------------------------------------------------------------------
---------------------------------------INSERT--------------------------------------- 
if exists(select *from sysobjects where name=prc_Insert<%=SourceTable.Name%>) 
 drop procedure prc_Insert<%=SourceTable.Name%>
go
 CREATE PROCEDURE prc_Insert<%=SourceTable.Name%>
        <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
        <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
        <% } %>
 AS
  begin
  INSERT INTO <%=SourceTable.Name%>(
   <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
   <%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
   <% } %>
  )
  VALUES(
   <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
   @<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
   <% } %>
  )   
 end
go
---------------------------------------DELETE--------------------------------------- 
if exists(select *from sysobjects where name=prc_Delete<%=SourceTable.Name%>) 
 drop procedure prc_Delete<%=SourceTable.Name%>
go
 CREATE PROCEDURE prc_Delete<%=SourceTable.Name%>
        <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
        <%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) { %>,<% } %>
        <% } %>
 AS
  begin
  DELETE FROM <%=SourceTable.Name%>
  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 %>
   <% } %>   
 end
go
---------------------------------------UPDATE---------------------------------------
if exists(select *from sysobjects where name=prc_Update<%=SourceTable.Name%>) 
 drop procedure prc_Update<%=SourceTable.Name%>
go
 CREATE PROCEDURE prc_Update<%=SourceTable.Name%>
       <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
       <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
       <% } %>
 AS
  begin
  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 %>
   <% } %>
 end
go
---------------------------------------SELECT---------------------------------------
if exists(select *from sysobjects where name=prc_Select<%=SourceTable.Name%>) 
 drop procedure prc_Select<%=SourceTable.Name%>
go
 CREATE PROCEDURE prc_Select<%=SourceTable.Name%>
       <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
       <%= GetSqlParameterStatement(SourceTable.PrimaryKey.MemberColumns[i]) %><% if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) { %>,<% } %>
       <% } %>
 AS
  begin
  SELECT <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
      <%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
      <% } %>
  FROM <%= SourceTable.Name %>
   
  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 %>
   <% } %>
 end
go