internal static void CreateBaseAuditingProcedures(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
GenerateRequestTransactionSequenceProcedure(sqlBuilder, updateParameters);
GenerateCurrentTransactionSequenceProcedure(sqlBuilder, updateParameters);
GenerateReleaseTransactionSequenceProcedure(sqlBuilder, updateParameters);
reportProgress("Generated TransactionSequence procedures", null);
}
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);
}
}
}
internal static void GenerateAuditForAllSchemas(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
foreach (string schema in updateParameters.SchemasToAudit)
{
GenerateAuditForSchema(schema, sqlBuilder, updateParameters, reportProgress);
}
}
internal static void CreateProcedureGrant(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
GrantExecuteToTransactionSequenceProcedures grantExecuteToTransactionSequenceProcedures = new GrantExecuteToTransactionSequenceProcedures(updateParameters);
string sql = grantExecuteToTransactionSequenceProcedures.TransformText();
sqlBuilder.AppendLine(sql);
}
internal CreateUpdateDeltaTableProcedure(string schema, string tableName, TrackingTable trackingTable, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTable = trackingTable;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
public CreateTrackingTable(string schema, string tableName, string trackingTableName, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTableName = trackingTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
internal SetBaseline(string schema, string tableName, TrackingTable trackingTable, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTable = trackingTable;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
public CreateTrackingInsertProcedure(string schema, string auditedTable, string trackingTableName, string deltaTableName, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_auditedTable = auditedTable;
_trackingTableName = trackingTableName;
_deltaTableName = deltaTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
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 void CreateBaseAuditingTables(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
GenerateCatalogTable(sqlBuilder, updateParameters);
reportProgress("Generated Catalog table", null);
GenerateActionCodesTable(sqlBuilder, updateParameters);
reportProgress("Generated ActionCodes table", null);
GenerateTransactionSequenceTable(sqlBuilder, updateParameters);
reportProgress("Generated TransactionSequence table", null);
GenerateSessionTransactionTable(sqlBuilder, updateParameters);
reportProgress("Generated SessionTransaction table", null);
GenerateAuditMasterTable(sqlBuilder, updateParameters);
reportProgress("Generated AuditMaster table", null);
}
public string GenerateAuditingFrameworkUpdateSql(AuditUpdateParameters updateParameters)
{
StringBuilder sqlBuilder = new StringBuilder();
Action <string, float?> reportProgress = (message, percentComplete) =>
{
sqlBuilder.AppendLine();
sqlBuilder.AppendFormat("PRINT(N'{0}');", message);
sqlBuilder.AppendLine();
sqlBuilder.AppendLine("GO");
OnReportProgress(message, percentComplete);
};
SqlConnection connection = updateParameters.AuditingDatabaseConnection;
try
{
OnReportProgress("Started generating script", null);
connection.Open();
if (updateParameters.RemoveAll)
{
GenerateRemoveAll(sqlBuilder, updateParameters);
}
else
{
LoadSchemaDataSet(updateParameters);
GenerateDatabaseFiles.GenerateDatabaseSetup(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateBaseAuditingTables(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateBaseAuditingProcedures(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateProcedureGrant(sqlBuilder, updateParameters, reportProgress);
GenerateSchemaAudit.GenerateAuditForAllSchemas(sqlBuilder, updateParameters, reportProgress);
GenerateDelta.GenerateBuildAllDeltaProcedures(sqlBuilder, updateParameters, reportProgress);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return(sqlBuilder.ToString());
}
internal static void GenerateDatabaseSetup(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
Server server = new Server(updateParameters.AuditingDatabaseConnection.DataSource);
Database database = server.Databases[updateParameters.AuditingDatabaseConnection.Database];
if (!database.FileGroups.Contains(updateParameters.AuditingFileGroup))
{
DataFile dataFile = database.FileGroups[0].Files[0];
string fileName = dataFile.FileName;
string path = Path.GetDirectoryName(fileName);
if (path == null)
{
throw new InvalidOperationException("Can't determine path of database file");
}
updateParameters.SqlFilePath = new DirectoryInfo(path);
SetupDatabase setupDatabaseTemplate = new SetupDatabase(updateParameters);
string text = setupDatabaseTemplate.TransformText();
sqlBuilder.AppendLine(text);
}
reportProgress("Generated database setup", null);
}
private void OpenCommand_Executed(object sender, ExecutedRoutedEventArgs e)
{
SaveFieldsToSettings();
DbConnectionStringBuilder connectionStringBuilder = new DbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = ConnectionStringTextBox.Text;
SqlConnection sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString);
AuditUpdateParameters parameters = new AuditUpdateParameters
{
AlwaysRecreateFramework = AlwaysRecreateCheckBox.IsChecked ?? false,
AuditingDatabaseConnection = sqlConnection,
AuditingFileGroup = AuditingFileGroupTextBox.Text,
AuditingSchema = AuditingSchemaTextBox.Text,
AuditTableFormat = AuditTableFormatTextBox.Text,
AutoDeployToDatabase = AutomaticallyDeployCheckBox.IsChecked ?? false,
DatabaseName = sqlConnection.Database,
OutputPath = new DirectoryInfo(OutputPathTextBox.Text),
RemoveAll = RemoveAllCheckBox.IsChecked ?? false,
TrackByPrimaryKeys = MatchByPrimaryKeysCheckBox.IsChecked ?? false,
GenerateBaseline = GenerateBaselineCheckBox.IsChecked ?? false
};
parameters.ColumnsNotAudited.AddRange(new[] { "", "" });
parameters.ExecuteGrantees.AddRange(ExecuteGranteesTextBox.Text.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
parameters.SchemasToAudit.AddRange(SchemasToAuditTextBox.Text.Trim().Split('\n', ' '));
parameters.TablesNotAudited.AddRange(TablesNotAuditedTextBox.Text.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
parameters.ColumnsNotAudited.AddRange(ColumnsNotAuditedTextBox.Text.Trim().Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
Generator generator = new Generator();
string sql = generator.GenerateAuditingFrameworkUpdateSql(parameters);
SqlWindow sqlWindow = new SqlWindow();
sqlWindow.SqlText = sql;
sqlWindow.ShowDialog();
}
public CreateDeltaTable(string deltaTableName, List <SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_deltaTableName = deltaTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
private static void GenerateRemoveAll(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
throw new NotImplementedException();
}
internal static EntityConnectionStringBuilder GetEntityConnectionStringBuilder(AuditUpdateParameters updateParameters)
{
return(new EntityConnectionStringBuilder
{
ProviderConnectionString = updateParameters.AuditingDatabaseConnection.ConnectionString,
Provider = "System.Data.SqlClient",
Metadata = @"res://*/AuditFrameworkEntityDataModel.csdl|
res://*/AuditFrameworkEntityDataModel.ssdl|
res://*/AuditFrameworkEntityDataModel.msl"
});
}
public SetupDatabase(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
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 CreateTransactionSequenceTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateCatalogTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
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);
}
}
public GrantExecuteToTransactionSequenceProcedures(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateInstantTrackIdProcedure(string schema, string targetTableName, AuditUpdateParameters updateParameters)
{
_schema = schema;
_targetTableName = targetTableName;
_params = updateParameters;
}
public CreateBuildDeltaProcedure(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateCurrentTransactionSequenceProcedure(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateActionCodesTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateAuditMasterTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
private static void GenerateRequestTransactionSequenceProcedure(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
CreateRequestTransactionSequenceProcedure createRequestTransactionSequenceProcedure = new CreateRequestTransactionSequenceProcedure(updateParameters);
string sql = createRequestTransactionSequenceProcedure.TransformText();
sqlBuilder.AppendLine(sql);
}
public CreateTrigger(string schema, string tableName, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_params = updateParameters;
}
private static List <SchemaDataSet.ColumnsRow> GetTrackingColumnsForTable(TrackingTable trackingTable, AuditUpdateParameters updateParameters)
{
IEnumerable <SchemaDataSet.ColumnsRow> columnsRows = GetColumnsForTable(updateParameters.AuditingSchema, trackingTable.ToString(), updateParameters);
IEnumerable <SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where !columnRow.COLUMN_NAME.Equals("TrackºId", StringComparison.Ordinal)
select columnRow;
return(query.ToList());
}
