private static bool AllColumnsMatch(IEnumerable <SchemaDataSet.ColumnsRow> targetTableColumns, IEnumerable <SchemaDataSet.ColumnsRow> trackingTableColumns)
{
Dictionary <string, SchemaDataSet.ColumnsRow> targetColumns = targetTableColumns.ToDictionary(row => row.COLUMN_NAME.ToLowerInvariant());
Dictionary <string, SchemaDataSet.ColumnsRow> trackingColumns = trackingTableColumns.ToDictionary(row => row.COLUMN_NAME.ToLowerInvariant());
int targetCount = targetColumns.Count;
for (int i = 0; i < targetCount; i++)
{
SchemaDataSet.ColumnsRow targetColumn = targetColumns.ElementAt(0).Value;
string targetKey = targetColumn.COLUMN_NAME.ToLowerInvariant();
targetColumns.Remove(targetKey);
SchemaDataSet.ColumnsRow trackingColumn = trackingColumns[targetKey];
if (null == trackingColumn)
{
return(true);
}
string trackingKey = trackingColumn.COLUMN_NAME.ToLowerInvariant();
if (!ColumnsMatch(targetColumn, trackingColumn))
{
return(true);
}
trackingColumns.Remove(trackingKey);
}
if (targetColumns.Count > 0 || trackingColumns.Count > 0)
{
return(true);
}
return(false);
}
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 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);
}
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());
}
/// <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());
}
internal static bool IsStringDataType(SchemaDataSet.ColumnsRow columnsRow)
{
return(c_stringDataTypes.Contains(columnsRow.DATA_TYPE.ToLowerInvariant()));
}
