private static void LoadSchemaDataSet(AuditUpdateParameters updateParameters)
{
SqlConnection connection = updateParameters.AuditingDatabaseConnection;
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
DataTable metaDataCollectionsTable = connection.GetSchema();
schemaDataSet.MetaDataCollections.BeginLoadData();
schemaDataSet.MetaDataCollections.Load(metaDataCollectionsTable.CreateDataReader());
schemaDataSet.MetaDataCollections.EndLoadData();
foreach (SchemaDataSet.MetaDataCollectionsRow metaDataCollectionsRow in schemaDataSet.MetaDataCollections)
{
try
{
DataTable metaDataTable = connection.GetSchema(metaDataCollectionsRow.CollectionName);
string collectionName = metaDataCollectionsRow.CollectionName;
if (collectionName.Equals("MetaDataCollections", StringComparison.OrdinalIgnoreCase))
{
continue;
}
DataTable schemaTable = collectionName.Equals("Tables", StringComparison.OrdinalIgnoreCase)
? schemaDataSet.SchemaTables
: schemaDataSet.Tables[collectionName];
schemaTable.BeginLoadData();
schemaTable.Load(metaDataTable.CreateDataReader());
schemaTable.EndLoadData();
}
catch (Exception ex)
{
Debug.WriteLine("Skipping {0}", metaDataCollectionsRow.CollectionName);
}
}
}
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 string DataTypeDetail(SchemaDataSet.ColumnsRow columnsRow)
{
StringBuilder dataTypeStringBuilder = new StringBuilder();
if (!c_fixedSizeDataTypes.Contains(columnsRow.DATA_TYPE.ToLowerInvariant()))
{
if (!columnsRow.IsCHARACTER_MAXIMUM_LENGTHNull())
{
dataTypeStringBuilder.AppendFormat("({0})", columnsRow.CHARACTER_MAXIMUM_LENGTH == -1 ? "MAX" : columnsRow.CHARACTER_MAXIMUM_LENGTH.ToString());
}
else
{
if (!columnsRow.IsNUMERIC_PRECISIONNull())
{
if (!columnsRow.IsNUMERIC_SCALENull())
{
dataTypeStringBuilder.AppendFormat("({0}, {1})", columnsRow.NUMERIC_PRECISION, columnsRow.NUMERIC_SCALE);
}
else
{
dataTypeStringBuilder.AppendFormat("({0})", columnsRow.NUMERIC_PRECISION);
}
}
else
{
throw new InvalidOperationException("Don't know how to create the detail for this data type");
}
}
}
return dataTypeStringBuilder.ToString();
}
private static void GenerateAuditForSchema(string schema, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
reportProgress(string.Format("Generating audit for schema {0}", schema), null);
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
SchemaDataSet.SchemaPrimaryKeysDataTable primaryKeysDataTable = schemaDataSet.SchemaPrimaryKeys;
primaryKeysDataTable.FillPrimaryKeysBySchema(schema, updateParameters.AuditingDatabaseConnection);
SchemaDataSet.SchemaTablesDataTable schemaTables = schemaDataSet.SchemaTables;
var schemaTablesRows = schemaTables.AsEnumerable();
var schemaTablesQuery = from schemaTableRow in schemaTablesRows
where schemaTableRow.TABLE_SCHEMA.Equals(schema)
where schemaTableRow.TABLE_TYPE.Equals("BASE TABLE")
orderby schemaTableRow.TABLE_NAME
select schemaTableRow;
foreach (SchemaDataSet.SchemaTablesRow schemaTablesRow in schemaTablesQuery)
{
string tableName = schemaTablesRow.TABLE_NAME;
if (!updateParameters.TablesNotAudited.Cast <string>().Any(name => name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
{
GenerateTableAudit.GenerateAuditForTable(schema, tableName, primaryKeysDataTable, sqlBuilder, updateParameters, reportProgress);
}
}
reportProgress(string.Format("Generated audit for schema {0}", schema), null);
}
public AuditUpdateParameters()
{
SchemasToAudit = new StringCollection();
TablesNotAudited = new StringCollection();
ColumnsNotAudited = new StringCollection();
ExecuteGrantees = new StringCollection();
SchemaDataSet = new SchemaDataSet();
}
public AuditUpdateParameters()
{
SchemasToAudit = new StringCollection();
TablesNotAudited = new StringCollection();
ColumnsNotAudited = new StringCollection();
ExecuteGrantees = new StringCollection();
SchemaDataSet = new SchemaDataSet();
}
private static void GenerateTransactionSequenceTable(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (!schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "TransactionSequence"))
{
CreateTransactionSequenceTable createTransactionSequenceTable = new CreateTransactionSequenceTable(updateParameters);
string sql = createTransactionSequenceTable.TransformText();
sqlBuilder.AppendLine(sql);
}
}
private static void GenerateAuditMasterTable(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (!schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "AuditMaster"))
{
CreateAuditMasterTable createAuditMasterTableTemplate = new CreateAuditMasterTable(updateParameters);
string sql = createAuditMasterTableTemplate.TransformText();
sqlBuilder.AppendLine(sql);
}
}
internal static IEnumerable <SchemaDataSet.ColumnsRow> GetColumnsForTable(string schema, string tableName, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
SchemaDataSet.ColumnsDataTable columnsDataTable = schemaDataSet.Columns;
IEnumerable <SchemaDataSet.ColumnsRow> columnsRows = columnsDataTable.Rows.Cast <SchemaDataSet.ColumnsRow>();
IEnumerable <SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where columnRow.TABLE_SCHEMA.Equals(schema, StringComparison.OrdinalIgnoreCase) &&
columnRow.TABLE_NAME.Equals(tableName, StringComparison.OrdinalIgnoreCase)
orderby columnRow.ORDINAL_POSITION
select columnRow;
return(query);
}
internal static string DataTypeAsVariable(SchemaDataSet.ColumnsRow columnsRow)
{
StringBuilder dataTypeStringBuilder = new StringBuilder();
if (!c_dataTypesToVariableTypes.ContainsKey(columnsRow.DATA_TYPE.ToLowerInvariant()))
{
dataTypeStringBuilder.Append("[" + columnsRow.DATA_TYPE.ToLowerInvariant() + "]");
dataTypeStringBuilder.Append(DataTypeDetail(columnsRow));
}
else
{
dataTypeStringBuilder.Append(c_dataTypesToVariableTypes[columnsRow.DATA_TYPE.ToLowerInvariant()]);
}
return dataTypeStringBuilder.ToString();
}
internal static string DataTypeWithDetail(SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow, SchemaDataSet.ColumnsDataTable columnsDataTable)
{
var enumerableColumnsDataTable = columnsDataTable.AsEnumerable();
var columnsTableQuery = from columnsRow in enumerableColumnsDataTable
where columnsRow.TABLE_SCHEMA.Equals(primaryKeysRow.TABLE_SCHEMA, StringComparison.OrdinalIgnoreCase)
where columnsRow.TABLE_NAME.Equals(primaryKeysRow.TABLE_NAME, StringComparison.OrdinalIgnoreCase)
where columnsRow.COLUMN_NAME.Equals(primaryKeysRow.COLUMN_NAME, StringComparison.OrdinalIgnoreCase)
select columnsRow;
var primaryKeyColumnRow = columnsTableQuery.FirstOrDefault();
if (null == primaryKeyColumnRow)
{
throw new InvalidOperationException("Unexpected: Did not find column from primary key table");
}
return string.Format("[{0}]{1}", primaryKeyColumnRow.DATA_TYPE.ToUpperInvariant(), DataTypeDetail(primaryKeyColumnRow));
}
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 GenerateAuditForTable(string schema, string tableName, SchemaDataSet.SchemaPrimaryKeysDataTable primaryKeysDataTable, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
TrackingTable trackingTable;
reportProgress(String.Format("Generating audit for table {0}", tableName), null);
List<SchemaDataSet.ColumnsRow> targetTableColumns = GetAuditedColumnsForTable(schema, tableName, updateParameters);
bool createNewTableVersion = NewVersionOfTrackingTableNeeded(schema, tableName, targetTableColumns, updateParameters);
if (createNewTableVersion)
{
trackingTable = GenerateTableTracking.GenerateNewTrackingTable(schema, tableName, targetTableColumns, updateParameters, sqlBuilder, reportProgress);
}
else
{
trackingTable = GenerateTableTracking.GetCurrentTrackingTable(schema, tableName, updateParameters);
}
GenerateTableTracking.GenerateAuditSupportForTable(schema, tableName, trackingTable, sqlBuilder, targetTableColumns, updateParameters, reportProgress);
GenerateTableTracking.GenerateInstantSupportForTable(schema, tableName, sqlBuilder, updateParameters);
GenerateDelta.CreateDeltaProcedures(schema, tableName, trackingTable, targetTableColumns, sqlBuilder, updateParameters, reportProgress);
reportProgress(String.Format("Generated audit for table {0}", tableName), null);
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\CreateTrackingTable.tt"
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 NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N\'[");
#line 17 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 17 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("]\') AND OBJECTPROPERTY(id, N\'IsUserTable\') = 1)\r\nBEGIN\r\n\tCREATE TABLE [");
#line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("]\r\n\t\t(\r\n \t [TrackºId] [bigint]\r\n");
#line 22 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
foreach (SchemaDataSet.ColumnsRow columnsRow in _targetTableColumns)
{
#line default
#line hidden
this.Write("\t\t, [");
#line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(columnsRow.COLUMN_NAME));
#line default
#line hidden
this.Write("] ");
#line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(columnsRow.DATA_TYPE.ToUpperInvariant()));
#line default
#line hidden
#line 23 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeDetail(columnsRow)));
#line default
#line hidden
this.Write(" NULL\r\n");
#line 24 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
}
#line default
#line hidden
this.Write("\r\n\t\t, CONSTRAINT [PK_");
#line 26 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("_TrackºId] PRIMARY KEY CLUSTERED \r\n\t\t\t(\r\n\t\t\t\t[TrackºId] ASC\r\n\t\t\t)\r\n\t\t\tON [");
#line 30 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingFileGroup));
#line default
#line hidden
this.Write("]\r\n\t\t\t\r\n\t\t, CONSTRAINT [FK_");
#line 32 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("_AuditMaster] FOREIGN KEY\r\n\t\t\t(\r\n\t\t\t\t[TrackºId]\r\n\t\t\t) \r\n\t\t\tREFERENCES [");
#line 36 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster]\r\n\t\t\t(\r\n\t\t\t\t[TrackºId]\r\n\t\t\t) \r\n\t\t\tON UPDATE CASCADE \r\n\t \t\tON DELET" +
"E CASCADE \t \r\n\t \t)\r\n\t\tON [");
#line 43 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingFileGroup));
#line default
#line hidden
this.Write("];\r\n\r\n\tCREATE NONCLUSTERED INDEX [IX_");
#line 45 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("_PK] ON [");
#line 45 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 45 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("] \r\n\t(\r\n");
#line 47 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
bool first = true;
PushIndent(" ");
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
if (!first)
{
Write(", ");
}
WriteLine("[" + primaryKeysRow.COLUMN_NAME + "] ASC");
first = false;
}
PopIndent();
#line default
#line hidden
this.Write("\t)\r\n\tINCLUDE \r\n\t(\r\n\t\t[TrackºId]\r\n\t) \r\n\tWITH \r\n\t(\r\n\t STATISTICS_NORECOMPUTE = OFF" +
"\r\n\t, SORT_IN_TEMPDB = OFF\r\n\t, IGNORE_DUP_KEY = OFF\r\n\t, DROP_EXISTING = OFF\r\n\t, O" +
"NLINE = OFF\r\n\t, ALLOW_ROW_LOCKS = ON\r\n\t, ALLOW_PAGE_LOCKS = ON\r\n\t)\r\n\tON [");
#line 76 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingTable.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingFileGroup));
#line default
#line hidden
this.Write("];\r\n\r\nEND\r\nGO\r\n");
return(this.GenerationEnvironment.ToString());
}
/// <summary>
/// Create the template output
/// </summary>
public virtual string TransformText()
{
this.Write("\r\n");
#line 6 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
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(_auditedTable, StringComparison.OrdinalIgnoreCase)
select primaryKeysRow;
var schemaTableName = _schema + "º" + _auditedTable;
var transactionName = schemaTableName.Length > 32 ? schemaTableName.Substring(0, 32) : schemaTableName;
#line default
#line hidden
this.Write("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[");
#line 17 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Trackº");
#line 17 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("º");
#line 17 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_auditedTable));
#line default
#line hidden
this.Write("]\') AND type IN (N\'P\', N\'PC\'))\r\n DROP PROCEDURE [");
#line 18 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Trackº");
#line 18 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("º");
#line 18 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_auditedTable));
#line default
#line hidden
this.Write("];\r\nGO\r\n\r\nCREATE PROCEDURE [");
#line 21 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Trackº");
#line 21 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("º");
#line 21 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_auditedTable));
#line default
#line hidden
this.Write(@"]
@AuditºTransactionSequence BIGINT
, @AuditºTransactionDepth INT
, @AuditºAction CHAR(1)
, @TrackºId BIGINT OUTPUT
, @AuditºTimeStampUtc DATETIME OUTPUT
, @AuditºDesignatedUserId SYSNAME
");
#line 29 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write(", @");
#line 31 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 31 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeWithDetail(primaryKeysRow, schemaDataSet.Columns)));
#line default
#line hidden
this.Write("\r\n");
#line 32 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
}
#line default
#line hidden
this.Write(@"AS
BEGIN
/*
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: CreateTrackingInsertProcedure.tt
*/
SET NOCOUNT ON;
DECLARE @Error INT;
BEGIN TRY
BEGIN TRANSACTION;
SAVE TRANSACTION " );
#line 56 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(transactionName));
#line default
#line hidden
this.Write(@";
DECLARE @AuditºTransactionSequenceInt [BIGINT];
DECLARE @AuditºIsLocalTransactionSequence [BIT];
SET @AuditºTransactionSequenceInt = ISNULL(@AuditºTransactionSequence, 0);
SET @AuditºIsLocalTransactionSequence = ~ CONVERT(BIT, @AuditºTransactionSequenceInt);
IF @AuditºIsLocalTransactionSequence <> 0
BEGIN
EXECUTE [" );
#line 66 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write(@"].[RequestTransactionSequence]
NULL
, @AuditºTransactionSequence OUTPUT
, @AuditºTransactionDepth OUTPUT;
END;
-- Create the audit record first to get the TrackId value
SET @AuditºTimeStampUtc = ISNULL(@AuditºTimeStampUtc, GETUTCDATE());
INSERT INTO [" );
#line 76 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write(@"].[AuditMaster]
(
[ServerId]
, [TransactionSequence]
, [TransactionDepth]
, [Action]
, [AuditedTableName]
, [TrackTableVersionName]
, [TimestampUtc]
, [ConnectionUserId]
, [DesignatedUserId]
, [PriorTrackºId]
)
VALUES
(
CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))
, @AuditºTransactionSequence
, @AuditºTransactionDepth
, @AuditºAction
, '[" );
#line 95 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("].[");
#line 95 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_auditedTable));
#line default
#line hidden
this.Write("]\'\r\n\t , \'");
#line 96 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write(@"' -- Tracking Table Name
, @AuditºTimestampUtc
, SYSTEM_USER
, @AuditºDesignatedUserId
, NULL
);
SET @TrackºId = SCOPE_IDENTITY();
-- Record the changes in the Tracking table
IF @AuditºAction = 'D'
BEGIN
INSERT INTO [" );
#line 109 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 109 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("]\r\n\t (\r\n\t [TrackºId]\r\n\t");
#line 112 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
foreach (SchemaDataSet.ColumnsRow columnsRow in _targetTableColumns)
{
#line default
#line hidden
this.Write("\t , [");
#line 114 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(columnsRow.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n\t");
#line 115 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
}
#line default
#line hidden
this.Write("\t )\r\n\t VALUES\r\n\t (\r\n\t @TrackºId\r\n\t");
#line 121 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
PushIndent(" ");
foreach (SchemaDataSet.ColumnsRow columnsRow in _targetTableColumns)
{
SchemaDataSet.ColumnsRow row = columnsRow;
if (primaryKeysQuery.Any(primaryKeysRow => primaryKeysRow.COLUMN_NAME.Equals(row.COLUMN_NAME, StringComparison.OrdinalIgnoreCase)))
{
WriteLine(", @" + row.COLUMN_NAME);
}
else
{
WriteLine(", NULL");
}
}
PopIndent();
#line default
#line hidden
this.Write("\t );\r\n\t END\r\n\t ELSE\r\n\t BEGIN\r\n\t INSERT INTO [");
#line 141 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 141 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTableName));
#line default
#line hidden
this.Write("]\r\n\t (\r\n\t [TrackºId]\r\n\t");
#line 144 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
foreach (SchemaDataSet.ColumnsRow columnsRow in _targetTableColumns)
{
#line default
#line hidden
this.Write("\t , [");
#line 146 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(columnsRow.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n\t");
#line 147 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
}
#line default
#line hidden
this.Write("\t )\r\n\t SELECT \r\n\t @TrackºId\r\n\t");
#line 152 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
foreach (SchemaDataSet.ColumnsRow columnsRow in _targetTableColumns)
{
#line default
#line hidden
this.Write("\t , [");
#line 154 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(columnsRow.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n\t");
#line 155 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
}
#line default
#line hidden
this.Write("\t FROM [");
#line 157 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("].[");
#line 157 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_auditedTable));
#line default
#line hidden
this.Write("]\r\n\t WHERE\r\n\t-- primary key values match arguments\r\n\t");
#line 160 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
PushIndent(" ");
bool first = true;
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
if (!first)
{
Write("AND ");
}
else
{
Write(" ");
}
WriteLine("[{0}] = @{0}", primaryKeysRow.COLUMN_NAME);
first = false;
}
PopIndent();
#line default
#line hidden
this.Write("\t\t\t\t\t;\r\n\t\t\t\t\r\n\t END;\r\n\t \r\n\t IF @AuditºIsLocalTransactionSequence <> 0\r\n\t" +
" BEGIN\r\n\t EXECUTE [");
#line 183 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write(@"].[ReleaseTransactionSequence]
@AuditºTransactionSequence
, @AuditºTransactionDepth;
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
, @ErrorNumber INT
, @ErrorSeverity INT
, @ErrorState INT
, @ErrorLine INT
, @ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @ErrorLine = ERROR_LINE()
, @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
IF XACT_STATE() = -1 -- uncommittable
BEGIN
ROLLBACK TRANSACTION " );
#line 208 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\CreateTrackingInsertProcedure.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(transactionName));
#line default
#line hidden
this.Write(@";
END;
IF XACT_STATE() = 1 -- committable
BEGIN
COMMIT TRANSACTION;
END;
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ ERROR_MESSAGE();
IF @ErrorState = 0
SET @ErrorState = 1;
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage
, @ErrorSeverity
, 1
, @ErrorNumber -- parameter: original error number.
, @ErrorSeverity -- parameter: original error severity.
, @ErrorState -- parameter: original error state.
, @ErrorProcedure -- parameter: original error procedure name.
, @ErrorLine -- parameter: original error line number.
);
END CATCH
END
GO
");
return(this.GenerationEnvironment.ToString());
}
private static bool ColumnsMatch(SchemaDataSet.ColumnsRow columnRow1, SchemaDataSet.ColumnsRow columnRow2)
{
if (columnRow1.IsDATA_TYPENull() != columnRow2.IsDATA_TYPENull()
|| (!columnRow1.IsDATA_TYPENull() && !columnRow2.IsDATA_TYPENull()
&& !columnRow1.DATA_TYPE.Equals(columnRow2.DATA_TYPE, StringComparison.OrdinalIgnoreCase))
)
{
return false;
}
if (columnRow1.IsCHARACTER_MAXIMUM_LENGTHNull() != columnRow2.IsCHARACTER_MAXIMUM_LENGTHNull()
|| (!columnRow1.IsCHARACTER_MAXIMUM_LENGTHNull() && !columnRow2.IsCHARACTER_MAXIMUM_LENGTHNull()
&& columnRow1.CHARACTER_MAXIMUM_LENGTH != columnRow2.CHARACTER_MAXIMUM_LENGTH)
)
{
return false;
}
if (columnRow1.IsNUMERIC_PRECISIONNull() != columnRow2.IsNUMERIC_PRECISIONNull()
|| (!columnRow1.IsNUMERIC_PRECISIONNull() && !columnRow2.IsNUMERIC_PRECISIONNull()
&& columnRow1.NUMERIC_PRECISION != columnRow2.NUMERIC_PRECISION)
)
{
return false;
}
if (columnRow1.IsNUMERIC_PRECISION_RADIXNull() != columnRow2.IsNUMERIC_PRECISION_RADIXNull()
|| (!columnRow1.IsNUMERIC_PRECISION_RADIXNull() && !columnRow2.IsNUMERIC_PRECISION_RADIXNull()
&& columnRow1.NUMERIC_PRECISION_RADIX != columnRow2.NUMERIC_PRECISION_RADIX)
)
{
return false;
}
if (columnRow1.IsNUMERIC_SCALENull() != columnRow2.IsNUMERIC_SCALENull()
|| (!columnRow1.IsNUMERIC_SCALENull() && !columnRow2.IsNUMERIC_SCALENull()
&& columnRow1.NUMERIC_SCALE != columnRow2.NUMERIC_SCALE)
)
{
return false;
}
if (columnRow1.IsDATETIME_PRECISIONNull() != columnRow2.IsDATETIME_PRECISIONNull()
|| (!columnRow1.IsDATETIME_PRECISIONNull() && !columnRow2.IsDATETIME_PRECISIONNull()
&& columnRow1.DATETIME_PRECISION != columnRow2.DATETIME_PRECISION)
)
{
return false;
}
if (columnRow1.IsCHARACTER_OCTET_LENGTHNull() != columnRow2.IsCHARACTER_OCTET_LENGTHNull()
|| (!columnRow1.IsCHARACTER_OCTET_LENGTHNull() && !columnRow2.IsCHARACTER_OCTET_LENGTHNull()
&& columnRow1.CHARACTER_OCTET_LENGTH != columnRow2.CHARACTER_OCTET_LENGTH)
)
{
return false;
}
return true;
}
public void Dispose() => SchemaDataSet.Dispose();
/// <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());
}
internal static bool IsStringDataType(SchemaDataSet.ColumnsRow columnsRow)
{
return c_stringDataTypes.Contains(columnsRow.DATA_TYPE.ToLowerInvariant());
}
/// <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());
}
/// <summary>
/// Create the template output
/// </summary>
public virtual string TransformText()
{
this.Write("\r\n");
#line 5 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
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("\r\nSET NOCOUNT ON;\r\n\r\nBEGIN TRY\r\n\tBEGIN TRANSACTION;\r\n\tSAVE TRANSACTION ");
#line 19 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("ºBuildDelta;\r\n\r\n\tDECLARE @TrackºId \t\t\t\t\t[BIGINT];\r\n\tDECLARE @AuditºTimeStampUtc \t" +
"\t[DATETIME];\r\n\tDECLARE @AuditºTransactionSequence [BIGINT];\r\n\tDECLARE @AuditºTr" +
"ansactionDepth [INT];\r\n\tDECLARE @BaselineºCounter \t\t\t[BIGINT];\r\n\r\n\tEXECUTE [" +
"");
#line 27 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[RequestTransactionSequence] \r\n\t\t NULL\r\n\t\t, @AuditºTransactionSequence OUTPUT\r" +
"\n\t\t, @AuditºTransactionDepth OUTPUT;\r\n\r\n\tSET @BaselineºCounter = 0;\r\n\r\n\t");
#line 34 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write("\tDECLARE @PKº");
#line 36 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write(" ");
#line 36 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(SchemaHelper.DataTypeWithDetail(primaryKeysRow, schemaDataSet.Columns)));
#line default
#line hidden
this.Write(";\r\n\t");
#line 37 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
}
#line default
#line hidden
this.Write("\r\n\t");
#line 40 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
string cursorName = string.Format("{0}º{1}ºCursor", _schema, _tableName);
#line default
#line hidden
this.Write("\tDECLARE ");
#line 43 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write(" CURSOR LOCAL\r\n\t\tFAST_FORWARD\r\n\t\tFOR\tSELECT\r\n\t");
#line 46 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
bool first = true;
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
if (!first)
{
Write(", ");
}
Write("[" + primaryKeysRow.COLUMN_NAME + "]");
first = false;
}
#line default
#line hidden
this.Write("\t\t\tFROM\t[");
#line 57 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("].[");
#line 57 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_tableName));
#line default
#line hidden
this.Write("];\r\n\r\n\tOPEN \t");
#line 59 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write(";\r\n\r\n\tFETCH NEXT FROM ");
#line 61 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write("\r\n\t\tINTO ");
#line 62 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
first = true;
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
if (!first)
{
Write(", ");
}
Write("@PKº" + primaryKeysRow.COLUMN_NAME);
first = false;
}
#line default
#line hidden
this.Write(";\r\n\r\n\tWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\r\n\t\tEXECUTE [");
#line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[Trackº");
#line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_schema));
#line default
#line hidden
this.Write("º");
#line 77 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_tableName));
#line default
#line hidden
this.Write("]\r\n\t\t @AuditºTransactionSequence = @AuditºTransactionSequence\r\n\t\t, @AuditºTransa" +
"ctionDepth = @AuditºTransactionDepth\r\n\t\t, @AuditºAction = \'B\'\r\n\t\t, @TrackºId = @" +
"TrackºId OUTPUT\r\n\t\t, @AuditºTimeStampUtc = NULL\r\n\t\t, @AuditºDesignatedUserId = N" +
"ULL\r\n\t");
#line 84 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
#line default
#line hidden
this.Write("\t\t, @");
#line 86 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write(" = @PKº");
#line 86 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(primaryKeysRow.COLUMN_NAME));
#line default
#line hidden
this.Write("\r\n\t");
#line 87 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
}
#line default
#line hidden
this.Write("\t\t;\r\n\t\r\n\t\tINSERT [");
#line 90 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[");
#line 90 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("Δ]\r\n (\r\n [TrackºId]\r\n");
#line 93 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , [");
#line 96 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(targetColumn.COLUMN_NAME));
#line default
#line hidden
this.Write("]\r\n");
#line 97 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
}
#line default
#line hidden
this.Write(" \r\n )\r\n VALUES\r\n (\r\n @TrackºId\r\n");
#line 103 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
foreach (SchemaDataSet.ColumnsRow targetColumn in _targetTableColumns)
{
#line default
#line hidden
this.Write(" , 0\r\n");
#line 107 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
}
#line default
#line hidden
this.Write(" \r\n );\r\n\r\n\t\tSET @BaselineºCounter = @BaselineºCounter + 1;\r\n\t\tIF " +
"@BaselineºCounter % 1000 = 0\r\n\t\tBEGIN\r\n\t\t\tPRINT (CAST(@BaselineºCounter AS [NVAR" +
"CHAR](16)));\r\n\t\tEND\r\n\t\t\r\n\t\tFETCH NEXT FROM ");
#line 117 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write("\r\n\t\t\tINTO ");
#line 118 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
first = true;
foreach (SchemaDataSet.SchemaPrimaryKeysRow primaryKeysRow in primaryKeysQuery)
{
if (!first)
{
Write(", ");
}
Write("@PKº" + primaryKeysRow.COLUMN_NAME);
first = false;
}
#line default
#line hidden
this.Write(";\r\n\r\n\tEND;\r\n\r\n\tCLOSE\t");
#line 133 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write(";\r\n\tDEALLOCATE\t");
#line 134 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(cursorName));
#line default
#line hidden
this.Write(";\r\n\r\n\tUPDATE\t[");
#line 136 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write("].[AuditMaster]\r\n\t\tSET\t[PriorTrackºId] = [TrackºId]\r\n\t\tWHERE\t[Action] = \'B\'\r\n\t\t\tA" +
"ND\t[TrackTableVersionName] = \'");
#line 139 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_trackingTable.ToString()));
#line default
#line hidden
this.Write("\';\r\n\r\n\tCOMMIT TRANSACTION;\r\n\r\n\tEXECUTE [");
#line 143 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write(@"].[ReleaseTransactionSequence]
@AuditºTransactionSequence
, @AuditºTransactionDepth;
PRINT (CAST(@BaselineºCounter AS [NVARCHAR](16)) + ' records baselined');
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
, @ErrorNumber INT
, @ErrorSeverity INT
, @ErrorState INT
, @ErrorLine INT
, @ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @ErrorLine = ERROR_LINE()
, @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
IF XACT_STATE() = -1 -- uncommittable
BEGIN
ROLLBACK TRANSACTION " );
#line 167 "C:\Users\mcbeea\Desktop\AuditFramework\Model.AuditFramework\T4Templates\SetBaseline.tt"
this.Write(this.ToStringHelper.ToStringWithCulture(_params.AuditingSchema));
#line default
#line hidden
this.Write(@"ºBuildDelta;
END;
IF XACT_STATE() = 1 -- committable
BEGIN
COMMIT TRANSACTION;
END;
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ ERROR_MESSAGE();
IF @ErrorState = 0
SET @ErrorState = 1;
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage
, @ErrorSeverity
, 1
, @ErrorNumber -- parameter: original error number.
, @ErrorSeverity -- parameter: original error severity.
, @ErrorState -- parameter: original error state.
, @ErrorProcedure -- parameter: original error procedure name.
, @ErrorLine -- parameter: original error line number.
);
END CATCH;
GO
");
return(this.GenerationEnvironment.ToString());
}
