Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        // 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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        // 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);
        }
Beispiel #5
0
        /// <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);
        }
Beispiel #6
0
        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);
        }