/// <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); }
// Check if an attribute exists in the metadata internal static string ValidateAttributeExistenceVirtual(string validationObject, string validationAttribute, DataTable inputDataTable) { string returnExistenceEvaluation = "False"; if (validationAttribute != "NULL") { //DataColumn[] columns = inputDataTable.Columns.Cast<DataColumn>().ToArray(); //bool existenceCheckTables = inputDataTable.AsEnumerable() // .Any(row => columns.Any(col => row[col].ToString() == validationObject)); //bool existenceCheckAttributes = inputDataTable.AsEnumerable() // .Any(row => columns.Any(col => row[col].ToString() == validationAttribute)); //if (existenceCheckTables == true && existenceCheckAttributes == true) //{ // returnExistenceEvaluation = "True"; //} var objectDetails = ClassMetadataHandling.GetSchema(validationObject).FirstOrDefault(); DataRow[] foundRows = inputDataTable.Select("TABLE_NAME = '" + objectDetails.Value + "' AND SCHEMA_NAME='" + objectDetails.Key + "' AND COLUMN_NAME = '" + validationAttribute + "'"); if (foundRows.Length > 0) { returnExistenceEvaluation = "True"; } } else { returnExistenceEvaluation = "True"; } // return the result of the test; return(returnExistenceEvaluation); }
internal static Dictionary <Tuple <string, string>, bool> ValidateSourceBusinessKeyExistenceVirtual(Tuple <string, string> validationObject, DataTable inputDataTable) { // 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(); 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.StartsWith("'") && businessKeyPart.EndsWith("'")) { // Do nothing } else { var objectDetails = ClassMetadataHandling.GetSchema(validationObject.Item1).FirstOrDefault(); bool returnExistenceEvaluation = false; DataRow[] foundAuthors = inputDataTable.Select("TABLE_NAME = '" + objectDetails.Value + "' AND SCHEMA_NAME = '" + objectDetails.Key + "' AND COLUMN_NAME = '" + businessKeyPart.Trim() + "'"); if (foundAuthors.Length != 0) { returnExistenceEvaluation = true; } result.Add(Tuple.Create(validationObject.Item1, businessKeyPart.Trim()), returnExistenceEvaluation); } } } // Return the result of the test; return(result); }
// Check if an object / table exists in the metadata internal static string ValidateObjectExistenceVirtual(string validationObject, DataTable inputDataTable) { string returnExistenceEvaluation = "False"; var objectDetails = ClassMetadataHandling.GetSchema(validationObject).FirstOrDefault(); //DataColumn[] columns = inputDataTable.Columns.Cast<DataColumn>().ToArray(); DataRow[] foundRows = inputDataTable.Select("TABLE_NAME = '" + objectDetails.Value + "' AND SCHEMA_NAME='" + objectDetails.Key + "'"); //bool existenceCheck = inputDataTable.AsEnumerable().Any(row => columns.Any(col => row[col].ToString() == objectName)); if (foundRows.Length > 0) { returnExistenceEvaluation = "True"; } // 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); }