/// <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); }