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); }