public void CreateAlterAndDropTable_CreateMultipleTable_ErrorTest()
{
MsSqlQueryBuilder createQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Create\",\"QueryName\":\"CreateTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"UserName\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\",\"IsPrimary\":true},{\"ColumnName\":\"Password\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\"},{\"ColumnName\":\"Email\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"200\",\"IsUnique\":true}]},{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"UserName\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\",\"IsPrimary\":true},{\"ColumnName\":\"Password\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"50\"},{\"ColumnName\":\"Email\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"200\",\"IsUnique\":true}]}],\"QueryClause\":[]}");
MsSqlQueryBuilder dropQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Drop\",\"QueryName\":\"DropTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\"}]}");
QueryResponseObject createQueryResponseObject = createQueryBuilder.GenerateQuery();
QueryResponseObject dropQueryResponseObject = dropQueryBuilder.GenerateQuery();
try
{
ValidateByExecutingNonQuery(createQueryResponseObject.Query);
ValidateByExecutingNonQuery(dropQueryResponseObject.Query);
Validator.ValidateQuery(createQueryResponseObject, true);
Validator.ValidateQuery(dropQueryResponseObject, true);
}
catch (Exception)
{
try
{
ValidateByExecutingNonQuery(dropQueryResponseObject.Query);
}
catch (Exception)
{
throw;
}
Assert.IsTrue(true);
}
}
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();
}
}
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();
}
}
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();
}
}
public void MssqlSelect_ShouldReturn_With_Where()
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder();
var expected = "SELECT id,name FROM [User] WITH(NOLOCK) WHERE Id=1";
var actualSql = qBuilder.Select("id", "name").From("User").Where("Id=1").Sql();
Assert.AreEqual(expected, actualSql);
}
public void MssqlSelect_Top_With_Where()
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder();
var expected = "SELECT TOP 1 Id,Name FROM [User] WITH(NOLOCK) WHERE Id=1";
var actualSql = qBuilder.Select("Id,Name").Top(1).From("User").Where("Id=1").Sql();
Assert.AreEqual(expected, actualSql);
}
public void MssqlSelect_Count_With_Where()
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder();
var expected = "SELECT COUNT(1) FROM [Sale] WITH(NOLOCK) WHERE Id=1";
var actualSql = qBuilder.Count().From("Sale").Where("Id=1").Sql();
Assert.AreEqual(expected, actualSql);
}
public void MssqlSelect_Count_Without_Where()
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder();
var expected = "SELECT COUNT(1) FROM [User] WITH(NOLOCK)";
var actualSql = qBuilder.Count().From("User").Sql();
Assert.AreEqual(expected, actualSql);
}
public void MssqlSelect_CheckColumnSyntax(bool respectColumnSyntax, string expected)
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder()
{
RespectColumnSyntax = respectColumnSyntax
};
var actualSql = qBuilder.From("User").Select("id", "Name").Sql();
Assert.AreEqual(expected, actualSql);
}
public void MssqlSelect_CheckNoLock(bool useNoLock, string expected)
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder()
{
UseNoLock = useNoLock, RespectColumnSyntax = false
};
var actualSql = qBuilder.From("User", useNoLock: useNoLock).Select("id", "name").Sql();
Assert.AreEqual(expected, actualSql);
}
public void Mssql_Pagination()
{
IQueryBuilder qBuilder = new MsSqlQueryBuilder();
var expected = "SELECT Id,Name FROM [User] WITH(NOLOCK) WHERE Id=1 ORDER BY Name ASC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY";
var pagination = new PagedFilterBase()
{
Page = 1, PageSize = 10
};
var actualSql = qBuilder.Select("Id,Name").From("User").Where("Id=1").OrderBy(new OrderBy(true, "Name")).Paginate(pagination).Sql();
Assert.AreEqual(expected, actualSql);
}
public void SelectWithJoin_InnerJoin_2_Tables()
{
MsSqlQueryBuilder joinQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Select\",\"QueryName\":\"SelectTableQueryWithJoin\",\"QueryData\":[{\"TableName\":\"TestTable1\",\"ColumnList\":[{\"ColumnName\":\"ID\",\"BelongsToTable\":\"TestTable1\"},{\"ColumnName\":\"UserName\",\"BelongsToTable\":\"TestTable1\"},{\"ColumnName\":\"Email\",\"BelongsToTable\":\"TestTable1\",\"Alias\":\"Email\"},{\"ColumnName\":\"Address\",\"BelongsToTable\":\"TestTable2\"},{\"ColumnName\":\"Contact_Number\",\"BelongsToTable\":\"TestTable2\",\"Alias\":\"Contact\"}],\"TableClause\":[{\"ClauseType\":\"JOIN\",\"ClauseValue\":\"TestTable1.UserName = TestTable2.UserName\",\"JoinType\":\"Inner\",\"JoinTableName\":\"TestTable2\"}]}],\"QueryClause\":[]}");
QueryResponseObject joinQueryResponseObject = joinQueryBuilder.GenerateQuery();
ColumnAliasMap = joinQueryResponseObject.ColumnAliasMap;
try
{
PredicateFunction predicateFunction = new PredicateFunction(new Func <SqlDataReader, bool>(reader =>
{
bool isValid = true;
List <string> resultantColumns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
resultantColumns.Add(reader.GetName(i));
}
foreach (var expectedColumnName in joinQueryResponseObject.ExpectedColumnList)
{
if (!resultantColumns.Contains(GetColumnAliasName(expectedColumnName)))
{
isValid = false;
break;
}
}
return(isValid);
}));
CreateTable();
Assert.IsTrue(ValidateUsingDbResponse(joinQueryResponseObject.Query, predicateFunction) && Validator.ValidateQuery(joinQueryResponseObject));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
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();
}
}
public void CreateAlterAndDropTable_AlterSingleTable_WithConstraints()
{
MsSqlQueryBuilder alterQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Alter\",\"QueryName\":\"AlterTableQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnOperation\":\"Alter\",\"ColumnName\":\"Password\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"100\"},{\"ColumnOperation\":\"Add\",\"ColumnName\":\"Phone\",\"DataType\":\"VARCHAR\",\"MaxLength\":\"10\",\"DefaultValue\":\"test123\"},{\"ColumnOperation\":\"Drop\",\"ColumnName\":\"Email\",\"IsUnique\":true}]}],\"QueryClause\":[]}");
QueryResponseObject alterQueryResponseObject = alterQueryBuilder.GenerateQuery();
try
{
CreateTable();
Assert.IsTrue(ValidateByExecutingNonQuery(alterQueryResponseObject.Query) && Validator.ValidateQuery(alterQueryResponseObject, true));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
public void InsertAndUpdateRecord_InsertSingleRecord_SingleTable()
{
MsSqlQueryBuilder insertQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Insert\",\"QueryName\":\"InsertRecordQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"ID\",\"Values\":[1]},{\"ColumnName\":\"UserName\",\"Values\":[\"t1\"]},{\"ColumnName\":\"Password\",\"Values\":[\"pass1\"]},{\"ColumnName\":\"Email\",\"Values\":[\"email1\"]}]}]}");
QueryResponseObject insertQueryResponseObject = insertQueryBuilder.GenerateQuery();
try
{
CreateTable();
Assert.IsTrue(ValidateByExecutingNonQuery(insertQueryResponseObject.Query) && Validator.ValidateQuery(insertQueryResponseObject, true));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
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 InsertAndUpdateRecord_UpdateMultipleRecord_SingleTable()
{
MsSqlQueryBuilder updateQueryBuilder = new MsSqlQueryBuilder("{\"QueryType\":\"Update\",\"QueryName\":\"UpdateRecordQuery\",\"QueryData\":[{\"TableName\":\"TestTable\",\"ColumnList\":[{\"ColumnName\":\"Password\",\"Values\":[\"UpdatedValue\"]}]}],\"QueryClause\":[]}");
QueryResponseObject updateQueryResponseObject = updateQueryBuilder.GenerateQuery();
try
{
CreateTable();
InsertDummyRecords();
Assert.IsTrue(ValidateByExecutingNonQuery(updateQueryResponseObject.Query) && Validator.ValidateQuery(updateQueryResponseObject, true));
}
catch (Exception)
{
throw;
}
finally
{
DropTable();
}
}
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 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();
}
}
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();
}
}
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();
}
}
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();
}
}
