Exemple #1
0
        public void TestFluentJoinMultipleFilterCollectionsWithOrLogicIndexed()
        {
            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))));";
            IDbQueryBuilder builder   = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection());

            var listOfThings1 = new string[] { "a", "b", "c" };
            var listOfThings2 = new string[] { "d", "e", "f" };

            var listOfCriteria = new Tuple <int, int, string[]>[]
            {
                new Tuple <int, int, string[]>(123, 456, listOfThings1),
                new Tuple <int, int, string[]>(123, 456, listOfThings2)
            };

            builder = builder.Where(x => x.FieldEquals <bool>("IsActive", true));
            builder = builder.WhereAny(
                listOfCriteria,
                (x, f, i) => x.FieldEquals("ProductCategoryID", f.Item1)
                .FieldEquals("SupplierID", f.Item2)
                .FieldIn("ThingName", f.Item3)
                );

            var stmt = builder.Render();

            Assert.IsNotNull(stmt.CommandText);
            Assert.AreEqual(expectSql, stmt.CommandText);
        }
        public void Test_ListQuery_Streamed_Apply()
        {
            IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());

            var queryDef = new ListQueryDefinition();

            queryDef.Order = new List <SortField>()
            {
                new SortField()
                {
                    Name = "FirstName"
                }
            };

            queryDef.Offset = 0;
            queryDef.Limit  = 50;

            var qb = ListQueryBuilder.Create(query, queryDef)
                     .NoValidation()
                     .UseStreamingResult()
                     .Apply();

            Assert.AreEqual(0, qb.PagingOptions.Value.Offset);
            Assert.AreEqual(50, qb.PagingOptions.Value.Limit);
        }
        public void Test_ListQuery_Paging()
        {
            var             expectedSql = "SELECT COUNT(1) FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);\r\nSELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p1) ORDER BY [FirstName] ASC OFFSET 0 ROWS FETCH NEXT 26 ROWS ONLY;";
            IDbQueryBuilder query       = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());

            var queryDef = new ListQueryDefinition();

            queryDef.Order = new List <SortField>()
            {
                new SortField()
                {
                    Name = "FirstName"
                }
            };

            queryDef.Filters = new List <ListFilter>()
            {
                new ListFilter()
                {
                    Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
                }
            };

            queryDef.Limit  = 25;
            queryDef.Offset = 0;

            var stmt = ListQueryBuilder.Create(query, queryDef)
                       .NoValidation()
                       .Render();

            Assert.AreEqual(expectedSql, stmt.CommandText.Trim(), true);
        }
        public void Test_ListQuery_DefaultHandler()
        {
            var             expectedSql = "SELECT [t0].[FirstName],[t0].[LastName] FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);";
            IDbQueryBuilder query       = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
            var             queryDef    = new ListQueryDefinition();

            queryDef.Fields  = new string[] { "FirstName", "LastName" };
            queryDef.Filters = new List <ListFilter>(new ListFilter[] {
                new ListFilter()
                {
                    Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
                }
            });

            var conn = new Mock.MockConnection();

            var stmt = conn
                       .QueryBuilder("dbo.Person")
                       .ListQuery(queryDef)
                       .ValidateWith <Mock.Person>()
                       .Apply()
                       .Render();

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(1, stmt.Parameters.Count);
        }
Exemple #5
0
        public void TestFluentSuffix()
        {
            string test = "SELECT * FROM [dbo].[Product] AS [t0];Suffix Value;";
            var    stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
                          .Suffix("Suffix Value")
                          .Render();

            Assert.AreEqual(test, stmt.CommandText, true);
        }
Exemple #6
0
        public void TestFluentLimit_WithoutAnyOrderBy_Throws()
        {
            var ex = Assert.ThrowsException <InvalidOperationException>(() =>
            {
                var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
                           .Limit(10, 50);
            });

            Assert.AreEqual("A query cannot have a limit or offset without an order by expression.", ex.Message);
        }
