/// <summary>
/// Create the template output
/// </summary>
public virtual string TransformText()
{
this.Write("\r\n");
#line 9 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
string currentVersionTrackIdName = _currentTrackingTable.ToString() + "ºTrackºId";
bool first;
SchemaDataSet schemaDataSet = _params.SchemaDataSet;
SchemaDataSet.SchemaPrimaryKeysDataTable primaryKeysTable = schemaDataSet.SchemaPrimaryKeys;
EnumerableRowCollection <SchemaDataSet.SchemaPrimaryKeysRow> primaryKeysRows = primaryKeysTable.AsEnumerable();
var primaryKeysQuery = from primaryKeysRow in primaryKeysRows
where primaryKeysRow.TABLE_SCHEMA.Equals(_schema, StringComparison.OrdinalIgnoreCase)
where primaryKeysRow.TABLE_NAME.Equals(_tableName, StringComparison.OrdinalIgnoreCase)
select primaryKeysRow;
#line default
#line hidden
this.Write("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[");
#line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_priorTrackingTable.ToString()));
#line default
#line hidden
this.Write("]\') AND type IN (N\'P\', N\'PC\'))\r\n DROP PROCEDURE [");
#line 24 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 24 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_priorTrackingTable.ToString()));
#line default
#line hidden
this.Write("];\r\nGO\r\n\r\nCREATE PROCEDURE [");
#line 27 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 27 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_priorTrackingTable.ToString()));
#line default
#line hidden
this.Write("]\r\n @");
#line 28 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(" [BIGINT]\r\n, @PriorºTrackId [BIGINT] = NULL OUTPUT\r\n, @AnyDelta \t\t[BIT] = " +
"0 OUTPUT\r\n");
#line 31 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(", @PKº");
#line 34 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 34 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeWithDetail(primaryKeysRow, schemaDataSet.Columns)));
#line default
#line hidden
this.Write("\r\n");
#line 35 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write("AS\r\n\r\n/*\r\n READ THIS BEFORE EDITING THIS CODE\r\n ---------------------------" +
"-------\r\n This code was automatically generated by the\r\n CodeCharm.Model.A" +
"uditFramework\r\n project.\r\n \r\n If you modify this code, and then execute" +
" \r\n the AuditFramework tool, your modifications\r\n will be ignored and over" +
"written.\r\n \r\n Template: CreateUpdateDeltaPriorTableProcedure.tt\r\n \r\n " +
" REMARKS\r\n -------\r\n This procedure is not meant to be called by users or " +
"applications\r\n directly. It is only meant to be called by other Delta* proced" +
"ures.\r\n \r\n This procedure creates a Delta record (in the tables with the Δ" +
" symbol).\r\n A Delta record contains a bitmask showing which fields have \r\n " +
" changed since the last recorded values of the fields.\r\n \r\n This procedure" +
" must compare the current track record, using the current schema \r\n version, " +
"against an earlier schema versions. Where the schema\r\n has mismatched fields " +
"(a field exists in the current version of the schema \r\n but not the earlier)," +
" the missing field is treated as a NULL and compared \r\n against the existing " +
"field. If a field exists in an earlier version but not\r\n\tthe current version, it" +
" is ignored (there is no place to store the bit).\r\n*/\r\n\r\n\tSET NOCOUNT ON;\r\n\t\r\n " +
" -- Find the TrackId of the most recent track/delta record (has PriorTrackId) i" +
"n the current tracking table that matches the primary keys of this entry\r\n\r\n " +
"SELECT TOP 1 @PriorºTrackId = [M].[TrackºId]\r\n FROM [");
#line 74 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 74 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_priorTrackingTable.ToString()));
#line default
#line hidden
this.Write("] AS [T]\r\n INNER JOIN [");
#line 75 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster] AS [M]\r\n ON [M].[TrackºId] = [T].[TrackºId]\r\n" +
" WHERE [M].[TrackºId] < @");
#line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n");
#line 78 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(" AND [T].[");
#line 81 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("] = @PKº");
#line 81 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 82 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write(@" ORDER BY [M].[TrackºId] DESC;
-- Exit quickly if we did not find the earlier version in this version of the tracking table
IF (@PriorºTrackId IS NULL)
RETURN;
-- Compare fields of that track record to the current track record and create the delta record
-- Variables to store values for comparison
");
#line 93 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" DECLARE @");
#line 96 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 96 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeAsVariable(targetColumn)));
#line default
#line hidden
this.Write(";\r\n DECLARE @Priorº");
#line 97 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 97 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeAsVariable(targetColumn)));
#line default
#line hidden
this.Write(";\r\n DECLARE @Deltaº");
#line 98 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" [BIT];\r\n \r\n");
#line 100 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n \r\n -- Get the values of the current record\r\n SELECT\r\n");
#line 105 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
first = true;
PushIndent(" ");
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
Write(first ? " " : ", ");
WriteLine("@" + targetColumn.COLUMN_NAME + " = [" + targetColumn.COLUMN_NAME + "]");
first = false;
}
PopIndent();
#line default
#line hidden
this.Write(" FROM ");
#line 116 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_currentTrackingTable.ToString()));
#line default
#line hidden
this.Write("\r\n WHERE [TrackºId] = @");
#line 117 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(";\r\n\r\n -- Get Prior values for comparison (only those that are in the current s" +
"chema)\r\n SELECT\r\n");
#line 121 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
List <string> priorTableColumnsList = (from column in schemaDataSet.Columns
where column.TABLE_SCHEMA.Equals(_params.AuditingSchema, StringComparison.OrdinalIgnoreCase)
where column.TABLE_NAME.Equals(_priorTrackingTable.ToString(), StringComparison.OrdinalIgnoreCase)
select column.COLUMN_NAME).ToList();
first = true;
PushIndent(" ");
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
if (priorTableColumnsList.Contains(targetColumn.COLUMN_NAME, StringComparer.OrdinalIgnoreCase))
{
Write(first ? " " : ", ");
WriteLine("@Priorº" + targetColumn.COLUMN_NAME + " = [" + targetColumn.COLUMN_NAME + "]");
first = false;
}
}
PopIndent();
#line default
#line hidden
this.Write(" FROM ");
#line 140 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_priorTrackingTable.ToString()));
#line default
#line hidden
this.Write("\r\n WHERE [TrackºId] = @PriorºTrackId;\r\n \r\n -- Set Delta bits\r\n" +
"");
#line 144 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" SET @Deltaº");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" = CASE WHEN (@");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" IS NULL AND @Priorº");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" IS NULL) OR (@");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" = @Priorº");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.IsStringDataType(targetColumn) ? " COLLATE Latin1_General_BIN" : string.Empty));
#line default
#line hidden
this.Write(" ) THEN 0 ELSE 1 END;\r\n\tSET @AnyDelta = CONVERT(BIT, CONVERT(INT, @AnyDelta) | CO" +
"NVERT(INT, @Deltaº");
#line 148 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("));\r\n\t\r\n");
#line 150 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write("\r\n -- Set bits for record with current version schema\r\n INSERT [");
#line 154 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 154 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_currentTrackingTable.ToString()));
#line default
#line hidden
this.Write("Δ]\r\n (\r\n [TrackºId]\r\n");
#line 157 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , [");
#line 159 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n");
#line 160 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write(" )\r\n VALUES\r\n (\r\n @");
#line 165 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(" \r\n");
#line 166 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , @Deltaº");
#line 169 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 170 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaPriorTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n );\r\n \r\nGO\r\n");
return(this.GenerationEnvironment.ToString());
}