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()
{
this.Write("\r\n");
#line 8 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
string currentVersionTrackIdName = _trackingTable.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;
IOrderedQueryable<Catalog> priorVersionTrackingTableQuery = null;
EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(_params);
AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString());
if (schemaDataSet.SchemaTables.TableExists(_params.AuditingSchema, "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;
}
#line default
#line hidden
this.Write("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[");
#line 34 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 34 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("]\') AND type IN (N\'P\', N\'PC\'))\r\n DROP PROCEDURE [");
#line 35 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 35 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("];\r\nGO\r\n\r\nCREATE PROCEDURE [");
#line 38 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 38 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("]\r\n @");
#line 39 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(" [BIGINT]\r\n, @PriorºTrackId [BIGINT] = NULL OUTPUT\r\nAS\r\n\r\n/*\r\n READ THI" +
"S BEFORE EDITING THIS CODE\r\n ----------------------------------\r\n This cod" +
"e was automatically generated by the\r\n CodeCharm.Model.AuditFramework\r\n pr" +
"oject.\r\n \r\n If you modify this code, and then execute \r\n the AuditFrame" +
"work tool, your modifications\r\n will be ignored and overwritten.\r\n \r\n T" +
"emplate: CreateUpdateDeltaTableProcedure.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 the BuildDelta procedure.\r\n \r\n This pro" +
"cedure creates a Delta record (in the tables with the Δ symbol).\r\n A Delta re" +
"cord contains a bitmask showing which fields have \r\n changed since the last r" +
"ecorded values of the fields.\r\n \r\n A Baseline record will show all fields " +
"as having not changed (all False).\r\n An Inserted or Deleted record will show " +
"all fields as having changed (all True).\r\n Only the Updated records will show" +
" the evaluated changes for each field.\r\n \r\n This procedure must compare th" +
"e current track record, using the current schema \r\n version, against potentia" +
"lly many earlier schema versions. Where the schema\r\n has mismatched fields (a" +
" field exists in one version of the schema \r\n but not the other), the missing" +
" field is treated as a NULL and compared \r\n against the existing field.\r\n*/\r\n" +
"\r\n\t-- Track whether any changes were made at all\r\n\tDECLARE @AnyDelta [BIT]\r\n\tSET" +
" @AnyDelta = 0\t\r\n\r\n -- Get the primary keys of this entry\r\n");
#line 81 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(" DECLARE @PKº");
#line 84 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 84 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeWithDetail(primaryKeysRow, schemaDataSet.Columns)));
#line default
#line hidden
this.Write(";\r\n");
#line 85 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n SELECT\r\n");
#line 89 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
first = true;
PushIndent(" ");
foreach(SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
Write(first ? " " : ", ");
WriteLine("@PKº" + primaryKeysRow.COLUMN_NAME + " = [" + primaryKeysRow.COLUMN_NAME + "]");
first = false;
}
PopIndent();
#line default
#line hidden
this.Write(" FROM [");
#line 100 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 100 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("]\r\n WHERE [TrackºId] = @");
#line 101 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(";\r\n \r\n DECLARE @CurrentºAction [NCHAR](1);\r\n SELECT @CurrentºAction" +
" = [Action]\r\n FROM [");
#line 105 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster]\r\n WHERE [TrackºId] = @");
#line 106 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(";\r\n\r\n IF (@CurrentºAction = \'B\')\r\n BEGIN\r\n\t\t-- New baselines are always set" +
" to all zeros\r\n\t\t-- to indicate no changes since previous entry (since there is " +
"no previous entry)\r\n INSERT [");
#line 112 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 112 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("Δ]\r\n (\r\n [TrackºId]\r\n");
#line 115 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , [");
#line 118 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n");
#line 119 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n )\r\n VALUES\r\n (\r\n @");
#line 124 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n");
#line 125 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , 0\r\n");
#line 129 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(@"
);
-- Even though we say that no changes happened in any fields,
-- we always consider a baseline to be a change so that it appears in reports
SET @AnyDelta = 1;
END
ELSE IF (@CurrentºAction = 'D' OR @CurrentºAction = 'I')
BEGIN
-- New Delete and new Insert actions are always set to all ones
-- to indicate that every field has been set (for inserts) or removed (for deletes)
INSERT [");
#line 141 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 141 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("Δ]\r\n (\r\n [TrackºId]\r\n");
#line 144 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , [");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n");
#line 148 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n )\r\n VALUES\r\n (\r\n @");
#line 153 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n");
#line 154 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , 1\r\n");
#line 158 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(@"
);
SET @AnyDelta = 1; -- An Insert or Delete is always a change
END
ELSE
BEGIN
-- Process update action
-- Find the TrackId of the most recent track/delta record in
-- the CURRENT tracking table that matches the primary keys of this entry
SELECT TOP 1 @PriorºTrackId = [M].[TrackºId]
FROM [");
#line 172 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 172 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("] AS [T]\r\n INNER JOIN [");
#line 173 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.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ºI" +
"d]\r\n WHERE [M].[TrackºId] < @");
#line 175 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n");
#line 176 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(" AND [T].[");
#line 179 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("] = @PKº");
#line 179 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 180 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(@" ORDER BY [M].[TrackºId] DESC;
-- If found, compare fields of that track record to the current track record and create the delta record, then return
IF (@PriorºTrackId IS NOT NULL)
BEGIN
-- Variables to store values for comparison
");
#line 188 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" DECLARE @");
#line 191 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 191 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeAsVariable(targetColumn)));
#line default
#line hidden
this.Write(";\r\n DECLARE @Priorº");
#line 192 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 192 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeAsVariable(targetColumn)));
#line default
#line hidden
this.Write(";\r\n DECLARE @Deltaº");
#line 193 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" [BIT];\r\n \r\n");
#line 195 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n \r\n -- Get the values of the current record\r\n SELECT" +
"\r\n");
#line 200 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.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 211 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("\r\n WHERE [TrackºId] = @");
#line 212 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(";\r\n\r\n -- Get Prior values for comparison\r\n SELECT\r\n");
#line 216 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
first = true;
PushIndent(" ");
foreach(SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
Write(first ? " " : ", ");
WriteLine("@Priorº" + targetColumn.COLUMN_NAME + " = [" + targetColumn.COLUMN_NAME + "]");
first = false;
}
PopIndent();
#line default
#line hidden
this.Write(" FROM ");
#line 227 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("\r\n WHERE [TrackºId] = @PriorºTrackId;\r\n \r\n -- " +
"Set Delta bits\r\n");
#line 231 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" SET @Deltaº");
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" = CASE WHEN (@");
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" IS NULL AND @Priorº");
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" IS NULL) OR (@");
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write(" = @Priorº");
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
#line 234 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.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\t\t\tSET @AnyDelta = CONVERT(BIT, CONVERT(INT, @AnyDelta) | " +
"CONVERT(INT, @Deltaº");
#line 235 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("));\r\n\r\n");
#line 237 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write("\r\n -- Set bits for record with current version schema\r\n INS" +
"ERT [");
#line 241 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 241 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("Δ]\r\n (\r\n [TrackºId]\r\n");
#line 244 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach(SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , [");
#line 246 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n");
#line 247 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" )\r\n VALUES\r\n (\r\n @" +
"");
#line 252 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(" \r\n");
#line 253 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , @Deltaº");
#line 256 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 257 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" \r\n );\r\n \r\n END; -- set bits for record wit" +
"h current version schema\r\n \r\n");
#line 263 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
if (null != priorVersionTrackingTableQuery)
{
#line default
#line hidden
this.Write(" -- Else, set bits for records with earlier version schemas\r\n -- Go" +
" to the next earlier version of the tracking table and repeat above steps \r\n");
#line 268 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (Catalog catalog in priorVersionTrackingTableQuery) // begin previous version search
{
#line default
#line hidden
this.Write(" -- Check against ");
#line 270 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(catalog.AuditingTableName));
#line default
#line hidden
this.Write("\r\n IF (@PriorºTrackId IS NULL)\r\n BEGIN\r\n\t\t\t-- A prior track record " +
"has not yet been found\r\n\t\t\t-- Execute earlier delta procedures (will ignore drop" +
"ped fields and always compare new fields to NULL)\r\n EXECUTE [");
#line 275 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Deltaº");
#line 275 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(catalog.AuditingTableName));
#line default
#line hidden
this.Write("]\r\n @");
#line 276 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\t\t-- always compare against the current record \r\n , @PriorºTr" +
"ackId OUTPUT\r\n\t\t\t\t, @AnyDelta\t\t\tOUTPUT\r\n");
#line 279 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(" , @PKº");
#line 281 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 282 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" ;\r\n \r\n END;\r\n");
#line 286 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
} // end of previous version search
}
#line default
#line hidden
this.Write(@"
END; -- process update action
-- By this point, we might have a PriorTrackId value (not guaranteed)
-- Set the PriorTrackºId field in the AuditMaster
IF (@CurrentºAction = 'B' OR @CurrentºAction = 'I')
BEGIN
UPDATE [");
#line 297 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster]\r\n SET [PriorTrackºId] = @");
#line 298 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n\t\t\t\t, [AnyDelta] = @AnyDelta\r\n WHERE [TrackºId] = @");
#line 300 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("; \r\n END\r\n ELSE\r\n BEGIN\r\n");
#line 304 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
if (schemaDataSet.SchemaTables.TableExists(_params.AuditingSchema, "Catalog"))
{
#line default
#line hidden
this.Write(" -- For \'D\'elete actions (\'U\'pdate should already have found a PriorTrackI" +
"d)\r\n");
#line 308 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
priorVersionTrackingTableQuery = from catalog in context.Catalogs
where catalog.AuditedSchema.Equals(_schema, StringComparison.OrdinalIgnoreCase)
where catalog.AuditedTableName.Equals(_tableName, StringComparison.OrdinalIgnoreCase)
where !catalog.Archived
orderby catalog.Version descending
select catalog;
foreach (Catalog catalog in priorVersionTrackingTableQuery)
{
#line default
#line hidden
this.Write(" IF (@PriorºTrackId IS NULL)\r\n BEGIN\r\n SELECT TOP 1 @" +
"PriorºTrackId = [M].[TrackºId]\r\n FROM [");
#line 320 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 320 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.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 321 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster] AS [M]\r\n ON [M].[TrackºId] = [T].[Tra" +
"ckºId]\r\n WHERE [M].[TrackºId] < @");
#line 323 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write("\r\n");
#line 324 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(" AND [T].[");
#line 327 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("] = @PKº");
#line 327 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n");
#line 328 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
#line default
#line hidden
this.Write(" ORDER BY [M].[TrackºId] DESC;\r\n\r\n END\r\n");
#line 333 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
}
}
#line default
#line hidden
this.Write("\r\n UPDATE [");
#line 337 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster]\r\n\t SET [PriorTrackºId] = @PriorºTrackId\r\n\t\t\t\t, [AnyDelta" +
"] = @AnyDelta\r\n WHERE [TrackºId] = @");
#line 340 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateUpdateDeltaTableProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(currentVersionTrackIdName));
#line default
#line hidden
this.Write(";\r\n \r\n END;\r\n\r\nGO\r\n");
return this.GenerationEnvironment.ToString();
}
/// <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();
}
