public void SqlListFilter_GetsCorrectType()
{
var table = SqlTable.Create("foo");
var filter1 = new SqlListFilter <int>(table, "Foo", new List <int>());
var filter2 = new SqlListFilter <int?>(table, "Foo", new List <int?>());
Assert.AreEqual(DbType.Int32, filter1.DataType);
Assert.AreEqual(DbType.Int32, filter2.DataType);
}
/// <summary>
/// Adds a (SELECT COUNT({countFieldName}) FROM {sqlText} WHERE {subQueryConditions}) {subQueryOperator} {countValue} condition to the query;
/// </summary>
/// <param name="where"></param>
/// <param name="sqlText"></param>
/// <param name="countColumnName"></param>
/// <param name="operator"></param>
/// <param name="countValue"></param>
/// <param name="subQueryWhere"></param>
/// <returns></returns>
public static IDbQueryWhereClause SubQueryCount(this IDbQueryWhereClause where, string sqlText, string countColumnName, SqlOperator @operator, int countValue, Action <IDbQueryWhereClause> subQueryWhere)
{
var sqf = new SqlCountFilter(where.Root, SqlTable.Create(sqlText), countColumnName, @operator, countValue);
var sqfWhere = new DbQueryWhereClause(sqf.SubQueryTable, SqlLogic.And);
subQueryWhere(sqfWhere);
sqfWhere.ApplyTo(sqf.SubQueryFilters);
where.AddFilter(sqf);
return(where);
}
/// <summary>
/// Adds an EXISTS(selectStatement) filter critera.
/// </summary>
/// <param name="where"></param>
/// <param name="innerQuery">The value to render as the inner SELECT statement</param>
/// <returns></returns>
public static IDbQueryWhereClause Exists(this IDbQueryWhereClause where, string sqlText, Action <IDbQueryWhereClause> subQueryFilters)
{
var innerTable = SqlTable.Create(sqlText);
var innerQuery = new SqlSelectBuilder(innerTable);
var innerWhere = new DbQueryWhereClause(innerTable, SqlLogic.And);
innerQuery.Columns.Add(new SqlRawColumn("1"));
subQueryFilters(innerWhere);
innerWhere.ApplyTo(innerQuery.Filters);
where.AddFilter(new Sql.SqlExistFilter(innerQuery));
return(where);
}
public void TestForkCreatesShallowClone()
{
var conn = new MockConnection();
var query = new DbQueryBuilder("dbo.TableName", conn);
query.OrderBy.Add(new SqlOrderColumn()
{
ColumnName = "Foo"
});
query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("Blah")));
query.AddFilter(new SqlNullFilter(query.Root, "Foo", false));
query.SelectColumns.Add(new SqlColumn(query.Root, "Foo"));
query.PagingOptions = new SqlPagingOptions()
{
Offset = 10,
Limit = 100
};
query.Parameters.Add(new DbParameterValue()
{
ParameterName = "@Param1",
DbType = System.Data.DbType.Int32,
Size = 4,
Value = 123
});
query.Distinct = true;
query.CommandTimeout = 123;
var fork = query.Fork();
query.OrderBy.Clear();
query.AddFilter(new SqlNullFilter(query.Root, "Bar", false));
query.SelectColumns.Clear();
query.PagingOptions = null;
query.Parameters.Clear();
query.CommandTimeout = 999;
query.Distinct = false;
query.AddJoin(new SqlJoin(query.Root, SqlJoinType.Cross, SqlTable.Create("blah2")));
Assert.AreEqual(1, fork.OrderBy.Count);
Assert.AreEqual(1, fork.SelectColumns.Count);
Assert.AreEqual(1, fork.Parameters.Count);
Assert.AreEqual(10, fork.PagingOptions.Value.Offset);
Assert.AreEqual(100, fork.PagingOptions.Value.Limit);
Assert.AreEqual(true, fork.Distinct);
Assert.AreEqual(123, fork.CommandTimeout);
}
/// <summary>
/// Adds a {columnName} IN | NOT IN (SELECT {subQueryColumnName} FROM {sqlText} WHERE {SubQueryConditions})
/// </summary>
/// <param name="where"></param>
/// <param name="columnName"></param>
/// <param name="sqlText"></param>
/// <param name="subQueryColumnName"></param>
/// <param name="condition"></param>
/// <param name="subQueryFilters"></param>
/// <returns></returns>
internal static IDbQueryWhereClause FieldSubQuery(IDbQueryWhereClause where, string columnName, string sqlText, string subQueryColumnName, SubQueryMode condition, Action <IDbQueryWhereClause> subQueryFilters)
{
var subQueryFilter = new Csg.Data.Sql.SqlSubQueryFilter(where.Root, SqlTable.Create(sqlText))
{
ColumnName = columnName,
Condition = condition,
SubQueryColumn = subQueryColumnName,
};
var subQueryWhere = new DbQueryWhereClause(subQueryFilter.SubQueryTable, SqlLogic.And);
subQueryFilters(subQueryWhere);
subQueryWhere.ApplyTo(subQueryFilter.SubQueryFilters);
where.AddFilter(subQueryFilter);
return(where);
}
public void TestSubQueryCountFilter()
{
string test = "SELECT * FROM [DimWidget] AS [t0] WHERE ((SELECT COUNT([t1].[WidgetCommentID]) AS [Cnt] FROM [dbo].[WidgetComment] AS [t1] WHERE ([t0].[WidgetID]=[t1].[WidgetID])) > @p0);";
// SELECT * FROM [DimWidget] AS [t0] WHERE ((SELECT COUNT([t1].[WidgetCommentID]) AS [Cnt] FROM [dbo].[WidgetComment] AS [t1] WHERE ([t0].[WidgetID]=[t1].[WidgetID])) > @p0);
SqlSelectBuilder q = new SqlSelectBuilder("DimWidget");
var widgetComment = SqlTable.Create("dbo.WidgetComment");
var sqf = new SqlCountFilter(q.Table, widgetComment, "WidgetCommentID", SqlOperator.GreaterThan, 3);
sqf.SubQueryFilters.Add(new SqlColumnCompareFilter(q.Table, "WidgetID", SqlOperator.Equal, widgetComment));
q.Filters.Add(sqf);
var s = q.Render();
Assert.IsTrue((s.Parameters.Count > 0), "Parameter count should be 1.");
Assert.AreEqual(test, s.CommandText, true);
}