CodeSmith生成insert和update存储过程

<%@ CodeTemplate Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL" Description="编辑用的存储过程，包括添加、修改和删除"  Debug="False" ResponseEncoding="UTF-8"%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>

<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="数据" Description="要进行操作的表" %>
<%@ Property Name="IncludeDrop" Type="System.Boolean" Default="False" Category="选项" Description="是否需要先Drop掉以前的存储过程" %>
<%@ Property Name="IncludeInsert" Type="System.Boolean" Default="True" Category="选项" Description="插入数据" %>
<%@ Property Name="IncludeUpdate" Type="System.Boolean" Default="True" Category="选项" Description="更新数据" %>

<% 
if(false)
{
%>
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
        <%= GetSqlParameterStatement(SourceTable.Columns[i]) %> <% if ((i < SourceTable.Columns.Count - 1)) { %>,<% } %>  <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
    <%
    }
    %>
    
    Update <%= GetTableOwner() %>[<%= 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) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
    <% } %>
    
    Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>] 
        (
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
            [<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
    <% } %>
        ) 
        Values 
        (
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
            @<%= SourceTable.Columns[i].Name %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if(SourceTable.Columns[i].Description.Length > 0) { %>--<%= SourceTable.Columns[i].Description %><% } %>
    <% } %>
        )
<%    
    return;
} 
%>
    
<% if (SourceTable.PrimaryKeys == null) throw new ApplicationException("该表缺少主键。"); %>

<% if (IncludeDrop) { %>
    <% if (IncludeInsert) { %>
/****** Object:  Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Insert    Script Date:  ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
GO

    <% } %>
    
    <% if (IncludeUpdate) { %>
/****** Object:  Stored Procedure <%= GetTableOwner() %><%= SourceTable.Name %>_Update    Script Date:  ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= SourceTable.Name %>_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
GO

    <% } %>
    
<%}%>


<% if (IncludeInsert) { %>
/*
******************************************************************************************
  过程名称：<%= SourceTable.Name %>_Insert
  设计时间：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************

<% if (SourceTable.PrimaryKey.MemberColumns.Count == 1 && (SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Guid || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int16 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int32 || SourceTable.PrimaryKey.MemberColumns[0].DataType == DbType.Int64)) {
    ColumnSchema primaryKey = SourceTable.PrimaryKey.MemberColumns[0];
%>
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Insert]
    <%= GetSqlParameterStatement(primaryKey, true) %>,
    <% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
    <%= GetSqlParameterStatement(SourceTable.NonPrimaryKeyColumns[i]) %><%if (i < SourceTable.NonPrimaryKeyColumns.Count - 1){%>,<%}%>        <% if(SourceTable.NonPrimaryKeyColumns[i].Description.Length > 0) { %>--<%= SourceTable.NonPrimaryKeyColumns[i].Description %><% } %>
    <% } %>
AS
    <% if (primaryKey.DataType == DbType.Guid) { %>
    Set @<%= primaryKey.Name %> = NEWID()
    <% } %>
    Insert Into <%= GetTableOwner() %>[<%= SourceTable.Name %>] 
        (
    <% if (primaryKey.DataType == DbType.Guid) { %>
            [<%= primaryKey.Name %>],
    <% } %>
    <% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
            [<%= SourceTable.NonPrimaryKeyColumns[i].Name %>]<% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
    <% } %>
        ) 
        Values 
        (
    <% if (primaryKey.DataType == DbType.Guid) { %>
            @<%= primaryKey.Name %>,
    <% } %>
    <% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
            @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
    <% } %>
        )
    <% if (primaryKey.DataType == DbType.Int16 || primaryKey.DataType == DbType.Int32 || primaryKey.DataType == DbType.Int64) { %>
    Set @<%= primaryKey.Name %> = @@IDENTITY
    <% } %>
    Return @@IDENTITY
<% } else { %>
Create Procedure <%= GetTableOwner() %>[Insert_<%= SourceTable.Name %>]
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
    <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
    <% } %>
AS
    Insert Into <%= GetTableOwner() %>[<%= 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) { %>,<% } %>
    <% } %>
        )
    Return @@IDENTITY
<% } %>
GO
<% } %>

<% if (IncludeUpdate) { %>
/*
******************************************************************************************
  过程名称：<%= SourceTable.Name %>_Update
  设计时间：<%= System.DateTime.Now.ToString("yyyy年MM月dd日 hh:mm:ss") %>
******************************************************************************************
Create Procedure <%= GetTableOwner() %>[<%= SourceTable.Name %>_Update]
    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
    <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if (SourceTable.Columns[i].Description.Length > 0) { %>-- <%= SourceTable.Columns[i].Description %><% } %>
    <% } %>
AS
    Update <%= GetTableOwner() %>[<%= 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 %>
    <% } %>
    Return @@ROWCOUNT
GO
<% } %>

<script runat="template">
// My methods here.

public string GetTableOwner()
{
    return GetTableOwner(true);
}

public string GetTableOwner(bool includeDot)
{
    if (SourceTable.Owner.Length > 0)
    {
        return "[" + SourceTable.Owner + "].";
    }
    else
    {
        return "";
    }
}
</script>