1 <%@ CodeTemplate Debug="False" Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL" CompilerVersion="v3.5"  2     Description="Generates standard CRUD procedures based on a database table schema." %>  3 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="表名" %>  4 <%@ Property Name="IncludeDropStatements" Type="System.Boolean" Default="True" Category="Options" Description="如果为true，则生成已存在存储过程的脚本" %>  5 <%@ Property Name="ProcedurePrefix" Type="System.String" Default="usp" Category="Options" Description="所有生成存储过程的前缀。" %>  6 <%@ Property Name="IncludeInsert" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成增加存储过程。" %>  7 <%@ Property Name="IncludeUpdate" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据主键更新的存储过程。" %>  8 <%@ Property Name="IncludeInsertUpdate" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成增加更新存储过程。" %>  9 <%@ Property Name="IncludeDelete" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据主键删除的存储过程。" %> 10 <%@ Property Name="IncludeDeleteByForeignKey" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据外键删除的存储过程。" %> 11 <%@ Property Name="IncludeDeleteByIndex" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据索引删除的存储过程。" %> 12 <%@ Property Name="IncludeSelect" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据主键查询的存储过程。" %> 13 <%@ Property Name="IncludeSelectAll" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成查询所有记录的存储过程。" %> 14 <%@ Property Name="IncludeSelectByForeignKey" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据外键查询的存储过程。" %> 15 <%@ Property Name="IncludeSelectByIndex" Type="System.Boolean" Default="True" Category="Procedure Types" Description="如果为true，则生成根据索引查询的存储过程。" %> 16 <%@ Property Name="InsertSufffix" Type="System.String" Default="Insert" Category="Style" Description="生成增加存储过程的后缀。" %> 17 <%@ Property Name="UpdateSufffix" Type="System.String" Default="Update" Category="Style" Description="生成根据主键更新存储过程的后缀。" %> 18 <%@ Property Name="InsertUpdateSufffix" Type="System.String" Default="InsertUpdate" Category="Style" Description="生成增加更新存储过程的后缀。" %> 19 <%@ Property Name="DeleteSufffix" Type="System.String" Default="Delete" Category="Style" Description="生成根据主键删除存储过程的后缀。" %> 20 <%@ Property Name="SelectSufffix" Type="System.String" Default="Select" Category="Style" Description="生成根据主键查询存储过程的后缀。" %> 21 <%@ Template Language="C#" TargetLanguage="SQL" %> 22 <%@ Assembly Name="SchemaExplorer"%> 23 <%@ Assembly Name="CodeSmith.BaseTemplates" %> 24 <%@ Assembly Name="CodeSmith.CustomProperties" %> 25 <%@ Assembly Name="System.Data" %> 26 <%@ Import Namespace="SchemaExplorer" %> 27 <%@ Import Namespace="CodeSmith.CustomProperties" %> 28 <%@ Import Namespace="CodeSmith.BaseTemplates" %> 29 <%@ Import Namespace="System.Data" %> 30 <%@ Import Namespace="System.Text.RegularExpressions" %> 31 <%@ Import Namespace="System.Collections.Generic" %> 32 <%------------------------------------------------------------------------------------------ 33 * 34 * Insert Procedure 35 * 36 ------------------------------------------------------------------------------------------%> 37 <% 38 if (IncludeInsert) 39 { 40     if(IncludeDropStatements) 41     { 42         GenerateProcedureHeader(GetInsertProcedureName()); 43     } 44 %> 45 CREATE PROCEDURE <%= GetInsertProcedureName() %> 46 <% GenerateParameters(SourceTable.Columns, 1); %> 47 AS 48 INSERT INTO <%= GetTableName() %> ( 49     <% GenerateColumns(SourceTable.Columns, 1); %> 50 ) VALUES ( 51     <% GenerateVariables(SourceTable.Columns, 1); %> 52 ) 53 <% GenerateProcedureFooter(); 54 }%> 55 <%------------------------------------------------------------------------------------------ 56 * 57 * Update Procedure 58 * 59 ------------------------------------------------------------------------------------------%> 60 <% 61 if (IncludeUpdate) 62 { 63     if(SourceTable.PrimaryKey.MemberColumns.Count > 0) 64     { 65         if(IncludeDropStatements) 66         { 67             GenerateProcedureHeader(GetUpdateProcedureName()); 68         } 69 %> 70 CREATE PROCEDURE <%= GetUpdateProcedureName() %> 71 <% GenerateParameters(SourceTable.Columns, 1); %> 72 AS 73 UPDATE <%= GetTableName() %> SET 74     <% GenerateUpdates(SourceTable.NonPrimaryKeyColumns, 1); %> 75 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %> 76 <% GenerateProcedureFooter(); 77     } 78 }%> 79 <%------------------------------------------------------------------------------------------ 80 * 81 * InsertUpdate Procedure 82 * 83 ------------------------------------------------------------------------------------------%> 84 <% 85 if (IncludeInsertUpdate) 86 { 87     if(SourceTable.PrimaryKey.MemberColumns.Count > 0) 88     { 89         if(IncludeDropStatements) 90         { 91             GenerateProcedureHeader(GetInsertUpdateProcedureName()); 92         } 93 %> 94 CREATE PROCEDURE <%= GetInsertUpdateProcedureName() %> 95 <% GenerateParameters(SourceTable.Columns, 1); %> 96 AS 97 IF EXISTS(SELECT <% GenerateColumns(SourceTable.PrimaryKey.MemberColumns, -1); %> FROM <%= GetTableName() %> WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %>) 98 BEGIN 99     UPDATE <%= GetTableName() %> SET100         <% GenerateUpdates(SourceTable.NonPrimaryKeyColumns, 2); %>101     WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %>102 END103 ELSE104 BEGIN105     INSERT INTO <%= GetTableName() %> (106         <% GenerateColumns(SourceTable.Columns, 2); %>107     ) VALUES (108         <% GenerateVariables(SourceTable.Columns, 2); %>109     )110 END111 <% GenerateProcedureFooter();112     }113 }%>114 <%------------------------------------------------------------------------------------------115 *116 * Delete Procedure117 *118 ------------------------------------------------------------------------------------------%>119 <%120 if (IncludeDelete)121 {122     if(SourceTable.PrimaryKey.MemberColumns.Count > 0)123     {124         if(IncludeDropStatements)125         {126             GenerateProcedureHeader(GetDeleteProcedureName());127         }128 %>129 CREATE PROCEDURE <%= GetDeleteProcedureName() %>130 <% GenerateParameters(SourceTable.PrimaryKey.MemberColumns, 1); %>131 AS132 DELETE FROM <%= GetTableName() %>133 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %>134 <% GenerateProcedureFooter();135     }136 }%>137 <%------------------------------------------------------------------------------------------138 *139 * Delete By Foreign Key Procedures140 *141 ------------------------------------------------------------------------------------------%>142 <%143 if (IncludeDeleteByForeignKey)144 {145     if(SourceTable.ForeignKeyColumns.Count > 0)146     {147         if(IncludeDropStatements)148         {149             GenerateProcedureHeader(GetSelectByProcedureName(SourceTable.ForeignKeyColumns));150         }151 %>152 CREATE PROCEDURE  <%= GetDeleteByProcedureName(SourceTable.ForeignKeyColumns) %>153 <% GenerateParameters(SourceTable.ForeignKeyColumns, 1); %>154 AS155 DELETE FROM <%= GetTableName() %>156 WHERE <% GenerateConditions(SourceTable.ForeignKeyColumns, -1); %>157 <% GenerateProcedureFooter();158     }159 }%>160 <%------------------------------------------------------------------------------------------161 *162 * Delete By Index Procedures163 *164 ------------------------------------------------------------------------------------------%>165 <%166 if (IncludeDeleteByIndex)167 {168     if(SourceTable.Indexes.Count > 0)169     {170         foreach(IndexSchema index in SourceTable.Indexes)171         {172             string procedureName = GetDeleteByProcedureName(index.MemberColumns);173             if (!_generatedProcedureNames.Contains(procedureName))174             {175                 _generatedProcedureNames.Add(procedureName);176                 if(IncludeDropStatements)177                 {178                     GenerateProcedureHeader(procedureName);179                 }180 %>181 CREATE PROCEDURE <%= procedureName %>182 <% GenerateParameters(index.MemberColumns, 1); %>183 AS184 DELETE FROM <%= GetTableName() %>185 WHERE <% GenerateConditions(index.MemberColumns, -1);%>186 <% GenerateProcedureFooter();187             }188         }189     }190 }%>191 <%------------------------------------------------------------------------------------------192 *193 * Select Procedure194 *195 ------------------------------------------------------------------------------------------%>196 <%197 if (IncludeSelect)198 {199     if(SourceTable.PrimaryKey.MemberColumns.Count > 0)200     {201         if(IncludeDropStatements)202         {203             GenerateProcedureHeader(GetSelectProcedureName());204         }205 %>206 CREATE PROCEDURE <%= GetSelectProcedureName() %>207 <% GenerateParameters(SourceTable.PrimaryKey.MemberColumns, 1); %>208 AS209 SELECT210     <% GenerateColumns(SourceTable.Columns, 1); %>211 FROM <%= GetTableName() %>212 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %>213 <% GenerateProcedureFooter();214     }215 }%>216 <%------------------------------------------------------------------------------------------217 *218 * Select All Procedure219 *220 ------------------------------------------------------------------------------------------%>221 <%222 if (IncludeSelectAll)223 {224     if(IncludeDropStatements)225     {226         GenerateProcedureHeader(GetSelectAllProcedureName());227     }228 %>229 CREATE PROCEDURE <%= GetSelectAllProcedureName() %>230 AS231 SELECT232     <% GenerateColumns(SourceTable.Columns, 1); %>233 FROM <%= GetTableName() %>234 <% GenerateProcedureFooter();235 }%>236 <%------------------------------------------------------------------------------------------237 *238 * Select By Foreign Key Procedures239 *240 ------------------------------------------------------------------------------------------%>241 <%242 if (IncludeSelectByForeignKey)243 {244     if(SourceTable.ForeignKeyColumns.Count > 0)245     {246         if(IncludeDropStatements)247         {248             GenerateProcedureHeader(GetSelectByProcedureName(SourceTable.ForeignKeyColumns));249         }250 %>251 CREATE PROCEDURE <%= GetSelectByProcedureName(SourceTable.ForeignKeyColumns) %>252 <% GenerateParameters(SourceTable.ForeignKeyColumns, 1); %>253 AS254 SELECT255     <% GenerateColumns(SourceTable.Columns, 1); %>256 FROM <%= GetTableName() %>257 WHERE <% GenerateConditions(SourceTable.ForeignKeyColumns, -1); %>258 <% GenerateProcedureFooter();259     }260 }%>261 <%------------------------------------------------------------------------------------------262 *263 * Select By Index Procedures264 *265 ------------------------------------------------------------------------------------------%>266 <%267 if (IncludeSelectByIndex)268 {269     if(SourceTable.Indexes.Count > 0)270     {271         foreach(IndexSchema index in SourceTable.Indexes)272         {273             string procedureName = GetSelectByProcedureName(index.MemberColumns);274             if (!_generatedProcedureNames.Contains(procedureName))275             {276                 _generatedProcedureNames.Add(procedureName);277                 if(IncludeDropStatements)278                 {279                     GenerateProcedureHeader(procedureName);280                 }281 %>282 CREATE PROCEDURE <%= procedureName %>283 <% GenerateParameters(index.MemberColumns, 1); %>284 AS285 SELECT286     <% GenerateColumns(SourceTable.Columns, 1); %>287 FROM <%= GetTableName() %>288 WHERE <% GenerateConditions(index.MemberColumns, -1);%>289 <% GenerateProcedureFooter();290             }291         }292     }293 }%>294 <script runat="template">295 296 #region     成员变量297 298 private StringCollection _generatedProcedureNames = new StringCollection();299 300 #endregion301 302 #region    辅助方法303      304 public string GetTableOwner()305 {306     if (SourceTable.Owner.Length > 0)return "[" + SourceTable.Owner + "].";307     return "";308 }309 310 public string GetTableName()311 {312     return GetTableOwner()+"[" + SourceTable.Name + "]";313 }314 315 public void GenerateProcedureHeader(string procedureName)316 {317     Response.WriteLine("IF OBJECT_ID(N‘{0}‘) IS NOT NULL", procedureName);318     GenerateIndent(1);319     Response.WriteLine("DROP PROCEDURE {0}", procedureName);320     Response.WriteLine("");321     Response.WriteLine("GO");322     Response.WriteLine("");323     Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");324     Response.WriteLine("-- 创 建 人: {0}", System.Environment.UserName);325     Response.WriteLine("-- 创建时间: {0}", DateTime.Now.ToLongDateString());326     Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");327 }328 329 public void GenerateProcedureFooter()330 {331     Response.WriteLine("");332     Response.WriteLine("GO");333     Response.WriteLine("");334 }335 336 public void GenerateIndent(int indentLevel)337 {338     for (int i = 0; i < indentLevel; i++)339     {340         Response.Write(‘\t‘);341     }342 }343 344 #endregion345 346 #region    存储过程命名347 348 public string GetInsertProcedureName()349 {350     return String.Format("{0}[{1}{2}{3}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), InsertSufffix);351 }352 353 public string GetUpdateProcedureName()354 {355     return String.Format("{0}[{1}{2}{3}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), UpdateSufffix);356 }357 358 public string GetInsertUpdateProcedureName()359 {360     return String.Format("{0}[{1}{2}{3}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), InsertUpdateSufffix);361 }362 363 public string GetDeleteProcedureName()364 {365     return String.Format("{0}[{1}{2}{3}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), DeleteSufffix);366 }367 368 public string GetDeleteByProcedureName(IList<ColumnSchema> targetColumns)369 {370     return String.Format("{0}[{1}{2}{3}By{4}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), DeleteSufffix, GetBySuffix(targetColumns));371 }372 373 public string GetDeleteByProcedureName(IList<MemberColumnSchema> targetColumns)374 {375     return GetDeleteByProcedureName(ConvertMemberColumnSchemaToColumnSchema(targetColumns));376 }377 378 public string GetSelectProcedureName()379 {380     return String.Format("{0}[{1}{2}{3}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), SelectSufffix);381 }382 383 public string GetSelectAllProcedureName()384 {385     return String.Format("{0}[{1}{2}{3}All]",GetTableOwner(), ProcedurePrefix , GetEntityName(), SelectSufffix);386 }387 388 public string GetSelectByProcedureName(IList<ColumnSchema> targetColumns)389 {390     return String.Format("{0}[{1}{2}{3}By{4}]",GetTableOwner(), ProcedurePrefix , GetEntityName(), SelectSufffix, GetBySuffix(targetColumns));391 }392 393 public string GetSelectByProcedureName(IList<MemberColumnSchema> targetColumns)394 {395     return GetSelectByProcedureName(ConvertMemberColumnSchemaToColumnSchema(targetColumns));396 }397 398 public string GetEntityName()399 {400     string entityName = SourceTable.Name;401     return StringUtil.ToPascalCase(entityName);402 }403 404 public string GetBySuffix(IList<ColumnSchema> columns)405 {406     System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();407     foreach(var column in columns.AsIndexedEnumerable())408     {409         if (!column.IsFirst) bySuffix.Append("And");410         bySuffix.Append(StringUtil.ToPascalCase(column.Value.Name));411     }412     413     return bySuffix.ToString();414 }415 416 #endregion417 418 #region    公共方法419 420 public void GenerateParameters(IList<ColumnSchema> columns, int indentLevel)421 {422     foreach(var column in columns.AsIndexedEnumerable())423     {424         GenerateIndent(indentLevel);425         Response.Write(GetSqlParameterStatement(column.Value));426         if (!column.IsLast) Response.Write(",");427         if (indentLevel >= 0)428         {429             Response.WriteLine("--" + column.Value.Description);430         }431         else if (!column.IsLast)432         {433             Response.Write(" ");434         }435     }436 }437 438 public void GenerateParameters(IList<MemberColumnSchema> columns, int indentLevel)439 {440     GenerateParameters(ConvertMemberColumnSchemaToColumnSchema(columns),indentLevel);441 }442 443 public void GenerateColumns(IList<ColumnSchema> columns, int indentLevel)444 {445     foreach(var column in columns.AsIndexedEnumerable())446     {447         GenerateIndent(indentLevel);448         Response.Write("[");449         Response.Write(column.Value.Name);450         Response.Write("]");451         if (!column.IsLast) Response.Write(",");452         if (indentLevel >= 0)453         {454             Response.WriteLine("");455         }456         else if (!column.IsLast)457         {458             Response.Write(" ");459         }460     }461 }462 463 public void GenerateColumns(IList<MemberColumnSchema> columns, int indentLevel)464 {465     GenerateColumns(ConvertMemberColumnSchemaToColumnSchema(columns),indentLevel);466 }467 468 public void GenerateVariables(IList<ColumnSchema> columns, int indentLevel)469 {470     foreach(var column in columns.AsIndexedEnumerable())471     {472         GenerateIndent(indentLevel);473         Response.Write("@");474         Response.Write(column.Value.Name);475         if (!column.IsLast) Response.Write(",");476         if (indentLevel >= 0)477         {478             Response.WriteLine("");479         }480         else if (!column.IsLast)481         {482             Response.Write(" ");483         }484     }485 }486 487 public void GenerateUpdates(IList<ColumnSchema> columns, int indentLevel)488 {489     foreach(var column in columns.AsIndexedEnumerable())490     {491         GenerateIndent(indentLevel);492         Response.Write("[");493         Response.Write(column.Value.Name);494         Response.Write("] = @");495         Response.Write(column.Value.Name);496         if (!column.IsLast) Response.Write(",");497         if (indentLevel >= 0)498         {499             Response.WriteLine("");500         }501         else if (!column.IsLast)502         {503             Response.Write(" ");504         }505     }506 }507 508 public void GenerateConditions(IList<ColumnSchema> columns, int indentLevel)509 {510     foreach(var column in columns.AsIndexedEnumerable())511     {512         if (!column.IsFirst) Response.Write("AND ");513         Response.Write("[");514         Response.Write(column.Value.Name);515         Response.Write("] = @");516         Response.Write(column.Value.Name);517         if (indentLevel >= 0)518         {519             Response.WriteLine("");520         }521         else if (!column.IsLast)522         {523             Response.Write(" ");524         }525     }526 }527 528 public void GenerateConditions(IList<MemberColumnSchema> columns, int indentLevel)529 {530     GenerateConditions(ConvertMemberColumnSchemaToColumnSchema(columns), indentLevel);531 }532 533 private IList<ColumnSchema> ConvertMemberColumnSchemaToColumnSchema(IList<MemberColumnSchema> memberColumns)534 {535     var columns = new List<ColumnSchema>();536     columns.AddRange(memberColumns.Cast<ColumnSchema>());537     return columns;538 }539 540 #endregion541 542 #region 重写方法543 544 public override string GetFileName()545 {546     if (this.SourceTable != null)547     {548         return StringUtil.ToPascalCase(this.SourceTable.Name) + "CreateProcedures.sql";549     }550     else551     {552         return base.GetFileName();553     }554 }555 556 [Category("Options")]557 [FileDialog(FileDialogType.Save, Title="请选择", Filter="脚本文件 (*.sql)|*.sql|All Files (*.*)|*.*", DefaultExtension=".sql")]558 public override string OutputFile559 {560     get {return base.OutputFile;}561     set {base.OutputFile = value;}562 }563 564 #endregion565 566 </script>