private void ButtonGenerateTestcases_Click(object sender, EventArgs e)
{
var connOmd = new SqlConnection {
ConnectionString = TeamConfigurationSettings.ConnectionStringOmd
};
try
{
connOmd.Open();
}
catch (Exception exception)
{
richTextBoxOutput.Text =
"There was an error connecting to the metadata database. \r\n\r\nA connection could not be established. Can you verify the connection details for the metadata in the main screen? \r\n\r\nThe error message is: " +
exception.Message;
}
// Evaluate the query types based on the environments / radio buttons
string environmentSnippet = "";
if (radioButtonPSA.Checked)
{
environmentSnippet = TeamConfigurationSettings.PsaDatabaseName;
}
else if (radioButtonIntegrationLayer.Checked)
{
environmentSnippet = TeamConfigurationSettings.IntegrationDatabaseName;
}
var queryRi = new StringBuilder();
queryRi.AppendLine("--");
queryRi.AppendLine("-- Referential Integrity Validation Query");
queryRi.AppendLine("-- Generated at " + DateTime.Now);
queryRi.AppendLine("--");
queryRi.AppendLine();
#region Satellite
// Satellite component
queryRi.AppendLine("GO");
queryRi.AppendLine();
queryRi.AppendLine("-- Satellite validation");
queryRi.AppendLine();
var queryTableArraySat = new StringBuilder();
queryTableArraySat.AppendLine(@"
SELECT DISTINCT
sat.[SOURCE_SCHEMA_NAME]
,sat.[SOURCE_NAME]
,sat.[TARGET_SCHEMA_NAME]
,sat.[TARGET_NAME]
,sat.[SOURCE_BUSINESS_KEY_DEFINITION]
,sat.[TARGET_BUSINESS_KEY_DEFINITION]
,sat.[TARGET_TYPE]
,sat.[SURROGATE_KEY]
,sat.[FILTER_CRITERIA]
,sat.[LOAD_VECTOR]
,hub.[TARGET_SCHEMA_NAME] AS [HUB_SCHEMA_NAME]
,hub.[TARGET_NAME] AS [HUB_NAME]
FROM [interface].[INTERFACE_SOURCE_SATELLITE_XREF] sat
JOIN [interface].[INTERFACE_SOURCE_HUB_XREF] hub
ON sat.[SOURCE_NAME] = hub.[SOURCE_NAME]
AND sat.[TARGET_BUSINESS_KEY_DEFINITION] = hub.[TARGET_BUSINESS_KEY_DEFINITION]
WHERE sat.[TARGET_TYPE]='Normal'
");
var metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArraySat.ToString());
if (metaDataTable.Rows.Count == 0)
{
richTextBoxInformationMain.Text += "There was no metadata available to create Satellite Referential Integrity scripts.";
}
else
{
foreach (DataRow row in metaDataTable.Rows)
{
queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["TARGET_NAME"] + "'");
queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["TARGET_SCHEMA_NAME"] + "].[" + (string)row["TARGET_NAME"] + "] sat");
queryRi.AppendLine("WHERE NOT EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["HUB_SCHEMA_NAME"] + "].[" + (string)row["HUB_NAME"] + "] hub WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = hub.[" + (string)row["SURROGATE_KEY"] + "]");
queryRi.AppendLine(")");
if (radioButtonDeltaValidation.Checked)
{
var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]);
var surrogateKeySnippet = new StringBuilder();
surrogateKeySnippet.AppendLine("HASHBYTES('MD5',");
foreach (var businessKey in businessKeyList)
{
string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey);
surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+");
}
surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3);
surrogateKeySnippet.AppendLine();
surrogateKeySnippet.AppendLine(" )");
queryRi.AppendLine("AND EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = ");
queryRi.AppendLine(" " + surrogateKeySnippet);
queryRi.Remove(queryRi.Length - 3, 3);
queryRi.AppendLine(")");
}
queryRi.AppendLine("--");
queryRi.AppendLine("UNION ALL");
queryRi.AppendLine("--");
}
queryRi.Remove(queryRi.Length - 19, 19);
}
#endregion
#region Link
// Link component
var queryTableArrayLink = @"
SELECT
[SOURCE_SCHEMA_NAME]
,[SOURCE_NAME]
,[LINK_SCHEMA_NAME]
,[LINK_NAME]
,[HUB_SCHEMA_NAME]
,[HUB_NAME]
,[HUB_SURROGATE_KEY]
,[HUB_TARGET_KEY_NAME_IN_LINK]
,[HUB_SOURCE_BUSINESS_KEY_DEFINITION]
,[HUB_TARGET_BUSINESS_KEY_DEFINITION]
,[HUB_ORDER]
FROM [interface].[INTERFACE_HUB_LINK_XREF]
WHERE LINK_NAME !='Not applicable'
";
metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArrayLink);
queryRi.AppendLine();
queryRi.AppendLine("-- Link validation");
queryRi.AppendLine();
if (metaDataTable.Rows.Count == 0)
{
richTextBoxInformationMain.Text +=
"There was no metadata available to create Link Referential Integrity scripts.";
}
else
{
foreach (DataRow row in metaDataTable.Rows)
{
queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["LINK_NAME"] + "'");
queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["LINK_SCHEMA_NAME"] + "].[" + (string)row["LINK_NAME"] + "] lnk");
queryRi.AppendLine("WHERE NOT EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["HUB_SCHEMA_NAME"] + "].[" + (string)row["HUB_NAME"] + "] hub WHERE lnk.[" + (string)row["HUB_TARGET_KEY_NAME_IN_LINK"] + "] = hub.[" + (string)row["HUB_SURROGATE_KEY"] + "]");
queryRi.AppendLine(")");
if (radioButtonDeltaValidation.Checked)
{
var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["HUB_SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["HUB_TARGET_BUSINESS_KEY_DEFINITION"]);
var surrogateKeySnippet = new StringBuilder();
surrogateKeySnippet.AppendLine("HASHBYTES('MD5',");
foreach (var businessKey in businessKeyList)
{
string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey);
surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+");
}
surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3);
surrogateKeySnippet.AppendLine();
surrogateKeySnippet.AppendLine(" )");
queryRi.AppendLine("AND EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE lnk.[" + (string)row["HUB_SURROGATE_KEY"] + "] = ");
queryRi.AppendLine(" " + surrogateKeySnippet);
queryRi.Remove(queryRi.Length - 3, 3);
queryRi.AppendLine(")");
}
queryRi.AppendLine("--");
queryRi.AppendLine("UNION ALL");
queryRi.AppendLine("--");
}
queryRi.Remove(queryRi.Length - 19, 19);
}
#endregion
#region LinkSatellite
// Link Satellite component
queryRi.AppendLine("GO");
queryRi.AppendLine();
queryRi.AppendLine("-- Satellite validation");
queryRi.AppendLine();
var queryTableArrayLsat = new StringBuilder();
queryTableArrayLsat.AppendLine(@"
SELECT DISTINCT
sat.[SOURCE_SCHEMA_NAME]
,sat.[SOURCE_NAME]
,sat.[TARGET_SCHEMA_NAME]
,sat.[TARGET_NAME]
,sat.[SOURCE_BUSINESS_KEY_DEFINITION]
,sat.[TARGET_BUSINESS_KEY_DEFINITION]
,sat.[TARGET_TYPE]
,sat.[SURROGATE_KEY]
,sat.[FILTER_CRITERIA]
,sat.[LOAD_VECTOR]
,lnk.[TARGET_SCHEMA_NAME] AS [LINK_SCHEMA_NAME]
,lnk.[TARGET_NAME] AS [LINK_NAME]
FROM [interface].[INTERFACE_SOURCE_SATELLITE_XREF] sat
JOIN [interface].[INTERFACE_SOURCE_LINK_XREF] lnk
ON sat.[SOURCE_NAME] = lnk.[SOURCE_NAME]
AND sat.[TARGET_BUSINESS_KEY_DEFINITION] = lnk.[TARGET_BUSINESS_KEY_DEFINITION]
WHERE sat.[TARGET_TYPE]='Link Satellite'
");
metaDataTable = Utility.GetDataTable(ref connOmd, queryTableArrayLsat.ToString());
if (metaDataTable.Rows.Count == 0)
{
richTextBoxInformationMain.Text += "There was no metadata available to create Link Satellite Referential Integrity scripts.";
}
else
{
foreach (DataRow row in metaDataTable.Rows)
{
queryRi.AppendLine("SELECT COUNT(*) AS RI_ISSUES, '" + (string)row["TARGET_NAME"] + "'");
queryRi.AppendLine("FROM [" + environmentSnippet + "].[" + (string)row["TARGET_SCHEMA_NAME"] + "].[" + (string)row["TARGET_NAME"] + "] sat");
queryRi.AppendLine("WHERE NOT EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + environmentSnippet + "].[" + (string)row["LINK_SCHEMA_NAME"] + "].[" + (string)row["LINK_NAME"] + "] lnk WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = lnk.[" + (string)row["SURROGATE_KEY"] + "]");
queryRi.AppendLine(")");
if (radioButtonDeltaValidation.Checked)
{
var businessKeyList = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]);
var surrogateKeySnippet = new StringBuilder();
surrogateKeySnippet.AppendLine("HASHBYTES('MD5',");
foreach (var businessKey in businessKeyList)
{
string businessKeyEval = InterfaceHandling.EvaluateBusinessKey(businessKey);
surrogateKeySnippet.AppendLine(" ISNULL(RTRIM(CONVERT(NVARCHAR(100)," + businessKeyEval + ")),'NA')+'|'+");
}
surrogateKeySnippet.Remove(surrogateKeySnippet.Length - 3, 3);
surrogateKeySnippet.AppendLine();
surrogateKeySnippet.AppendLine(" )");
queryRi.AppendLine("AND EXISTS");
queryRi.AppendLine("(");
queryRi.AppendLine(" SELECT 1 FROM [" + TeamConfigurationSettings.StagingDatabaseName + "].[" + (string)row["SOURCE_SCHEMA_NAME"] + "].[" + (string)row["SOURCE_NAME"] + "] WHERE sat.[" + (string)row["SURROGATE_KEY"] + "] = ");
queryRi.AppendLine(" " + surrogateKeySnippet);
queryRi.Remove(queryRi.Length - 3, 3);
queryRi.AppendLine(")");
}
queryRi.AppendLine("--");
queryRi.AppendLine("UNION ALL");
queryRi.AppendLine("--");
}
queryRi.Remove(queryRi.Length - 19, 19);
}
#endregion
richTextBoxOutput.Text = queryRi.ToString();
}
/// <summary>
/// Create output using Handlebars as templating engine
/// </summary>
private void GenerateFromPattern()
{
EventLog eventLog = new EventLog();
localRichTextBoxGenerationOutput.Clear();
RaiseOnClearMainText();
localTabControl.SelectedIndex = 0;
var connOmd = new SqlConnection {
ConnectionString = TeamConfigurationSettings.ConnectionStringOmd
};
// Populate the main list of source-to-target mappings (base query)
var metadataQuery = input.LoadPatternBaseQuery;
var metadataDataTable = Utility.GetDataTable(ref connOmd, metadataQuery);
// Populate the attribute mappings
// Create the column-to-column mapping
var columnMetadataQuery = input.LoadPatternAttributeQuery;
var columnMetadataDataTable = Utility.GetDataTable(ref connOmd, columnMetadataQuery);
// Populate the additional business key information (i.e. links)
var additionalBusinessKeyQuery = input.LoadPatternAdditionalBusinessKeyQuery;
var additionalBusinessKeyDataTable = Utility.GetDataTable(ref connOmd, additionalBusinessKeyQuery);
// Loop through the checked items, select the right mapping and generate the pattern
if (localCheckedListBox.CheckedItems.Count != 0)
{
for (int x = 0; x <= localCheckedListBox.CheckedItems.Count - 1; x++)
{
var targetTableName = localCheckedListBox.CheckedItems[x].ToString();
localRichTextBox.AppendText(@"Processing generation for " + targetTableName + ".\r\n");
DataRow[] mappingRows = null;
try
{
mappingRows = metadataDataTable.Select("[TARGET_NAME] = '" + targetTableName + "'");
}
catch (Exception ex)
{
RaiseOnChangeMainText("There was an error generating the output, this happened when interpreting the source-to-mapping rows. " +
"\r\n\r\nThe query used was:" + input.LoadPatternBaseQuery + ".\r\n\r\nThe error message was:" + ex);
}
// Move the data table to the class instance
List <SourceToTargetMapping> sourceToTargetMappingList = new List <SourceToTargetMapping>();
if (mappingRows != null)
{
foreach (DataRow row in mappingRows)
{
#region Business Key
// Creating the Business Key definition, using the available components (see above)
List <BusinessKey> businessKeyList = new List <BusinessKey>();
BusinessKey businessKey =
new BusinessKey
{
businessKeyComponentMapping = InterfaceHandling.BusinessKeyComponentMappingList((string)row["SOURCE_BUSINESS_KEY_DEFINITION"], (string)row["TARGET_BUSINESS_KEY_DEFINITION"]),
surrogateKey = (string)row["SURROGATE_KEY"]
};
businessKeyList.Add(businessKey);
#endregion
#region Column Mapping
// Create the column-to-column mapping
List <ColumnMapping> columnMappingList = new List <ColumnMapping>();
if (columnMetadataDataTable != null && columnMetadataDataTable.Rows.Count > 0)
{
DataRow[] columnRows = columnMetadataDataTable.Select("[TARGET_NAME] = '" + targetTableName + "' AND [SOURCE_NAME] = '" + (string)row["SOURCE_NAME"] + "'");
foreach (DataRow column in columnRows)
{
ColumnMapping columnMapping = new ColumnMapping();
Column sourceColumn = new Column();
Column targetColumn = new Column();
sourceColumn.columnName = (string)column["SOURCE_ATTRIBUTE_NAME"];
targetColumn.columnName = (string)column["TARGET_ATTRIBUTE_NAME"];
columnMapping.sourceColumn = sourceColumn;
columnMapping.targetColumn = targetColumn;
columnMappingList.Add(columnMapping);
}
}
#endregion
#region Additional Business Keys
if (additionalBusinessKeyDataTable != null && additionalBusinessKeyDataTable.Rows.Count > 0)
{
DataRow[] additionalBusinessKeyRows = additionalBusinessKeyDataTable.Select("[LINK_NAME] = '" + targetTableName + "'");
foreach (DataRow additionalKeyRow in additionalBusinessKeyRows)
{
var hubBusinessKey = new BusinessKey();
hubBusinessKey.businessKeyComponentMapping = InterfaceHandling.BusinessKeyComponentMappingList((string)additionalKeyRow["HUB_SOURCE_BUSINESS_KEY_DEFINITION"], (string)additionalKeyRow["HUB_TARGET_BUSINESS_KEY_DEFINITION"]);
hubBusinessKey.surrogateKey = (string)additionalKeyRow["HUB_TARGET_KEY_NAME_IN_LINK"];
businessKeyList.Add(hubBusinessKey); // Adding the Link Business Key
}
}
#endregion
#region Lookup Table
// Define a lookup table, in case there is a desire to do key lookups.
var lookupTable = (string)row["TARGET_NAME"];
if (TeamConfigurationSettings.TableNamingLocation == "Prefix")
{
int prefixLocation = lookupTable.IndexOf(TeamConfigurationSettings.StgTablePrefixValue);
if (prefixLocation != -1)
{
lookupTable = lookupTable
.Remove(prefixLocation, TeamConfigurationSettings.StgTablePrefixValue.Length)
.Insert(prefixLocation, TeamConfigurationSettings.PsaTablePrefixValue);
}
}
else
{
int prefixLocation = lookupTable.LastIndexOf(TeamConfigurationSettings.StgTablePrefixValue);
if (prefixLocation != -1)
{
lookupTable = lookupTable
.Remove(prefixLocation, TeamConfigurationSettings.StgTablePrefixValue.Length)
.Insert(prefixLocation, TeamConfigurationSettings.PsaTablePrefixValue);
}
}
#endregion
// Add the created Business Key to the source-to-target mapping
var sourceToTargetMapping = new SourceToTargetMapping();
sourceToTargetMapping.sourceTable = (string)row["SOURCE_NAME"]; // Source table
sourceToTargetMapping.targetTable = (string)row["TARGET_NAME"]; // Target table
sourceToTargetMapping.lookupTable = lookupTable; // Lookup Table
sourceToTargetMapping.targetTableHashKey = (string)row["SURROGATE_KEY"]; // Surrogate Key
sourceToTargetMapping.businessKey = businessKeyList; // Business Key
sourceToTargetMapping.filterCriterion = (string)row["FILTER_CRITERIA"]; // Filter criterion
sourceToTargetMapping.columnMapping = columnMappingList; // Column to column mapping
// Add the source-to-target mapping to the mapping list
sourceToTargetMappingList.Add(sourceToTargetMapping);
}
}
// Create an instance of the 'MappingList' class / object model
SourceToTargetMappingList sourceTargetMappingList = new SourceToTargetMappingList();
sourceTargetMappingList.individualSourceToTargetMapping = sourceToTargetMappingList;
sourceTargetMappingList.metadataConfiguration = new MetadataConfiguration();
sourceTargetMappingList.mainTable = targetTableName;
// Return the result to the user
try
{
// Compile the template, and merge it with the metadata
var template = Handlebars.Compile(localRichTextBoxGenerationPattern.Text);
var result = template(sourceTargetMappingList);
// Check if the metadata needs to be displayed
if (displayJsonFlag)
{
try
{
var json = JsonConvert.SerializeObject(sourceTargetMappingList, Formatting.Indented);
localRichTextBoxGenerationOutput.AppendText(json + "\r\n\r\n");
}
catch (Exception ex)
{
RaiseOnChangeMainText("An error was encountered while generating the JSON metadata. The error message is: " + ex);
}
}
// Display the output of the template to the user
localRichTextBoxGenerationOutput.AppendText(result);
// Spool the output to disk
EventLog fileSaveEventLog = new EventLog();
if (saveOutputFileFlag)
{
fileSaveEventLog = Utility.SaveOutputToDisk(VedwConfigurationSettings.VedwOutputPath + @"\Output_" + targetTableName + ".sql", result);
}
//Generate in database
EventLog databaseEventLog = new EventLog();
if (generateInDatabaseFlag)
{
var localConn = input.MatchConnectionKey();
var conn = new SqlConnection {
ConnectionString = localConn[input.LoadPatternConnectionKey]
};
databaseEventLog = Utility.ExecuteOutputInDatabase(conn, result);
}
eventLog.AddRange(fileSaveEventLog);
eventLog.AddRange(databaseEventLog);
}
catch (Exception ex)
{
var localEvent = new Event
{
eventCode = 1,
eventDescription = "The template could not be compiled, the error message is " + ex + "."
};
eventLog.Add(localEvent);
}
}
}
else
{
localRichTextBox.AppendText($"There was no metadata selected to generate {inputNiceName} code. Please check the metadata schema - are there any {inputNiceName} objects selected?");
}
connOmd.Close();
connOmd.Dispose();
// Report back to the user
int errorCounter = 0;
foreach (Event individualEvent in eventLog)
{
if (individualEvent.eventCode == 1)
{
errorCounter++;
}
RaiseOnChangeMainText(individualEvent.eventDescription);
}
RaiseOnChangeMainText($"\r\n\r\n{errorCounter} errors have been found.\r\n");
RaiseOnChangeMainText($"Associated scripts have been saved in {VedwConfigurationSettings.VedwOutputPath}.\r\n");
// Apply syntax highlighting
SyntaxHighlight();
}
