Entities.cst:
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Src="" Inherits="" Debug="False" Description="Template description here." ResponseEncoding="UTF-8" %> <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Default="" Optional="False" Category="Table" Description="源表名" OnChanged="" Editor="" EditorBase="" Serializer="" %> <%@ Property Name="NameSpace" Type="System.String" Default="DAL" Optional="False" Category="NameSpace" Description="命名空间" OnChanged="" Editor="" EditorBase="" Serializer="" %> <%@ Property Name="IsFK" Type="System.Boolean" Default="False" Optional="False" Category="Other" Description="是否处理外键" OnChanged="" Editor="" EditorBase="" Serializer="" %> <%@ Property Name="Author" Type="System.String" Default="ahjesus" Optional="False" Category="Other" Description="" OnChanged="" Editor="" EditorBase="" Serializer="" %> <%@ Assembly Name="SchemaExplorer" %> <%@ Assembly Name="System.Data" %> <%@ Assembly Name="mscorlib" %> <%@ Import Namespace="SchemaExplorer" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Collections.Generic" %> using System; using System.Collections.Generic; using System.Text; namespace <%=this.NameSpace%> { /// <summary> /// 实体类<%=this.GetClassName()%> /// </summary> public class <%=this.GetClassName()%> { #region 公开属性 <%foreach(ColumnSchema column in this.SourceTable.ForeignKeyColumns){%> <%if(!IsFK){%> /// <summary> /// <%=GetDiscription(column)%> /// </summary> public <%=this.GetCSharpVariableType(column)%> <%=this.ToPascal(column.Name)%> { get; set; } <%}else{%> /// <summary> /// <%=GetDiscription(column)%> /// </summary> public <%=this.GetFKClassName(column)%> <%=this.ToPascal(column.Name)%> { get; set; } <%}%> <%}%> <%foreach(ColumnSchema column in this.SourceTable.NonForeignKeyColumns){%> /// <summary> /// <%=GetDiscription(column)%> /// </summary> public <%=this.GetCSharpVariableType(column)%> <%=this.ToPascal(column.Name)%> { get; set; } <%}%> #endregion } } <script runat="template"> #region Pascal命名法 public string ToPascal(string s) { return s.Substring(0,1).ToUpper()+s.Substring(1); } #endregion #region 骆驼命名法 public string ToCamel(string s) { return s.Substring(0,1).ToLower()+s.Substring(1); } #endregion #region 获取实体类类名 public string GetClassName() { string s=this.SourceTable.Name; if(s.EndsWith("s")) { s=s.Substring(0,s.Length-1); } return this.ToPascal(s); } public string GetClassName(TableSchema table) { string s=table.Name; if(s.EndsWith("s")) { s=s.Substring(0,s.Length-1); } return this.ToPascal(s); } #endregion #region 获取实体对象名 public string GetObjectName() { return this.ToCamel(this.GetClassName()); } #endregion #region 获取文件名 public override string GetFileName() { return this.GetClassName()+".cs"; } #endregion #region 获取列的数据类型 public string GetCSharpVariableType(ColumnSchema column) { if (column.Name.EndsWith("TypeCode")) return column.Name; switch (column.DataType) { case DbType.AnsiString: return "string"; case DbType.AnsiStringFixedLength: return "string"; case DbType.Binary: return "byte[]"; case DbType.Boolean: return "bool"; case DbType.Byte: return "byte"; case DbType.Currency: return "decimal"; case DbType.Date: return "DateTime"; case DbType.DateTime: return "DateTime"; case DbType.Decimal: return "decimal"; case DbType.Double: return "double"; case DbType.Guid: return "Guid"; case DbType.Int16: return "short"; case DbType.Int32: return "int"; case DbType.Int64: return "long"; case DbType.Object: return "object"; case DbType.SByte: return "sbyte"; case DbType.Single: return "float"; case DbType.String: return "string"; case DbType.StringFixedLength: return "string"; case DbType.Time: return "TimeSpan"; case DbType.UInt16: return "ushort"; case DbType.UInt32: return "uint"; case DbType.UInt64: return "ulong"; case DbType.VarNumeric: return "decimal"; default: { return "__UNKNOWN__" + column.NativeType; } } } #endregion #region 获取外键类名 public string GetFKClassName(ColumnSchema column) { foreach(TableKeySchema key in this.SourceTable.ForeignKeys) { foreach(MemberColumnSchema fk in key.ForeignKeyMemberColumns) { if(fk.Name==column.Name) { return this.GetClassName(key.PrimaryKeyTable); } } } return ""; } #endregion public string GetDiscription(ColumnSchema column) { return column.Description; } </script>
EntitiesVeiw.cst:
<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Description="Generates a update stored procedure." %> <%@ Assembly Name="SchemaExplorer" %> <%@ Import Namespace="SchemaExplorer" %> <%@ Property Name="SourceTable" Type="SchemaExplorer.ViewSchema" Category="Context" Description="Table that the stored procedures should be based on." %> <%@ Assembly Src="CommonUtility.cs" %> <%@ Import Namespace="Common.Data" %> <script runat="template"> CommonUtility rule=new CommonUtility(); </script> using System; using System.Collections.Generic; using System.Text; namespace SOA.Model { public class <%= SourceTable.Name %> { #region <% for (int i = 0; i < SourceTable.Columns.Count; i++) { %> public <%= rule.GetCSharpVariableType(SourceTable.Columns[i]) %> <%= rule.GetCamelCaseName(SourceTable.Columns[i].Name) %>{get;set;} <% } %> #endregion } }
StoredProcedures.cst:
<%------------------------------------------------------------------------------------------ * Author: Eric J. Smith * Description: This template will generate standard CRUD stored procedures for a given * database table. ------------------------------------------------------------------------------------------%> <%@ CodeTemplate Debug="False" Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL" CompilerVersion="v3.5" Description="Generates standard CRUD procedures based on a database table schema." %> <%-- Context --%> <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="1. Context" Description="Table that the stored procedures should be based on." %> <%-- Options --%> <%@ Property Name="IncludeDropStatements" Type="System.Boolean" Default="True" Category="2. Options" Description="If true drop statements will be generated to drop existing stored procedures." %> <%@ Property Name="IsolationLevel" Type="TransactionIsolationLevelEnum" Default="ReadCommitted" Category="2. Options" Description="Isolation level to use in the generated procedures." %> <%@ Property Name="ProcedurePrefix" Type="System.String" Default="usp_" Category="2. Options" Description="Prefix to use for all generated procedure names." %> <%@ Property Name="TablePrefix" Type="System.String" Default="tbl_" Category="2. Options" Description="If this prefix is found at the start of a table name, it will be stripped off." %> <%@ Property Name="AutoExecuteScript" Type="System.Boolean" Default="False" Category="2. Options" Description="Whether or not to immediately execute the script on the target database." %> <%@ Property Name="OrderByExpression" Type="System.String" Default="" Optional="True" Category="2. Options" Description="If supplied, this expression will be used to apply an order to the results on SELECT statements." %> <%@ Property Name="ExcludedColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options" Description="If supplied, any columns in this list will be excluded from all stored procedures unless the column is part of the primary key. (* is treated as a wildcard)" %> <%@ Property Name="ReadOnlyColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options" Description="If supplied, any columns in this list will be treated as read only. (* is treated as a wildcard)" %> <%-- Procedure Types --%> <%@ Property Name="IncludeInsert" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true an INSERT procedure will be generated." %> <%@ Property Name="IncludeUpdate" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true an UPDATE procedure will be generated." %> <%@ Property Name="IncludeInsertUpdate" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true an INSERT/UPDATE procedure will be generated." %> <%@ Property Name="IncludeDelete" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a DELETE procedure will be generated." %> <%@ Property Name="IncludeSelect" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT procedure will be generated." %> <%@ Property Name="IncludeSelectAll" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT all procedure will be generated." %> <%@ Property Name="IncludeSelectPaged" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT procedure will be generated that allows for server side paging." %> <%@ Property Name="IncludeSelectByForeignKey" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT procedure will be generated for each foreign key." %> <%@ Property Name="IncludeSelectByIndex" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT procedure will be generated for each table index." %> <%@ Property Name="IncludeSelectDynamic" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a SELECT procedure will be generated that allows a dynamic WHERE condition to be used." %> <%@ Property Name="IncludeDeleteByForeignKey" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a DELETE procedure will be generated for each foreign key." %> <%@ Property Name="IncludeDeleteByIndex" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a DELETE procedure will be generated for each table index." %> <%@ Property Name="IncludeDeleteDynamic" Type="System.Boolean" Default="True" Category="3. Procedure Types" Description="If true a DELETE procedure will be generated that allows a dynamic WHERE condition to be used." %> <%-- Assembly References --%> <%@ Assembly Name="SchemaExplorer" %> <%@ Assembly Name="CodeSmith.BaseTemplates" %> <%@ Assembly Name="CodeSmith.CustomProperties" %> <%@ Assembly Name="System.Data" %> <%-- Namespace Imports --%> <%@ Import Namespace="SchemaExplorer" %> <%@ Import Namespace="CodeSmith.CustomProperties" %> <%@ Import Namespace="CodeSmith.BaseTemplates" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Text.RegularExpressions" %> <%@ Import Namespace="System.Collections.Generic" %> <% this.Progress.MaximumValue = 13; this.Progress.Step = 1; // this template requires a primary key on the source table if (!SourceTable.HasPrimaryKey) throw new ApplicationException("SourceTable does not contain a primary key."); // generate drop statements if (IncludeDropStatements) { Response.WriteLine("--region Drop Existing Procedures"); Response.WriteLine(""); if (IncludeInsert) GenerateDropStatement(GetInsertProcedureName()); if (IncludeUpdate) GenerateDropStatement(GetUpdateProcedureName()); if (IncludeInsertUpdate) GenerateDropStatement(GetInsertUpdateProcedureName()); if (IncludeDelete) { GenerateDropStatement(GetDeleteProcedureName()); // this is to keep a drop statement for a delete by procedure with only the primary key columns from being generated _droppedProcedureNames.Add(GetDeleteByProcedureName(SourceTable.PrimaryKey.MemberColumns)); } if (IncludeDeleteDynamic) GenerateDropStatement(GetDeleteDynamicProcedureName()); if (IncludeSelect) { GenerateDropStatement(GetSelectProcedureName()); // this is to keep a drop statement for a select by procedure with only the primary key columns from being generated _droppedProcedureNames.Add(GetSelectByProcedureName(SourceTable.PrimaryKey.MemberColumns)); } if (IncludeSelectDynamic) GenerateDropStatement(GetSelectDynamicProcedureName()); if (IncludeSelectPaged) GenerateDropStatement(GetSelectPagedProcedureName()); if (IncludeSelectAll) GenerateDropStatement(GetSelectAllProcedureName()); if (IncludeSelectByForeignKey) { foreach(TableKeySchema foreignKey in SourceTable.ForeignKeys) { GenerateDropStatement(GetSelectByProcedureName(foreignKey.ForeignKeyMemberColumns)); } } if (IncludeSelectByIndex) { foreach(IndexSchema index in SourceTable.Indexes) { GenerateDropStatement(GetSelectByProcedureName(index.MemberColumns)); } } if (IncludeDeleteByForeignKey) { foreach(TableKeySchema foreignKey in SourceTable.ForeignKeys) { GenerateDropStatement(GetDeleteByProcedureName(foreignKey.ForeignKeyMemberColumns)); } } if (IncludeDeleteByIndex) { foreach(IndexSchema index in SourceTable.Indexes) { GenerateDropStatement(GetDeleteByProcedureName(index.MemberColumns)); } } Response.WriteLine("--endregion"); Response.WriteLine(""); Response.WriteLine("GO"); Response.WriteLine(""); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Insert Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeInsert) { GenerateProcedureHeader(GetInsertProcedureName()); /* * Check to see if the primary key is a single column primary key and also if it's either an * identity column or a GUID. If so, we will not include the primary key column in the * list of input parameters. */ 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) && SourceTable.PrimaryKey.MemberColumns[0].ExtendedProperties != null && SourceTable.PrimaryKey.MemberColumns[0].ExtendedProperties.Contains("CS_IsIdentity") && (bool)SourceTable.PrimaryKey.MemberColumns[0].ExtendedProperties["CS_IsIdentity"].Value == true))) { ColumnSchema primaryKeyColumn = SourceTable.PrimaryKey.MemberColumns[0]; %> CREATE PROCEDURE <%= GetInsertProcedureName() %> <% GenerateParameters(FilterReadOnlyAndExcludedColumns(SourceTable.NonPrimaryKeyColumns), 1, true, false); %> <% GenerateParameter(primaryKeyColumn, 1, false, true, true, false); %> AS SET NOCOUNT ON <%-- If the primary key is a GUID, then assign a new GUID using NEWID(). --%> <% if (primaryKeyColumn.DataType == DbType.Guid) { %> SET @<%= primaryKeyColumn.Name %> = NEWID() <% } %> INSERT INTO <%= GetTableOwner() %>[<%= SourceTable.Name %>] ( <% if (primaryKeyColumn.DataType == DbType.Guid) { %> [<%= primaryKeyColumn.Name %>], <% } %> <% GenerateColumns(FilterReadOnlyAndExcludedColumns(SourceTable.NonPrimaryKeyColumns), 1, false); %> ) VALUES ( <% if (primaryKeyColumn.DataType == DbType.Guid) { %> @<%= primaryKeyColumn.Name %>, <% } %> <% GenerateVariables(FilterReadOnlyAndExcludedColumns(SourceTable.NonPrimaryKeyColumns), 1, false); %> ) <%-- If the primary key is an identity column, then capture the newly assigned identity using SCOPE_IDENTITY(). --%> <% if (primaryKeyColumn.DataType == DbType.Int16 || primaryKeyColumn.DataType == DbType.Int32 || primaryKeyColumn.DataType == DbType.Int64) { %> SET @<%= primaryKeyColumn.Name %> = SCOPE_IDENTITY() <% } %> <%-- Primary key is not a identity column or a GUID, so include all columns as input parameters. --%> <% } else { %> CREATE PROCEDURE <%= GetInsertProcedureName() %> <% GenerateParameters(FilterReadOnlyAndExcludedColumns(SourceTable.Columns), 1, false); %> AS SET NOCOUNT ON INSERT INTO <%= GetTableOwner() %>[<%= SourceTable.Name %>] ( <% GenerateColumns(SourceTable.Columns, 1, false); %> ) VALUES ( <% GenerateVariables(SourceTable.Columns, 1, false); %> ) <% } GenerateProcedureFooter(GetInsertProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Update Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeUpdate) { GenerateProcedureHeader(GetUpdateProcedureName()); %> CREATE PROCEDURE <%= GetUpdateProcedureName() %> <% GenerateParameters(FilterReadOnlyAndExcludedColumns(SourceTable.Columns), 1, false); %> AS SET NOCOUNT ON UPDATE <%= GetTableOwner() %>[<%= SourceTable.Name %>] SET <% GenerateUpdates(SourceTable.NonPrimaryKeyColumns, 1, false); %> WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, 1); %> <% GenerateProcedureFooter(GetUpdateProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * InsertUpdate Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeInsertUpdate) { GenerateProcedureHeader(GetInsertUpdateProcedureName()); %> CREATE PROCEDURE <%= GetInsertUpdateProcedureName() %> <% GenerateParameters(SourceTable.Columns, 1, false); %> AS SET NOCOUNT ON IF EXISTS(SELECT <% GenerateColumns(SourceTable.PrimaryKey.MemberColumns, -1, false); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, -1); %>) BEGIN UPDATE <%= GetTableOwner() %>[<%= SourceTable.Name %>] SET <% GenerateUpdates(SourceTable.NonPrimaryKeyColumns, 2, false); %> WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, 2); %> END ELSE BEGIN INSERT INTO <%= GetTableOwner() %>[<%= SourceTable.Name %>] ( <% GenerateColumns(SourceTable.Columns, 2, false); %> ) VALUES ( <% GenerateVariables(SourceTable.Columns, 2, false); %> ) END <% GenerateProcedureFooter(GetInsertUpdateProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Delete Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeDelete) { // this is to keep a delete by procedure with only the primary key columns from being generated _generatedProcedureNames.Add(GetDeleteByProcedureName(SourceTable.PrimaryKey.MemberColumns)); GenerateProcedureHeader(GetDeleteProcedureName()); %> CREATE PROCEDURE <%= GetDeleteProcedureName() %> <% GenerateParameters(SourceTable.PrimaryKey.MemberColumns, 1, true); %> AS SET NOCOUNT ON DELETE FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, 1); %> <% GenerateProcedureFooter(GetDeleteProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Delete By Foreign Key Procedures * ------------------------------------------------------------------------------------------%> <% if (IncludeDeleteByForeignKey) { foreach(TableKeySchema foreignKey in SourceTable.ForeignKeys) { string procedureName = GetDeleteByProcedureName(foreignKey.ForeignKeyMemberColumns); if (!_generatedProcedureNames.Contains(procedureName)) { _generatedProcedureNames.Add(procedureName); GenerateProcedureHeader(procedureName); %> CREATE PROCEDURE <%= procedureName %> <% GenerateParameters(foreignKey.ForeignKeyMemberColumns, 1, true); %> AS SET NOCOUNT ON DELETE FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(foreignKey.ForeignKeyMemberColumns, 1); %> GO <% GenerateProcedureFooter(procedureName); } } this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Delete By Index Procedures * ------------------------------------------------------------------------------------------%> <% if (IncludeDeleteByIndex) { foreach( IndexSchema index in SourceTable.Indexes) { string procedureName = GetDeleteByProcedureName(index.MemberColumns); if (!_generatedProcedureNames.Contains(procedureName)) { _generatedProcedureNames.Add(procedureName); GenerateProcedureHeader(procedureName); %> CREATE PROCEDURE <%= procedureName %> <% GenerateParameters(index.MemberColumns, 1, true); %> AS SET NOCOUNT ON DELETE FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(index.MemberColumns, 1); %> <% GenerateProcedureFooter(procedureName); } } this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Delete Dynamic Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeDeleteDynamic) { GenerateProcedureHeader(GetDeleteDynamicProcedureName()); %> CREATE PROCEDURE <%= GetDeleteDynamicProcedureName() %> @WhereCondition nvarchar(500) AS SET NOCOUNT ON DECLARE @SQL nvarchar(3250) SET @SQL = ' DELETE FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE ' + @WhereCondition EXEC sp_executesql @SQL <% GenerateProcedureFooter(GetDeleteDynamicProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeSelect) { // this is to keep a select by procedure with only the primary key columns from being generated _generatedProcedureNames.Add(GetSelectByProcedureName(SourceTable.PrimaryKey.MemberColumns)); GenerateProcedureHeader(GetSelectProcedureName()); %> CREATE PROCEDURE <%= GetSelectProcedureName() %> <% GenerateParameters(SourceTable.PrimaryKey.MemberColumns, 1, true); %> AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(SourceTable.PrimaryKey.MemberColumns, 1); %> <% GenerateProcedureFooter(GetSelectProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select By Foreign Key Procedures * ------------------------------------------------------------------------------------------%> <% if (IncludeSelectByForeignKey) { foreach(TableKeySchema foreignKey in SourceTable.ForeignKeys) { string procedureName = GetSelectByProcedureName(foreignKey.ForeignKeyMemberColumns); if (!_generatedProcedureNames.Contains(procedureName)) { _generatedProcedureNames.Add(procedureName); GenerateProcedureHeader(procedureName); %> CREATE PROCEDURE <%= procedureName %> <% GenerateParameters(foreignKey.ForeignKeyMemberColumns, 1, true); %> AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(foreignKey.ForeignKeyMemberColumns, 1); %> <% GenerateOrderByClause(); %> <% GenerateProcedureFooter(procedureName); } } this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select By Index Procedures * ------------------------------------------------------------------------------------------%> <% if (IncludeSelectByIndex) { foreach(IndexSchema index in SourceTable.Indexes) { string procedureName = GetSelectByProcedureName(index.MemberColumns); if (!_generatedProcedureNames.Contains(procedureName)) { _generatedProcedureNames.Add(procedureName); GenerateProcedureHeader(procedureName); %> CREATE PROCEDURE <%= procedureName %> <% GenerateParameters(index.MemberColumns, 1, true); %> AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE <% GenerateConditions(index.MemberColumns, 1); %> <% GenerateOrderByClause(); %> <% GenerateProcedureFooter(procedureName); } } this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select Dynamic Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeSelectDynamic) { GenerateProcedureHeader(GetSelectDynamicProcedureName()); %> CREATE PROCEDURE <%= GetSelectDynamicProcedureName() %> @WhereCondition nvarchar(500), @OrderByExpression nvarchar(250) = NULL AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> DECLARE @SQL nvarchar(3250) SET @SQL = ' SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE ' + @WhereCondition <% if (OrderByExpression != null && OrderByExpression.Trim().Length > 0) { %> IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0 BEGIN SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression END ELSE BEGIN SET @SQL = @SQL + ' ORDER BY <%= OrderByExpression %>' END <% } else{%> IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0 BEGIN SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression END ELSE BEGIN SET @SQL = @SQL + 'ORDER BY <%=SourceTable.PrimaryKey.MemberColumns[0].ToString().Split('.')[2]%> DESC' END <%} %> EXEC sp_executesql @SQL <% GenerateProcedureFooter(GetSelectDynamicProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select All Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeSelectAll) { GenerateProcedureHeader(GetSelectAllProcedureName()); %> CREATE PROCEDURE <%= GetSelectAllProcedureName() %> AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] <% GenerateOrderByClause(); %> <% GenerateProcedureFooter(GetSelectAllProcedureName()); this.Progress.PerformStep(); } %> <%------------------------------------------------------------------------------------------ * * Select Paged Procedure * ------------------------------------------------------------------------------------------%> <% if (IncludeSelectPaged) { GenerateProcedureHeader(GetSelectPagedProcedureName()); %> CREATE PROCEDURE <%= GetSelectPagedProcedureName() %> @pageIndex int, @pageSize int, @WhereCondition nvarchar(500), @OrderByExpression nvarchar(250) = NULL AS SET NOCOUNT ON <% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %> DECLARE @offset int SET @offset = (@pageIndex-1)*@pageSize DECLARE @SQL nvarchar(3250) SET @SQL = 'SELECT COUNT(0) as total FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE 1=1 ' IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0 BEGIN SET @SQL = @SQL + ' AND ' + @WhereCondition END EXEC sp_executesql @SQL SET @SQL = ' SELECT <% GenerateColumns(SourceTable.Columns, 1, true); %> FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE 1=1 ' IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0 BEGIN SET @SQL = @SQL + ' AND ' + @WhereCondition END IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0 BEGIN SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression END ELSE BEGIN SET @SQL = @SQL + 'ORDER BY <%=SourceTable.PrimaryKey.MemberColumns[0].ToString().Split('.')[2]%> DESC' END SET @SQL = @SQL + ' OFFSET '+CONVERT(NVARCHAR,@offset)+' ROWS FETCH NEXT '+CONVERT(NVARCHAR,@pageSize)+' ROWS ONLY' EXEC sp_executesql @SQL <% GenerateProcedureFooter(GetSelectPagedProcedureName()); this.Progress.PerformStep(); } %> <script runat="template"> #region Member Variables private StringCollection _droppedProcedureNames = new StringCollection(); private StringCollection _generatedProcedureNames = new StringCollection(); #endregion #region Isolation Level public enum TransactionIsolationLevelEnum { ReadCommitted, ReadUncommitted, RepeatableRead, Serializable } public void GenerateSetTransactionIsolationLevelStatement(TransactionIsolationLevelEnum isolationLevel) { Response.Write("SET TRANSACTION ISOLATION LEVEL "); switch (isolationLevel) { case TransactionIsolationLevelEnum.ReadUncommitted: { Response.WriteLine("READ UNCOMMITTED"); break; } case TransactionIsolationLevelEnum.RepeatableRead: { Response.WriteLine("REPEATABLE READ"); break; } case TransactionIsolationLevelEnum.Serializable: { Response.WriteLine("SERIALIZABLE"); break; } default: { Response.WriteLine("READ COMMITTED"); break; } } } #endregion #region Code Generation Helpers public string GetTableOwner() { return GetTableOwner(true); } public string GetTableOwner(bool includeDot) { if (SourceTable.Owner.Length > 0) { if (includeDot) { return "[" + SourceTable.Owner + "]."; } else { return "[" + SourceTable.Owner + "]"; } } else { return ""; } } public void GenerateDropStatement(string procedureName) { // check to see if this procedure has already been dropped. if (!_droppedProcedureNames.Contains(procedureName)) { Response.WriteLine("IF OBJECT_ID(N'{0}') IS NOT NULL", procedureName); GenerateIndent(1); Response.WriteLine("DROP PROCEDURE {0}", procedureName); Response.WriteLine(""); // add this procedure to the list of dropped procedures _droppedProcedureNames.Add(procedureName); } } public void GenerateProcedureHeader(string procedureName) { Response.WriteLine("--region {0}", procedureName); Response.WriteLine(""); Response.WriteLine("------------------------------------------------------------------------------------------------------------------------"); Response.WriteLine("-- Generated By: {0} using CodeSmith {1}", System.Environment.UserName, typeof(CodeTemplate).Assembly.GetName().Version.ToString()); Response.WriteLine("-- Template: {0}", this.CodeTemplateInfo.FileName); Response.WriteLine("-- Procedure Name: {0}", procedureName); Response.WriteLine("-- Date Generated: {0}", DateTime.Now.ToLongDateString()); Response.WriteLine("------------------------------------------------------------------------------------------------------------------------"); } public void GenerateProcedureFooter(string procedureName) { Response.WriteLine("--endregion"); Response.WriteLine(""); Response.WriteLine("GO"); Response.WriteLine(""); } public void GenerateIndent(int indentLevel) { for (int i = 0; i < indentLevel; i++) { Response.Write('\t'); } } public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool includeTimeStamp) { GenerateParameter(column, indentLevel, isFirst, isLast, false, includeTimeStamp); } public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool isOutput, bool includeTimeStamp) { if(!includeTimeStamp && ColumnIsTimestamp(column)) return; GenerateIndent(indentLevel); Response.Write(GetSqlParameterStatement(column, isOutput)); if (!isLast) Response.Write(","); if (indentLevel >= 0) { Response.WriteLine(""); } else if (!isLast) { Response.Write(" "); } } public void GenerateParameters(IList<MemberColumnSchema> columns, int indentLevel, bool includeTimeStamp) { GenerateParameters(ConvertMemberColumnSchemaToColumnSchema(columns), indentLevel, false, includeTimeStamp); } public void GenerateParameters(IList<ColumnSchema> columns, int indentLevel, bool includeTimeStamp) { GenerateParameters(columns, indentLevel, false, includeTimeStamp); } public void GenerateParameters(IList<ColumnSchema> columns, int indentLevel, bool includeTrailingComma, bool includeTimeStamp) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateParameter(column.Value, indentLevel, column.IsFirst, column.IsLast && !includeTrailingComma, includeTimeStamp); } } public void GenerateColumn(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool includeTimeStamp) { if(!includeTimeStamp && ColumnIsTimestamp(column)) return; GenerateIndent(indentLevel); Response.Write("["); Response.Write(column.Name); Response.Write("]"); if (!isLast) Response.Write(","); if (indentLevel >= 0) { Response.WriteLine(""); } else if (!isLast) { Response.Write(" "); } } public void GenerateColumns(IList<MemberColumnSchema> columns, int indentLevel, bool includeTimeStamp) { GenerateColumns(ConvertMemberColumnSchemaToColumnSchema(columns), indentLevel, includeTimeStamp); } public void GenerateColumns(IList<ColumnSchema> columns, int indentLevel, bool includeTimeStamp) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateColumn(column.Value, indentLevel, column.IsFirst, column.IsLast, includeTimeStamp); } } public void GenerateUpdate(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool includeTimeStamp) { if(!includeTimeStamp && ColumnIsTimestamp(column)) return; GenerateIndent(indentLevel); Response.Write("["); Response.Write(column.Name); Response.Write("] = @"); Response.Write(column.Name); if (!isLast) Response.Write(","); if (indentLevel >= 0) { Response.WriteLine(""); } else if (!isLast) { Response.Write(" "); } } public void GenerateUpdates(IList<ColumnSchema> columns, int indentLevel, bool includeTimeStamp) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateUpdate(column.Value, indentLevel, column.IsFirst, column.IsLast, includeTimeStamp); } } public void GenerateCondition(ColumnSchema column, int indentLevel, bool isFirst, bool isLast) { GenerateIndent(indentLevel); if (!isFirst) Response.Write("AND "); Response.Write("["); Response.Write(column.Name); Response.Write("] = @"); Response.Write(column.Name); if (indentLevel >= 0) { Response.WriteLine(""); } else if (!isLast) { Response.Write(" "); } } public void GenerateConditions(IList<MemberColumnSchema> columns, int indentLevel) { GenerateConditions(ConvertMemberColumnSchemaToColumnSchema(columns), indentLevel); } public void GenerateConditions(IList<ColumnSchema> columns, int indentLevel) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateCondition(column.Value, indentLevel, column.IsFirst, column.IsLast); } } public void GenerateVariable(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool includeTimeStamp) { if(!includeTimeStamp && ColumnIsTimestamp(column)) return; GenerateIndent(indentLevel); Response.Write("@"); Response.Write(column.Name); if (!isLast) Response.Write(","); if (indentLevel >= 0) { Response.WriteLine(""); } else if (!isLast) { Response.Write(" "); } } public void GenerateVariables(List<ColumnSchema> columns, int indentLevel, bool includeTimeStamp) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateVariable(column.Value, indentLevel, column.IsFirst, column.IsLast, includeTimeStamp); } } public void GenerateVariables(IList<ColumnSchema> columns, int indentLevel, bool includeTimeStamp) { foreach(var column in FilterExcludedColumns(columns).AsIndexedEnumerable()) { GenerateVariable(column.Value, indentLevel, column.IsFirst, column.IsLast, includeTimeStamp); } } public void GenerateOrderByClause() { if (OrderByExpression != null && OrderByExpression.Trim().Length > 0) { Response.WriteLine("ORDER BY"); GenerateIndent(1); Response.WriteLine(OrderByExpression); } } public IList<MemberColumnSchema> FilterReadOnlyColumns(IList<MemberColumnSchema> columns) { return columns.Where(c => !ColumnIsReadOnly(c)).ToList(); } public List<ColumnSchema> FilterExcludedColumns(IList<ColumnSchema> columns) { return columns.Where(c => !ColumnIsExcluded(c)).ToList(); } public List<ColumnSchema> FilterReadOnlyAndExcludedColumns(IList<ColumnSchema> columns) { return columns.Where(c => !ColumnIsExcludedOrReadOnly(c)).ToList(); } private Regex excludedColumnRegex = null; public bool ColumnIsExcluded(ColumnSchema column) { if (column.IsPrimaryKeyMember) return false; if (excludedColumnRegex == null) { if (ExcludedColumns != null && ExcludedColumns.Count > 0) { string excluded = String.Empty; for (int i = 0; i < ExcludedColumns.Count; i++) { if (ExcludedColumns[i].Trim().Length > 0) { excluded += "(" + Regex.Escape(ExcludedColumns[i]).Replace("\\*", ".*?") + ")|"; } } if (excluded.Length > 0) { excluded = excluded.Substring(0, excluded.Length - 1); excludedColumnRegex = new Regex(excluded, RegexOptions.IgnoreCase); } } } if (excludedColumnRegex != null && excludedColumnRegex.IsMatch(column.Name)) return true; return false; } private Regex readOnlyColumnRegex = null; public bool ColumnIsReadOnly(ColumnSchema column) { if (column.IsPrimaryKeyMember) return false; if (readOnlyColumnRegex == null) { if (ReadOnlyColumns != null && ReadOnlyColumns.Count > 0) { string readOnly = String.Empty; for (int i = 0; i < ReadOnlyColumns.Count; i++) { if (ReadOnlyColumns[i].Trim().Length > 0) { readOnly += "(" + Regex.Escape(ReadOnlyColumns[i]).Replace("\\*", ".*?") + ")|"; } } if (readOnly.Length > 0) { readOnly = readOnly.Substring(0, readOnly.Length - 1); readOnlyColumnRegex = new Regex(readOnly, RegexOptions.IgnoreCase); } } } if (readOnlyColumnRegex != null && readOnlyColumnRegex.IsMatch(column.Name)) return true; return false; } public bool ColumnIsTimestamp(ColumnSchema column) { if (column.NativeType.ToLower() == "timestamp" || column.NativeType.ToLower() == "rowversion") return true; return false; } public bool ColumnIsExcludedOrReadOnly(ColumnSchema column) { return ColumnIsExcluded(column) || ColumnIsReadOnly(column); } #endregion #region Procedure Naming public string GetInsertProcedureName() { return String.Format("{0}[{1}Insert{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false)); } public string GetUpdateProcedureName() { return String.Format("{0}[{1}Update{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false)); } public string GetInsertUpdateProcedureName() { return String.Format("{0}[{1}InsertUpdate{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false)); } public string GetDeleteProcedureName() { return String.Format("{0}[{1}Delete{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false)); } public string GetSelectProcedureName() { return String.Format("{0}[{1}Select{2}]", GetTableOwner(), ProcedurePrefix, GetEntityName(false)); } public string GetSelectAllProcedureName() { return String.Format("{0}[{1}Select{2}All]", GetTableOwner(), ProcedurePrefix, GetEntityName(true)); } public string GetSelectPagedProcedureName() { return String.Format("{0}[{1}Select{2}Paged]", GetTableOwner(), ProcedurePrefix, GetEntityName(true)); } public string GetSelectByProcedureName(IList<MemberColumnSchema> targetColumns) { return GetSelectByProcedureName(ConvertMemberColumnSchemaToColumnSchema(targetColumns)); } public string GetSelectByProcedureName(IList<ColumnSchema> targetColumns) { return String.Format("{0}[{1}Select{2}By{3}]", GetTableOwner(), ProcedurePrefix, GetEntityName(true), GetBySuffix(targetColumns)); } public string GetSelectDynamicProcedureName() { return String.Format("{0}[{1}Select{2}Dynamic]", GetTableOwner(), ProcedurePrefix, GetEntityName(true)); } public string GetDeleteByProcedureName(IList<MemberColumnSchema> targetColumns) { return GetDeleteByProcedureName(ConvertMemberColumnSchemaToColumnSchema(targetColumns)); } private IList<ColumnSchema> ConvertMemberColumnSchemaToColumnSchema(IList<MemberColumnSchema> memberColumns) { var columns = new List<ColumnSchema>(); columns.AddRange(memberColumns.Cast<ColumnSchema>()); return columns; } public string GetDeleteByProcedureName(IList<ColumnSchema> targetColumns) { return String.Format("{0}[{1}Delete{2}By{3}]", GetTableOwner(), ProcedurePrefix, GetEntityName(true), GetBySuffix(targetColumns)); } public string GetDeleteDynamicProcedureName() { return String.Format("{0}[{1}Delete{2}Dynamic]", GetTableOwner(), ProcedurePrefix, GetEntityName(true)); } public string GetEntityName(bool plural) { string entityName = SourceTable.Name; if (entityName.StartsWith(TablePrefix)) { entityName = entityName.Substring(TablePrefix.Length); } if (plural) { entityName = StringUtil.ToPlural(entityName); } else { entityName = StringUtil.ToSingular(entityName); } return StringUtil.ToPascalCase(entityName); } public string GetBySuffix(IList<ColumnSchema> columns) { System.Text.StringBuilder bySuffix = new System.Text.StringBuilder(); foreach(var column in columns.AsIndexedEnumerable()) { if (column.IsFirst) bySuffix.Append("And"); bySuffix.Append(column.Value.Name); } return bySuffix.ToString(); } #endregion #region Template Overrides // Assign an appropriate file name to the output. public override string GetFileName() { if (this.SourceTable != null) { return this.SourceTable.Name + "_Procedures.sql"; } else { return base.GetFileName(); } } // Override the OutputFile property and assign our specific settings to it. [Category("2. Options")] [FileDialog(FileDialogType.Save, Title="Select Output File", Filter="Query Files (*.sql)|*.sql|All Files (*.*)|*.*", DefaultExtension=".sql")] public override string OutputFile { get {return base.OutputFile;} set {base.OutputFile = value;} } protected override void OnPostRender(string result) { if (this.AutoExecuteScript) { // execute the output on the same database as the source table. CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.SourceTable.Database.ConnectionString, result, new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage)); Trace.WriteLine(scriptResult.ToString()); } base.OnPostRender(result); } private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e) { Trace.WriteLine(e.Message); } #endregion </script>
需要在模板同级文件夹下新建
CommonUtility.cs：
using System; using System.Text; using CodeSmith.Engine; using SchemaExplorer; using System.ComponentModel; using System.Data; namespace Common.Data { /**//// <summary> /// TemplateRule /// </summary> public class CommonUtility { //get Columns info by TableName public ViewColumnSchemaCollection GetColumnCollectionByTable(ViewSchema table) { ViewColumnSchemaCollection columns = new ViewColumnSchemaCollection(table.Columns); return columns; } //Get camelcase name,such as Customer, public string GetCamelCaseName(string str) { return str.Substring(0,1).ToUpper()+str.Substring(1); } //Get ,user,private const String USER_FIELD = "User" public string GetMemberConstantDeclarationStatement(ColumnSchema column) { return GetMemberConstantDeclarationStatement("public const String ",column); } //such as public const String USER_TABLE = "User" public string GetTableConstantDeclarationStatement(ViewSchema table) { return GetMemberConstantDeclarationStatement("public const String ",table); } //suck as USER_TABLE public string GetUpperStatement(ViewSchema table) { return table.Name.ToUpper()+"_TABLE"; } //suck as USER_FIELD public string GetUpperStatement(ColumnSchema column) { return column.Name.ToUpper()+"_FIELD"; } // such as USER_TABLE = "User" public string GetMemberConstantDeclarationStatement(string protectionLevel, ViewSchema table) { return protectionLevel+GetUpperStatement(table)+" = "+GetCamelCaseName(table.Name)+""; } //such as USERID_FIELD = "Userid" public string GetMemberConstantDeclarationStatement(string protectionLevel,ColumnSchema column) { return protectionLevel+GetUpperStatement(column)+" = "+GetCamelCaseName(column.Name)+""; } public string GetCSharpVariableType(ViewColumnSchema column) { switch(column.DataType) { case DbType.AnsiString: return "string"; case DbType.AnsiStringFixedLength: return "string"; case DbType.Binary: return "byte[]"; case DbType.Boolean: return "bool"; case DbType.Byte: return "int"; case DbType.Currency: return "decimal"; case DbType.Date: return "DataTime"; case DbType.DateTime: return "DateTime"; case DbType.Decimal: return "decimal"; case DbType.Double: return "double"; case DbType.Guid: return "Guid"; case DbType.Int16: return "short"; case DbType.Int32: return "int"; case DbType.Int64: return "long"; case DbType.Object: return "object"; case DbType.SByte: return "sbyte"; case DbType.Single: return "float"; case DbType.String: return "string"; case DbType.StringFixedLength: return "string"; case DbType.Time: return "TimeSpan"; case DbType.UInt16: return "ushort"; case DbType.UInt32: return "uint"; case DbType.UInt64: return "ulong"; case DbType.VarNumeric: return "decimal"; } return null; } public string GetCSharpBaseType(ViewColumnSchema column) { switch(column.DataType) { case DbType.AnsiString: return "System.String"; case DbType.AnsiStringFixedLength: return "System.String"; case DbType.Binary: return "System.Byte[]"; case DbType.Boolean: return "System.Boolean"; case DbType.Byte: return "System.Int32"; case DbType.Currency: return "System.Decimal"; case DbType.Date: return "System.DataTime"; case DbType.DateTime: return "System.DataTime"; case DbType.Decimal: return "System.Decimal"; case DbType.Double: return "System.Double"; case DbType.Guid: return "System.Guid"; case DbType.Int16: return "System.Int16"; case DbType.Int32: return "System.Int32"; case DbType.Int64: return "System.Int64"; case DbType.Object: return "System.Object"; case DbType.SByte: return "System.SByte"; case DbType.Single: return "System.Single"; case DbType.String: return "System.String"; case DbType.StringFixedLength: return "System.String"; case DbType.Time: return "System.TimeSpan"; case DbType.UInt16: return "System.UInt16"; case DbType.UInt32: return "System.UInt32"; case DbType.UInt64: return "System.UInt64"; case DbType.VarNumeric: return "System.Decimal"; } return null; } } }