private static void GenerateBuildDeltaProcedures(string schema, string tableName, TrackingTable trackingTable, List <SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters) { CreateUpdateDeltaTableProcedure createUpdateDeltaTableProcedureTemplate = new CreateUpdateDeltaTableProcedure(schema, tableName, trackingTable, targetTableColumns, updateParameters); string sql = createUpdateDeltaTableProcedureTemplate.TransformText(); sqlBuilder.AppendLine(sql); SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet; if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog")) { EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters); AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString()); IOrderedQueryable <Catalog> priorVersionTrackingTableQuery = from catalog in context.Catalogs where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase) where catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase) where catalog.Version < trackingTable.Version where !catalog.Archived orderby catalog.Version descending select catalog; foreach (Catalog catalog in priorVersionTrackingTableQuery) { TrackingTable priorTrackingTable = TrackingTable.Parse(catalog.AuditingTableName); CreateUpdateDeltaPriorTableProcedure createUpdateDeltaPriorTableProcedureTemplate = new CreateUpdateDeltaPriorTableProcedure(schema, tableName, trackingTable, priorTrackingTable, targetTableColumns, updateParameters); sql = createUpdateDeltaPriorTableProcedureTemplate.TransformText(); sqlBuilder.AppendLine(sql); } } }
internal static TrackingTable GenerateNewTrackingTable(string schema, string tableName, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters, StringBuilder sqlBuilder, Action <string, float?> reportProgress) { int version; SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet; if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog")) { EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters); AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString()); var catalogQuery = from catalog in context.Catalogs where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase) && catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase) orderby catalog.Version descending select catalog; var latestCatalog = catalogQuery.FirstOrDefault(); if (null == latestCatalog) { version = 0; } else { version = latestCatalog.Version + 1; } } else { version = 0; } TrackingTable trackingTable = new TrackingTable(schema, tableName, version); string trackingTableName = trackingTable.ToString(); InsertCatalog insertCatalogTemplate = new InsertCatalog(schema, tableName, version, updateParameters.AuditingSchema, trackingTableName); string sql = insertCatalogTemplate.TransformText(); sqlBuilder.AppendLine(sql); CreateTrackingTable createTrackingTableTemplate = new CreateTrackingTable(schema, tableName, trackingTableName, targetTableColumns, updateParameters); sql = createTrackingTableTemplate.TransformText(); sqlBuilder.AppendLine(sql); return(trackingTable); }
internal static TrackingTable GetCurrentTrackingTable(string schema, string tableName, AuditUpdateParameters updateParameters) { SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet; if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog")) { EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters); AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString()); var catalogQuery = from catalog in context.Catalogs where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase) && catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase) orderby catalog.Version descending select catalog; Catalog catalogRow = catalogQuery.FirstOrDefault(); if (null == catalogRow) { return(null); } TrackingTable trackingTable = TrackingTable.Parse(catalogRow.AuditingTableName); return(trackingTable); } return(null); }
/// <summary> /// Create the template output /// </summary> public virtual string TransformText() { #line 7 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(_params); AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString()); 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(_targetTableName, StringComparison.OrdinalIgnoreCase) select primaryKeysRow; #line default #line hidden this.Write("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'["); #line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema)); #line default #line hidden this.Write("].[Instantº"); #line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_schema)); #line default #line hidden this.Write("º"); #line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_targetTableName)); #line default #line hidden this.Write("]\') AND type IN (N\'P\', N\'PC\'))\r\n DROP PROCEDURE ["); #line 20 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema)); #line default #line hidden this.Write("].[Instantº"); #line 20 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_schema)); #line default #line hidden this.Write("º"); #line 20 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_targetTableName)); #line default #line hidden this.Write("];\r\nGO\r\n\r\nCREATE PROCEDURE ["); #line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema)); #line default #line hidden this.Write("].[Instantº"); #line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_schema)); #line default #line hidden this.Write("º"); #line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_targetTableName)); #line default #line hidden this.Write("]\r\n @AuditºTimestampUtc [DATETIME]\r\n, @TrackºId\t\t\t\t[BIGINT] OUTPUT\r\n"); #line 26 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery) { #line default #line hidden this.Write(", @"); #line 29 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME)); #line default #line hidden this.Write(" "); #line 29 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeWithDetail(primaryKeysRow, schemaDataSet.Columns))); #line default #line hidden this.Write("\r\n"); #line 30 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" } #line default #line hidden this.Write(@"AS /* READ THIS BEFORE EDITING THIS CODE ---------------------------------- This code was automatically generated by the CodeCharm.Model.AuditFramework project. If you modify this code, and then execute the AuditFramework tool, your modifications will be ignored and overwritten. Template: CreateInstantTrackIdProcedure.tt REMARKS ------- This procedure finds the most recent TrackId for the audited record which has the primary keys provided by the caller. If the action of the most recent audit was a delete, go to the PriorTrackId of the found record in order to get data that is meaningful. */ SET NOCOUNT ON; -- Find the TrackId of the most recent track/delta record in the tracking table(s) that matches the primary key(s) "); #line 59 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" if (schemaDataSet.SchemaTables.TableExists(_params.AuditingSchema, "Catalog")) { var priorVersionTrackingTableQuery = from catalog in context.Catalogs where catalog.AuditedSchema.Equals(_schema, StringComparison.OrdinalIgnoreCase) where catalog.AuditedTableName.Equals(_targetTableName, StringComparison.OrdinalIgnoreCase) orderby catalog.Version descending select catalog; foreach (Catalog catalog in priorVersionTrackingTableQuery) { #line default #line hidden this.Write(" SELECT TOP 1 \r\n @TrackºId = [M].[TrackºId]\r\n FROM [" + ""); #line 71 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema)); #line default #line hidden this.Write("].["); #line 71 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(new TrackingTable(catalog.AuditedSchema, catalog.AuditedTableName, catalog.Version).ToString())); #line default #line hidden this.Write("] AS [T]\r\n INNER JOIN ["); #line 72 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.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].[TimestampUtc] < @AuditºTimestampUtc\r\n"); #line 75 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery) { #line default #line hidden this.Write(" AND [T].["); #line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME)); #line default #line hidden this.Write("] = @"); #line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME)); #line default #line hidden this.Write("\r\n"); #line 78 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" } #line default #line hidden this.Write(" ORDER BY [M].[TrackºId] DESC;\r\n\r\n"); #line 81 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" if (catalog.Version > 0) { #line default #line hidden this.Write(" IF @TrackºId IS NOT NULL\r\n RETURN; \r\n\t\t\r\n"); #line 86 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" } } } else { #line default #line hidden this.Write(" SELECT TOP 1 \r\n @TrackºId = [M].[TrackºId]\r\n FROM [" + ""); #line 93 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema)); #line default #line hidden this.Write("].["); #line 93 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(new TrackingTable(_schema, _targetTableName, 0).ToString())); #line default #line hidden this.Write("] AS [T]\r\n INNER JOIN ["); #line 94 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.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].[TimestampUtc] < @AuditºTimestampUtc\r\n"); #line 97 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery) { #line default #line hidden this.Write(" AND [T].["); #line 99 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME)); #line default #line hidden this.Write("] = @"); #line 99 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME)); #line default #line hidden this.Write("\r\n"); #line 100 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" } #line default #line hidden this.Write(" ORDER BY [M].[TrackºId] DESC;\r\n"); #line 102 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateInstantTrackIdProcedure.tt" } #line default #line hidden this.Write("\r\nGO\r\n"); return(this.GenerationEnvironment.ToString()); }