Exemple #7
0
        public void TestFluentExists()
        {
            string test = "SELECT * FROM [dbo].[Product] AS [t0] WHERE (EXISTS (SELECT 1 FROM [dbo].[ProductColor] AS [t1] WHERE (([t1].[ProductID]=@p0) AND ([t1].[Color]=@p1))));";
            //SELECT * FROM [dbo].[Product] AS [t0] WHERE (EXISTS (SELECT 1 FROM [dbo].[ProductColor] AS [t1] WHERE (([t1].[ProductID]=@p0) AND ([t1].[Color]=@p1))));
            var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
                       .Where(x => x.Exists("dbo.ProductColor", sub => sub.FieldEquals("ProductID", 1).FieldEquals("Color", "Red")))
                       .Render();

            Assert.AreEqual(test, stmt.CommandText, true);
        }
Exemple #8
0
        public void TestFluentPagingOptions()
        {
            string test = "SELECT * FROM [dbo].[Product] AS [t0] ORDER BY [PersonID] ASC OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;";

            var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
                       .OrderBy("PersonID")
                       .Limit(10, 50)
                       .Render();

            Assert.AreEqual(test, stmt.CommandText, true);
        }
        public void Test_ListQuery_DefaultLimit()
        {
            IDbQueryBuilder query    = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
            var             queryDef = new ListQueryDefinition();

            var qb = ListQueryBuilder.Create(query, queryDef)
                     .NoValidation()
                     .BeforeApply((config) => config.UseLimitOracle = false)
                     .DefaultLimit(150)
                     .Apply();

            Assert.AreEqual(150, qb.PagingOptions.Value.Limit);
        }
        public void Test_ListQuery_DefaultSort()
        {
            IDbQueryBuilder query    = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
            var             queryDef = new ListQueryDefinition();

            var qb = ListQueryBuilder.Create(query, queryDef)
                     .NoValidation()
                     .DefaultSort("FirstName")
                     .Apply();

            Assert.AreEqual(1, qb.OrderBy.Count);
            Assert.AreEqual("FirstName", qb.OrderBy.First().ColumnName);
        }
Exemple #11
0
        public void TestFluentSubQueryCount()
        {
            var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ((SELECT COUNT([t1].[ProductID]) AS [Cnt] FROM [dbo].[ProductAttribute] AS [t1] WHERE ([t1].[AttributeName]=@p0)) > @p1);";
            //               SELECT * FROM [dbo].[Product] AS [t0] WHERE ((SELECT COUNT([t1].[ProductID]) AS [Cnt] FROM [dbo].[ProductAttribute] AS [t1] WHERE ([t1].[AttributeName]=@p0)) > @p1);
            IDbQueryBuilder builder = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection());

            builder = builder.Where(where => where.SubQueryCount("dbo.ProductAttribute", "ProductID", SqlOperator.GreaterThan, 1,
                                                                 subWhere => subWhere.FieldMatch("AttributeName", SqlOperator.Equal, "Color", isAnsi: true)
                                                                 ));

            var stmt = builder.Render();

            Assert.IsNotNull(stmt.CommandText);
            Assert.AreEqual(expectSql, stmt.CommandText);
            Assert.AreEqual(2, stmt.Parameters.Count);
        }
Exemple #12
0
        public void TestFluentFieldInSubQuery()
        {
            var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[ProductID] IN (SELECT [t1].[ProductID] FROM [dbo].[ProductAttribute] AS [t1] WHERE (([t1].[AttributeName]=@p0) AND ([t1].[AttributeValue] IN (@p1,@p2)))));";
            //               SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[ProductID] IN (SELECT [t1].[ProductID] FROM [dbo].[ProductAttribute] AS [t1] WHERE (([t0].[AttributeName]=@p0) AND ([t0].[AttributeValue] IN (@p1,@p2)))));
            IDbQueryBuilder builder = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection());

            builder = builder.Where(where => where.FieldInSubQuery("ProductID", "dbo.ProductAttribute", "ProductID",
                                                                   subWhere => subWhere.FieldMatch("AttributeName", SqlOperator.Equal, "Color", isAnsi: true)
                                                                   .FieldIn("AttributeValue", new string[] { "Red", "Green" })
                                                                   ));

            var stmt = builder.Render();

            Assert.IsNotNull(stmt.CommandText);
            Assert.AreEqual(expectSql, stmt.CommandText);
            Assert.AreEqual(3, stmt.Parameters.Count);
        }
