/// <summary>
/// This method ensures that a table object exists in the physical model against the catalog
/// </summary>
internal static string ValidateObjectExistencePhysical(string validationObject, string connectionString)
{
string returnExistenceEvaluation = "False";
var conn = new SqlConnection {
ConnectionString = connectionString
};
conn.Open();
var objectName = ClassMetadataHandling.GetNonQualifiedTableName(validationObject);
var schemaName = ClassMetadataHandling.GetSchema(validationObject);
// Execute the check
var cmd = new SqlCommand(
"SELECT CASE WHEN EXISTS ((SELECT * " +
"FROM sys.objects a " +
"JOIN sys.schemas b on a.schema_id = b.schema_id" +
"WHERE a.[name] = '" + objectName + "' and b.[name]= '" + schemaName.FirstOrDefault(x => x.Value.Contains(objectName)).Key + "')) THEN 1 ELSE 0 END", conn);
//SELECT * FROM sys.objects a
// JOIN sys.schemas b ON a.schema_id = b.schema_id
// WHERE a.[name] = 'dim_holder'
// AND b.[name] = 'landing'
var exists = (int)cmd.ExecuteScalar() == 1;
returnExistenceEvaluation = exists.ToString();
conn.Close();
// return the result of the test;
return(returnExistenceEvaluation);
}
/// <summary>
/// This method ensures that an attribute object exists in the physical model against the catalog
/// </summary>
internal static string ValidateAttributeExistencePhysical(string validationObject, string validationAttribute, string connectionString)
{
string returnExistenceEvaluation = "False";
// Temporary fix to allow 'transformations', in this case hard-coded NULL values to be loaded.
if (validationAttribute != "NULL")
{
var objectName = ClassMetadataHandling.GetNonQualifiedTableName(validationObject).Replace("[", "")
.Replace("]", "");
var schemaName = ClassMetadataHandling.GetSchema(validationObject);
var conn = new SqlConnection {
ConnectionString = connectionString
};
conn.Open();
// Execute the check
var cmd = new SqlCommand(
"SELECT CASE WHEN EXISTS ((SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = '" +
objectName + "' AND [TABLE_SCHEMA] = '" +
schemaName.FirstOrDefault(x => x.Value.Contains(objectName)).Key.Replace("[", "").Replace("]", "") +
"' AND [COLUMN_NAME] = '" + validationAttribute + "')) THEN 1 ELSE 0 END", conn);
var exists = (int)cmd.ExecuteScalar() == 1;
returnExistenceEvaluation = exists.ToString();
conn.Close();
}
else
{
returnExistenceEvaluation = "True";
}
// return the result of the test;
return(returnExistenceEvaluation);
}
internal static Dictionary <Tuple <string, string>, bool> ValidateSourceBusinessKeyExistencePhysical(Tuple <string, string> validationObject, string connectionString)
{
// First, the Business Keys for each table need to be identified information. This can be the combination of Business keys separated by a comma.
// Every business key needs to be iterated over to validate if the attribute exists in that table.
List <string> businessKeys = validationObject.Item2.Split(',').ToList();
// Get the table the component belongs to if available
var objectName = ClassMetadataHandling.GetNonQualifiedTableName(validationObject.Item1);
var schemaName = ClassMetadataHandling.GetSchema(validationObject.Item1);
// Now iterate over each table, as identified by the business key.
var conn = new SqlConnection {
ConnectionString = connectionString
};
conn.Open();
Dictionary <Tuple <string, string>, bool> result = new Dictionary <Tuple <string, string>, bool>();
foreach (string businessKey in businessKeys)
{
var trimBusinessKey = businessKey.Trim();
// Handle concatenate and composite
List <string> subKeys = new List <string>();
if (trimBusinessKey.StartsWith("CONCATENATE"))
{
var localBusinessKey = trimBusinessKey.Replace("CONCATENATE(", "").Replace(")", "");
subKeys = localBusinessKey.Split(';').ToList();
}
else if (trimBusinessKey.StartsWith("COMPOSITE"))
{
var localBusinessKey = trimBusinessKey.Replace("COMPOSITE(", "").Replace(")", "");
subKeys = localBusinessKey.Split(';').ToList();
}
else
{
subKeys.Add(trimBusinessKey);
}
foreach (string businessKeyPart in subKeys)
{
// Handle hard-coded business key values
if (businessKeyPart.Trim().StartsWith("'") && businessKeyPart.Trim().EndsWith("'"))
{
// Do nothing
}
else
{
// Query the data dictionary to validate existence
var cmd = new SqlCommand("SELECT CASE WHEN EXISTS (" +
"(" +
"SELECT * FROM sys.columns a " +
"JOIN sys.objects b ON a.object_id = b.object_id " +
"JOIN sys.schemas c on b.schema_id = c.schema_id " +
"WHERE OBJECT_NAME(a.[object_id]) = '" + objectName + "' AND c.[name] = '" + schemaName.FirstOrDefault(x => x.Value.Contains(objectName)).Key.Replace("[", "").Replace("]", "") + "' AND a.[name] = '" + businessKeyPart.Trim() + "'" +
")" +
") THEN 1 ELSE 0 END", conn);
var exists = (int)cmd.ExecuteScalar() == 1;
result.Add(Tuple.Create(validationObject.Item1, businessKeyPart.Trim()), exists);
}
}
}
conn.Close();
// Return the result of the test;
return(result);
}