随笔- 210 文章- 0 评论- 1680 阅读- 119万

CodeSmith系列（一）——使用CodeSmith生成存储过程

在工作中经常会碰到重复编程的情况。这些代码要么是完全重复的，要么是有规律的。但是手写起来都需要耗费时间，而且很容易出错。最近就碰到了几件，于是想到了利用CodeSmith来写模版生成，发现是一件很不错的事。

比如有这么一个需求，有很多表，每张表都有一张日志表，需要有这么个存储过程来更新日志表的数据。定义模版如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

<%-- 
Author: LWQ
Description: 生成LOG表的存储过程
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="生成Insert存储过程." ResponseEncoding="UTF-8"  %>
<%--加载使用访问数据库的组件SchemaExplorer，并声明其使用的命名空间。--%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%--定义一个存储表名称使用的变量，然后指明这个变量类型为数据库中的表--%>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context"  Description="要生成的表" %>
<%@ Property Name="LogTable" Type="SchemaExplorer.TableSchema" Category="Context"  Description="日志表" %>
<%@ Property Name="DBName"            Type="String"                       Category="New"      Optional="True" Description="数据库名称" %>
<%@ Property Name="ProcName"          Type="String"                       Category="New"      Optional="True" Description="存储过程名称" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<% 
        //设置存储过程名称
        this.ProcName="dbo.Insert"+ SourceTable.Name +"AndSetLog";
        this.DBName = SourceTable.Database.Name;
%>
/*
 * 利用CodeSmith生成。模板名：CreateLogStoredProcedure.cst。
 * $Date: <%= DateTime.Now.ToShortDateString() %> 
 * $Author: <%= Environment.UserName %> 
 *
 */
GO
USE <%= DBName %>
GO
--如果存在，则删除
IF OBJECT_ID('<%= ProcName %>') IS NOT NULL
    DROP PROCEDURE <%= ProcName %>
GO
CREATE PROCEDURE <%= ProcName %>
<%-- 设置参数--%>
<% int ignoreColumns=0; %>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
    <%if(!((bool)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value) 
        && SourceTable.Columns[i].Name.ToLower()!="defaultflag") {%>
        <%= GetSqlParameterStatement(SourceTable.Columns[i]) %>
        <% if (i < SourceTable.Columns.Count - 1-ignoreColumns) { %>
        ,
        <% } %>
    <% }else{ignoreColumns++;}%>
<% } %>
<%-- 如果日志表存在ChangeType，则添加该参数--%>
<% if(LogTable.Columns.Contains("ChangeType")){ %>
    <%= ","+ GetSqlParameterStatement(LogTable.Columns["ChangeType"]) %>
<% }%>
 
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION    
    DECLARE @errorSun INT
    SET @errorSun=0
     
    --更新<%=SourceTable.Name %>表状态为0
    <%-- 如果原表存在DefaulFlag，则更新所有表的DefaulFlag状态为0--%>
    <% if(SourceTable.Columns.Contains("DefaultFlag")){ %>
        Update <%=SourceTable.Name %>
        Set [DefaulFlag]=0
        Where <%=SourceTable.Columns.Contains("ShopID")?"ShopID=@ShopID And ":string.Empty %><%=SourceTable.Columns.Contains("ObjectID")?"ObjectID=@ObjectID And ":string.Empty %>
    <% }%>
     SET @errorSun=@errorSun+@@ERROR 
     
    --向<%=SourceTable.Name %>表插入数据,DefaultFlag设置为1
    INSERT INTO [<%= SourceTable.Name %>]
                (
                    <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
                        <%-- 判断是否标识列，如果是，则不输出列名--%>
                        <%if(!((bool)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value)) {%>
                            [<%=SourceTable.Columns[i].Name %>]
                            <% if (i < SourceTable.Columns.Count - ignoreColumns) { %>,<% } %>
                        <% } %>
                    <% }%>
                )
     VALUES
            (
                <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
                    <% if(SourceTable.Columns[i].Name.ToLower()=="defaultflag")  { %>
                        <%= "1"%>
                        <% if (i < LogTable.Columns.Count - 1-ignoreColumns) { %>,<% } %>
                    <% continue; }%>
                    <%if(!((bool)SourceTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value)) {%>
                        <%= "@"+ SourceTable.Columns[i].Name%>
                        <% if (i < SourceTable.Columns.Count - 1-ignoreColumns) { %>,<% } %>
                    <% }%>
                <% } %>
            )
    SET @errorSun=@errorSun+@@ERROR 
     
    <% int ignoreColumnsForLog=0; %>
    --向<%=LogTable.Name %>表插入数据,DefaultFlag设置为0
    INSERT INTO [<%= LogTable.Name %>]
                (
                    <% for (int i = 0; i < LogTable.Columns.Count; i++) { %>
                        <%-- 判断是否标识列，如果是，则不输出列名--%>
                        <%if(!((bool)LogTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value)) {%>
                            [<%=LogTable.Columns[i].Name %>]
                            <% if (i < LogTable.Columns.Count - 1 - ignoreColumnsForLog) { %>,<% } %>
                        <% }else {ignoreColumnsForLog++;} %>
                    <% }%>
                )
     VALUES
            (
                <% for (int i = 0; i < LogTable.Columns.Count; i++) { %>
                    <%if(!((bool)LogTable.Columns[i].ExtendedProperties["CS_IsIdentity"].Value)) {%>
                         <% if(LogTable.Columns[i].Name.ToLower()=="defaultflag")  { %>
                            <%= "0"%>
                            <% if (i < LogTable.Columns.Count - 1-ignoreColumnsForLog) { %>,<% } %>
                        <% continue; }%>
                        <%= "@"+ LogTable.Columns[i].Name%>
                        <% if (i < LogTable.Columns.Count - 1-ignoreColumnsForLog) { %>,<% } %>
                    <% }%>
                <% } %>
            )
             
    SET @errorSun=@errorSun+@@ERROR 
    IF @errorSun<>0
    BEGIN
        ROLLBACK TRANSACTION 
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION 
    END
END
<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>