public void SelectWithTopClause()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"TOP\",\"ClauseValue\":\"20\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    int rowCount = 0;

                    while (reader.Read())
                    {
                        rowCount++;
                    }
                    return(rowCount == 20);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#2
0
        public void SelectWithWhereClause_MultiCondition_1()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"EnglishProductName LIKE 'Chainring%' AND Color = 'Black'\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    reader.Read();
                    return(reader.GetString(3).StartsWith("Chainring", StringComparison.OrdinalIgnoreCase) && (reader.GetInt32(0) == 11));
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#3
0
        public void TestMsSqlSchemaParserConstructor_AsXmlString()
        {
            string      xmlString = File.ReadAllText(@"..\..\Data\DimProduct.xml");
            TableSchema table     = MsSqlSchemaParser.Parse(xmlString);

            Assert.AreEqual(table.Name, "AdventureWorksDW2012.dbo.DimProduct");
        }
        public void SimpleSelectQueryWithSingleTable_Sum()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"SafetyStockLevel\",\"ColumnOperation\":\"SUM\",\"Alias\":\"Safety Stock Level Total\"}],\"TableClause\":[]}],\"QueryClause\":[]}");

                validator.AddTable(table);

                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    reader.Read();
                    return(reader.GetInt32(0) == 300092);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#5
0
        public void TestXmlParsing_TableName_1()
        {
            XmlDocument xmlDoc = new XmlDocument();

            xmlDoc.Load(@"..\..\Data\DimProduct.xml");
            TableSchema table = MsSqlSchemaParser.Parse(xmlDoc);

            Assert.AreEqual(table.Name, "AdventureWorksDW2012.dbo.DimProduct");
        }
示例#6
0
        public void TestXmlParsing_FirstColumnName_1()
        {
            string      xmlContent = File.ReadAllText(@"..\..\Data\DimProduct.xml");
            XmlDocument xmlDoc     = new XmlDocument();

            xmlDoc.LoadXml(xmlContent);
            TableSchema table = MsSqlSchemaParser.Parse(xmlDoc);

            Assert.AreEqual(table.Columns.First().Name, "ProductKey");
        }
示例#7
0
 /// <summary>
 /// Method add/import new table data from schema xml.
 /// </summary>
 /// <param name="xmlPathOrString">XML file path or xml string.</param>
 /// <returns>Returns true if successfully added.</returns>
 public bool AddTableSchema(string xmlPathOrString)
 {
     try
     {
         TableList.Add(MsSqlSchemaParser.Parse(xmlPathOrString));
         return(true);
     }
     catch (Exception)
     {
         throw;
     }
 }
        public void SelectWithGroupByClause_WithSumOperation()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Level\",\"ColumnOperation\":\"SUM\"}],\"TableClause\":[{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    bool isValid            = true;
                    List <string> colorList = new List <string>();

                    while (reader.Read())
                    {
                        var color = reader.GetString(0);
                        if (colorList.Contains(color) && reader.GetInt32(1) > -1)
                        {
                            isValid = false;
                            break;
                        }

                        colorList.Add(color);
                    }
                    return(isValid);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#9
0
        public void SingleSelectWithMultiClausesAndConditions_Top_WhereClauseWithSingleCondition()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color = 'black'\"},{\"ClauseType\":\"TOP\",\"ClauseValue\":\"20\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    bool isValid = true;
                    int rowCount = 0;

                    while (reader.Read())
                    {
                        if (!reader.GetString(4).Equals("Black", StringComparison.OrdinalIgnoreCase))
                        {
                            isValid = false;
                            break;
                        }
                        rowCount++;
                    }
                    return(isValid && rowCount == 20);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
        public void SelectWithOrderByClause_WithWhereClause_Desc()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color IN ('Black', 'Silver')\"},{\"ClauseType\":\"ORDERBY\",\"ClauseValue\":\"ProductAlternateKey DESC\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    List <string> listAsInResponse = new List <string>(), sortedList = new List <string>();

                    while (reader.Read())
                    {
                        var value = reader.GetString(1);
                        listAsInResponse.Add(value);
                        sortedList.Add(value);
                    }

                    sortedList.OrderByDescending(x => x);

                    return(listAsInResponse.SequenceEqual(sortedList));
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#11
0
        public void SelectWithWhereClause_SingleCondition_BetweenOperator()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"},{\"ColumnName\":\"Color\"}],\"TableClause\":[{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"ProductKey BETWEEN 12 AND 25\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    bool isValid = true;

                    while (reader.Read())
                    {
                        var key = reader.GetInt32(0);
                        if (key < 12 && key > 25)
                        {
                            isValid = false;
                            break;
                        }
                    }
                    return(isValid);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#12
