<%------------------------------------------------------------------------------------------* 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 = http://www.mamicode.com/13;this.Progress.Step = 1;// this template requires a primary key on the source tableif (!SourceTable.HasPrimaryKey) throw new ApplicationException("SourceTable does not contain a primary key.");// generate drop statementsif (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 =http://www.mamicode.com/= true)))    {        ColumnSchema primaryKeyColumn = SourceTable.PrimaryKey.MemberColumns[0];%>CREATE PROCEDURE <%= GetInsertProcedureName() %><% GenerateParameters(FilterReadOnlyAndExcludedColumns(SourceTable.NonPrimaryKeyColumns), 1, true, false); %><% GenerateParameter(primaryKeyColumn, 1, false, true, true, false); %>ASSET 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); %>ASSET NOCOUNT ONINSERT 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); %>ASSET NOCOUNT ONUPDATE <%= 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); %>ASSET NOCOUNT ONIF 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); %>ENDELSEBEGIN    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); %>ASSET NOCOUNT ONDELETE 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); %>ASSET NOCOUNT ONDELETE 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); %>ASSET NOCOUNT ONDELETE 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)ASSET NOCOUNT ONDECLARE @SQL nvarchar(3250)SET @SQL = ‘DELETE FROM    <%= GetTableOwner() %>[<%= SourceTable.Name %>]WHERE    ‘ + @WhereConditionEXEC 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); %>ASSET 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); %>ASSET 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); %>ASSET 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) = NULLASSET 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) > 0BEGIN    SET @SQL = @SQL + ‘ORDER BY    ‘ + @OrderByExpressionENDELSEBEGIN    SET @SQL = @SQL + ‘ORDER BY    <%= OrderByExpression %>‘END<% } else{%>IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0BEGIN    SET @SQL = @SQL + ‘ORDER BY    ‘ + @OrderByExpressionENDELSEBEGIN    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() %>ASSET 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) = NULLASSET NOCOUNT ON<% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %>DECLARE @offset intSET @offset = (@pageIndex-1)*@pageSizeDECLARE @SQL nvarchar(3250)SET @SQL = ‘SELECT COUNT(0) as total FROM <%= GetTableOwner() %>[<%= SourceTable.Name %>] WHERE 1=1 AND ‘+ @WhereConditionEXEC sp_executesql @SQLSET @SQL = ‘SELECT    <% GenerateColumns(SourceTable.Columns, 1, true); %>FROM     <%= GetTableOwner() %>[<%= SourceTable.Name %>]WHERE    1=1 ‘IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0BEGIN    SET @SQL = @SQL + ‘AND‘ + @WhereConditionENDIF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0BEGIN    SET @SQL = @SQL + ‘ORDER BY    ‘ + @OrderByExpressionENDELSEBEGIN    SET @SQL = @SQL + ‘ORDER BY <%=SourceTable.PrimaryKey.MemberColumns[0].ToString().Split(‘.‘)[2]%> DESC‘ENDSET @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 Variablesprivate StringCollection _droppedProcedureNames = new StringCollection();private StringCollection _generatedProcedureNames = new StringCollection();#endregion#region Isolation Levelpublic 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 Helperspublic 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 Namingpublic 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>