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