0
        public void SelectWithHavingClause_WithGroupByAndWhereClause_WithCountOperation()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Count\",\"ColumnOperation\":\"COUNT\"}],\"TableClause\":[{\"ClauseType\":\"HAVING\",\"ClauseValue\":\"SUM(SafetyStockLevel) > 500\"},{\"ClauseType\":\"WHERE\",\"ClauseValue\":\"Color IN ('Black','Red')\"},{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    bool isValid = true;

                    while (reader.Read())
                    {
                        var color = reader.GetString(0);
                        if (!(color.Equals("Black", StringComparison.OrdinalIgnoreCase) || color.Equals("Red", StringComparison.OrdinalIgnoreCase)) && reader.GetInt32(1) > -1)
                        {
                            isValid = false;
                            break;
                        }
                    }
                    return(isValid);
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
        public void SelectWithOrderByClause_WithGroupByClause_Asc()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"Color\"},{\"ColumnName\":\"SafetyStockLevel\",\"Alias\":\"Safety Stock Sum\",\"ColumnOperation\":\"SUM\"}],\"TableClause\":[{\"ClauseType\":\"GROUPBY\",\"ClauseValue\":\"Color\"},{\"ClauseType\":\"ORDERBY\",\"ClauseValue\":\"SUM(SafetyStockLevel) ASC\"}]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                QueryResponseObject queryResponseObject = queryBuilder.GenerateQuery();

                PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
                {
                    List <int> listAsInResponse = new List <int>(), sortedList = new List <int>();

                    while (reader.Read())
                    {
                        var value = reader.GetInt32(1);
                        listAsInResponse.Add(value);
                        sortedList.Add(value);
                    }

                    sortedList.OrderBy(x => x);

                    return(listAsInResponse.SequenceEqual(sortedList));
                }));

                Assert.IsTrue(ValidateUsingDbResponse(queryResponseObject.Query, predicateFunction) && validator.ValidateQuery(queryResponseObject));
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#14
0
        public void SimpleSelectQueryWithSingleTable()
        {
            MsSqlQueryValidator validator = new MsSqlQueryValidator();;

            try
            {
                TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

                MsSqlQueryBuilder queryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SimpleSelectQueryWithSingleTable\",\"QueryData\":[{\"TableName\":\"AdventureWorksDW2012.dbo.DimProduct\",\"ColumnList\":[{\"ColumnName\":\"ProductKey\",\"Alias\":\"Product Key\"},{\"ColumnName\":\"ProductAlternateKey\",\"Alias\":\"Product Alternate Key\"},{\"ColumnName\":\"SafetyStockLevel\"},{\"ColumnName\":\"EnglishProductName\",\"Alias\":\"Product Name\"}],\"TableClause\":[]}],\"QueryClause\":[]}");

                validator.AddTable(table);
                Assert.IsTrue(validator.ValidateQuery(queryBuilder.GenerateQuery()));
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                validator.Destroy();
            }
        }
示例#15
0
        public void TestXmlParsing_FirstColumnIsAllowNull_3()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProductSubcategory.xml");

            Assert.AreEqual(table.Columns.First().AllowNull, false);
        }
示例#16
0
        public void TestXmlParsing_LastColumnDataType_1()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

            Assert.AreEqual(table.Columns.Last().DataType, "NVARCHAR");
        }
示例#17
0
        public void TestXmlParsing_LastColumnIsAllowNull_1()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

            Assert.AreEqual(table.Columns.Last().AllowNull, true);
        }
示例#18
0
        public void TestXmlParsing_LastColumnName_1()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

            Assert.AreEqual(table.Columns.Last().Name, "Status");
        }
示例#19
0
        public void TestXmlParsing_LastColumnName_2()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProductCategory.xml");

            Assert.AreEqual(table.Columns.Last().Name, "FrenchProductCategoryName");
        }
示例#20
0
        public void TestMsSqlSchemaParserConstructor_AsFilePath()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProduct.xml");

            Assert.AreEqual(table.Name, "AdventureWorksDW2012.dbo.DimProduct");
        }
示例#21
0
        public void TestXmlParsing_LastColumnDataType_3()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProductSubcategory.xml");

            Assert.AreEqual(table.Columns.Last().DataType, "INT");
        }
示例#22
0
        public void TestXmlParsing_TableName_3()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProductSubcategory.xml");

            Assert.AreEqual(table.Name, "AdventureWorksDW2012.dbo.DimProductSubcategory");
        }
示例#23
0
        public void TestXmlParsing_FirstColumnName_3()
        {
            TableSchema table = MsSqlSchemaParser.Parse(@"..\..\Data\DimProductSubcategory.xml");

            Assert.AreEqual(table.Columns.First().Name, "ProductSubcategoryKey");
        }