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() %> SET 100 <% GenerateUpdates(SourceTable.NonPrimaryKeyColumns, 2); %> 101 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %> 102 END 103 ELSE 104 BEGIN 105 INSERT INTO <%= GetTableName() %> ( 106 <% GenerateColumns(SourceTable.Columns, 2); %> 107 ) VALUES ( 108 <% GenerateVariables(SourceTable.Columns, 2); %> 109 ) 110 END 111 <% GenerateProcedureFooter(); 112 } 113 }%> 114 <%------------------------------------------------------------------------------------------ 115 * 116 * Delete Procedure 117 * 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 AS 132 DELETE FROM <%= GetTableName() %> 133 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %> 134 <% GenerateProcedureFooter(); 135 } 136 }%> 137 <%------------------------------------------------------------------------------------------ 138 * 139 * Delete By Foreign Key Procedures 140 * 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 AS 155 DELETE FROM <%= GetTableName() %> 156 WHERE <% GenerateConditions(SourceTable.ForeignKeyColumns, -1); %> 157 <% GenerateProcedureFooter(); 158 } 159 }%> 160 <%------------------------------------------------------------------------------------------ 161 * 162 * Delete By Index Procedures 163 * 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 AS 184 DELETE FROM <%= GetTableName() %> 185 WHERE <% GenerateConditions(index.MemberColumns, -1);%> 186 <% GenerateProcedureFooter(); 187 } 188 } 189 } 190 }%> 191 <%------------------------------------------------------------------------------------------ 192 * 193 * Select Procedure 194 * 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 AS 209 SELECT 210 <% GenerateColumns(SourceTable.Columns, 1); %> 211 FROM <%= GetTableName() %> 212 WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %> 213 <% GenerateProcedureFooter(); 214 } 215 }%> 216 <%------------------------------------------------------------------------------------------ 217 * 218 * Select All Procedure 219 * 220 ------------------------------------------------------------------------------------------%> 221 <% 222 if (IncludeSelectAll) 223 { 224 if(IncludeDropStatements) 225 { 226 GenerateProcedureHeader(GetSelectAllProcedureName()); 227 } 228 %> 229 CREATE PROCEDURE <%= GetSelectAllProcedureName() %> 230 AS 231 SELECT 232 <% GenerateColumns(SourceTable.Columns, 1); %> 233 FROM <%= GetTableName() %> 234 <% GenerateProcedureFooter(); 235 }%> 236 <%------------------------------------------------------------------------------------------ 237 * 238 * Select By Foreign Key Procedures 239 * 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 AS 254 SELECT 255 <% GenerateColumns(SourceTable.Columns, 1); %> 256 FROM <%= GetTableName() %> 257 WHERE <% GenerateConditions(SourceTable.ForeignKeyColumns, -1); %> 258 <% GenerateProcedureFooter(); 259 } 260 }%> 261 <%------------------------------------------------------------------------------------------ 262 * 263 * Select By Index Procedures 264 * 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 AS 285 SELECT 286 <% 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 #endregion 301 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 #endregion 345 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 #endregion 417 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 #endregion 541 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 else 551 { 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 OutputFile 559 { 560 get {return base.OutputFile;} 561 set {base.OutputFile = value;} 562 } 563 564 #endregion 565 566 </script>
87
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