Exemple #13
0
        /// <summary>
        /// Creates a new instance of <see cref="DbQueryBuilder"/> configured in the same manner as the existing one.
        /// </summary>
        /// <returns></returns>
        public IDbQueryBuilder Fork()
        {
            var builder = new DbQueryBuilder(this.Connection, this.Transaction);

            builder.CommandTimeout = this.CommandTimeout;
            builder.Root           = this.Root;
            builder.Joins          = new List <ISqlJoin>(this.Joins);
            builder.Filters        = new List <ISqlFilter>(this.Filters);
            builder.SelectColumns  = new List <ISqlColumn>(this.SelectColumns);
            builder.Distinct       = this.Distinct;
            builder.Parameters     = new List <DbParameterValue>(this.Parameters);
            builder.OrderBy        = new List <SqlOrderColumn>(this.OrderBy);
            builder.PagingOptions  = this.PagingOptions;
            builder.Prefix         = this.Prefix;
            builder.Suffix         = this.Suffix;
            return(builder);
        }
        public void Test_ListQuery_BuildThrowsExceptionWhenNoConfig()
        {
            IDbQueryBuilder query   = new Csg.Data.DbQueryBuilder("Person", new Mock.MockConnection());
            var             request = new ListQueryDefinition();

            request.Filters = new List <ListFilter>(new ListFilter[] {
                new ListFilter()
                {
                    Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
                }
            });

            Assert.ThrowsException <System.Exception>(() =>
            {
                var stmt = ListQueryBuilder.Create(query, request)
                           .Apply();
            });
        }
        public void Test_ListQuery_FluentWithParametersFromQueryBuilder()
        {
            IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection())
                                    .AddParameter("@Foo", "Bar", DbType.String)
                                    .AddParameter("@Bar", "Baz", DbType.String);

            var queryDef = new ListQueryDefinition();

            queryDef.Limit  = 10;
            queryDef.Offset = 0;

            var dapperCmd = query.ListQuery(queryDef)
                            .NoValidation()
                            .DefaultSort("FirstName")
                            .Render()
                            .ToDapperCommand();

            Assert.AreEqual(2, (dapperCmd.Parameters as DynamicParameters).ParameterNames.Count());
            //.GetResultAsync<Person>().ConfigureAwait(false).GetAwaiter().GetResult();
        }
        public void Test_ListQuery_ApplyEventHandlers()
        {
            IDbQueryBuilder query         = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
            var             queryDef      = new ListQueryDefinition();
            bool            beforeInvoked = false;
            bool            afterInvoked  = false;

            queryDef.Order = new List <SortField>()
            {
                new SortField()
                {
                    Name = "FirstName"
                }
            };

            var qb = ListQueryBuilder.Create(query, queryDef)
                     .NoValidation()
                     .BeforeApply((config) =>
            {
                beforeInvoked = true;
                Assert.IsNotNull(config);
                Assert.AreEqual(0, config.QueryBuilder.OrderBy.Count);
                config.QueryBuilder.OrderBy.Add("LastName");
            })
                     .AfterApply((config, appliedQuery) =>
            {
                afterInvoked = true;
                Assert.IsNotNull(config);
                Assert.IsNotNull(appliedQuery);
                Assert.AreEqual(2, appliedQuery.OrderBy.Count);
                Assert.IsTrue(appliedQuery.OrderBy.Any(x => x.ColumnName == "LastName"));
            })
                     .Apply();

            Assert.IsTrue(beforeInvoked);
            Assert.IsTrue(afterInvoked);
            Assert.AreEqual(2, qb.OrderBy.Count);
        }
        public void Test_ListQuery_TypeDerivedHandler()
        {
            var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));";
            //                 SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));
            IDbQueryBuilder query    = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
            var             queryDef = new ListQueryDefinition();

            queryDef.Filters = new List <ListFilter>(new ListFilter[] {
                new ListFilter()
                {
                    Name = "PhoneNumber", Operator = ListFilterOperator.Like, Value = "555"
                }
            });

            var stmt = ListQueryBuilder.Create(query, queryDef)
                       .ValidateWith <Mock.Person>()
                       .AddFilterHandlers <Mock.PersonFilters>()
                       .Apply()
                       .Render();

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(1, stmt.Parameters.Count);
        }