public void TestStringFilter()
{
string test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE ([t0].[Foreman] LIKE @p0) ORDER BY [WidgetName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT WidgetName,WidgetID FROM DimWidget ORDER BY [WidgetName];");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
q.Filters.Add(new SqlStringMatchFilter(q.Table, "Foreman", SqlWildcardDecoration.BeginsWith, "B"));
s = q.Render();
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
Assert.IsTrue((s.Parameters.Count == 1), "Parameter count should be 1.");
Assert.IsTrue(((string)s.Parameters.Single().Value == "B%"), "Parameter value is incorrect");
q.Filters.Clear();
q.Filters.Add(new SqlStringMatchFilter(q.Table, "Foreman", SqlWildcardDecoration.EndsWith, "B"));
s = q.Render();
Assert.IsTrue((s.Parameters.Count == 1), "Parameter count should be 1.");
Assert.IsTrue(((string)s.Parameters.Single().Value == "%B"), "Parameter value is incorrect");
q.Filters.Clear();
q.Filters.Add(new SqlStringMatchFilter(q.Table, "Foreman", SqlWildcardDecoration.Contains, "B"));
s = q.Render();
Assert.IsTrue((s.Parameters.Count == 1), "Parameter count should be 1.");
Assert.IsTrue(((string)s.Parameters.Single().Value == "%B%"), "Parameter value is incorrect");
}
public void TestGreaterThanFilter()
{
string test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE ([t0].[OfficeKey]>@p0) ORDER BY [WidgetName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT WidgetName,WidgetID FROM DimWidget ORDER BY [WidgetName];");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
q.Filters.Add(q.Table, "OfficeKey", SqlOperator.GreaterThan, System.Data.DbType.Int32, 10);
s = q.Render();
Assert.IsTrue((s.Parameters.Count > 0), "Parameter count should be 1.");
Assert.IsTrue(((int)s.Parameters.Single().Value == 10), "Parameter value not correct");
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
}
public void TestListFilterBoolean()
{
string test = "SELECT * FROM (SELECT * FROM Products) AS [t0] WHERE ([t0].[ProductID] IN (1,0,1)) ORDER BY [ProductName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT * FROM Products ORDER BY [ProductName]");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
List <bool> values = new List <bool>();
values.Add(true);
values.Add(false);
values.Add(true);
q.Filters.Add(new SqlListFilter <bool>(q.Table, "ProductID", values));
s = q.Render();
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
}
public void TestDateFilter()
{
string test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE (CAST([t0].[CreateDate] as date)>=@p0 AND CAST([t0].[CreateDate] as date)<=@p1) ORDER BY [WidgetName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT WidgetName,WidgetID FROM DimWidget ORDER BY [WidgetName];");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
DateTime beginDate = DateTime.Parse("2011-01-01 05:00");
DateTime endDate = DateTime.Parse("2011-01-01 23:30");
q.Filters.Add(new SqlDateFilter(q.Table, "CreateDate", beginDate, endDate));
s = q.Render();
Assert.IsTrue((s.Parameters.Count == 2), "Parameter count should be 2.");
Assert.IsTrue(s.Parameters.All(x => (DateTime)x.Value == ((DateTime)x.Value).Date), "Date params should not have time in parameter.");
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
}
public void TestListFilterInt32()
{
string test = "SELECT * FROM (SELECT * FROM Products) AS [t0] WHERE ([t0].[ProductID] IN (@p0,@p1,@p2)) ORDER BY [ProductName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT * FROM Products ORDER BY [ProductName]");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
List <int> values = new List <int>();
values.Add(1);
values.Add(2);
values.Add(3);
q.Filters.Add(new SqlListFilter <int>(q.Table, "ProductID", values));
s = q.Render();
Assert.AreEqual(test, s.CommandText, true);
Assert.AreEqual(3, s.Parameters.Count());
}
public void TestDateTimeFilter()
{
string test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE ([t0].[CreateDate]>=@p0 AND [t0].[CreateDate]<=@p1) ORDER BY [WidgetName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT WidgetName,WidgetID FROM DimWidget ORDER BY [WidgetName];");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
DateTime beginDate = DateTime.Parse("2011-01-01 05:00");
DateTime endDate = DateTime.Parse("2011-01-01 23:30");
q.Filters.Add(new SqlDateTimeFilter(q.Table, "CreateDate", beginDate, endDate));
s = q.Render();
Assert.IsTrue((s.Parameters.Count > 0), "Parameter count should be 1.");
Assert.IsTrue(((DateTime)s.Parameters.ToList()[0].Value == beginDate), "Begin date does not match parameter");
Assert.IsTrue(((DateTime)s.Parameters.ToList()[1].Value == endDate), "End date does not match parameter");
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
}
public void TestNullFilter()
{
string test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE ([t0].[Foreman] IS NULL) ORDER BY [WidgetName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT WidgetName,WidgetID FROM DimWidget ORDER BY [WidgetName];");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
q.Filters.Add(new SqlNullFilter(q.Table, "Foreman", true));
s = q.Render();
Assert.IsTrue((s.Parameters.Count <= 0), "Parameter count should be 0.");
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
test = "SELECT * FROM (SELECT WidgetName,WidgetID FROM DimWidget) AS [t0] WHERE ([t0].[Foreman] IS NOT NULL) ORDER BY [WidgetName];";
q.Filters.Clear();
q.Filters.Add(new SqlNullFilter(q.Table, "Foreman", false));
s = q.Render();
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
}
public void TestListFilterString()
{
string test = "SELECT * FROM (SELECT * FROM Products) AS [t0] WHERE ([t0].[ProductName] IN (@p0,@p1,@p2)) ORDER BY [ProductName];";
SqlSelectBuilder q = new SqlSelectBuilder("SELECT * FROM Products ORDER BY [ProductName]");
SqlStatement s;
SqlFilterCollection filters = new SqlFilterCollection();
List <string> values = new List <string>();
values.Add("blah");
values.Add("it's cool");
values.Add("woohoo");
q.Filters.Add(new SqlListFilter <string>(q.Table, "ProductName", values));
s = q.Render();
Assert.IsTrue(string.Equals(s.CommandText, test, StringComparison.OrdinalIgnoreCase), "Output CommandText does not match expected result");
Assert.IsTrue(s.Parameters.Any(x => (string)x.Value == values[0]), "Parameter at index 0 value is incorrect");
Assert.IsTrue(s.Parameters.Any(x => (string)x.Value == values[1]), "Parameter at index 1 value is incorrect");
Assert.IsTrue(s.Parameters.Any(x => (string)x.Value == values[2]), "Parameter at index 2 value is incorrect");
}
public void TestJoinMultipleFilterCollectionsWithOrLogic()
{
var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[IsActive]=@p0) AND ((([t0].[ProductCategoryID]=@p1) AND ([t0].[SupplierID]=@p2) AND ([t0].[ThingName] IN (@p3,@p4,@p5))) OR (([t0].[ProductCategoryID]=@p6) AND ([t0].[SupplierID]=@p7) AND ([t0].[ThingName] IN (@p8,@p9,@p10))));";
var builder = new SqlSelectBuilder("dbo.Product");
var listOfThings1 = new string[] { "a", "b", "c" };
var listOfThings2 = new string[] { "d", "e", "f" };
builder.Filters.Add(builder.Table, "IsActive", SqlOperator.Equal, System.Data.DbType.Boolean, true);
var productCategories = new SqlFilterCollection()
{
Logic = SqlLogic.Or
};
var productCategory1 = new SqlFilterCollection();
productCategory1.Add(builder.Table, "ProductCategoryID", SqlOperator.Equal, System.Data.DbType.Int32, 123);
productCategory1.Add(builder.Table, "SupplierID", SqlOperator.Equal, System.Data.DbType.Int32, 456);
productCategory1.Add(new SqlListFilter <string>(builder.Table, "ThingName", listOfThings1));
var productCategory2 = new SqlFilterCollection();
productCategory2.Add(builder.Table, "ProductCategoryID", SqlOperator.Equal, System.Data.DbType.Int32, 123);
productCategory2.Add(builder.Table, "SupplierID", SqlOperator.Equal, System.Data.DbType.Int32, 456);
productCategory2.Add(new SqlListFilter <string>(builder.Table, "ThingName", listOfThings2));
productCategories.Add(productCategory1);
productCategories.Add(productCategory2);
builder.Filters.Add(productCategories);
var stmt = builder.Render();
Assert.IsNotNull(stmt.CommandText);
Assert.AreEqual(expectSql, stmt.CommandText);
}