/// <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()); }