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_ListFilterOperator_Like()
        {
            var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName] LIKE @p0);";
            var queryDef    = new ListQueryDefinition()
            {
                Filters = new List <ListFilter>()
                {
                    new ListFilter()
                    {
                        Name = "FirstName", Operator = ListFilterOperator.Like, Value = "Bob"
                    }
                }
            };

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

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(1, stmt.Parameters.Count);
            // default is a beginswith search
            Assert.AreEqual("Bob%", stmt.Parameters.First().Value.ToString());
        }
        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);
        }
 /// <summary>
 /// Creates a <see cref="ListQueryBuilder"/> from the given query builder and query definition.
 /// </summary>
 /// <param name="queryBuilder"></param>
 /// <param name="queryDefinition"></param>
 /// <returns></returns>
 public static IListQueryBuilder Create(IDbQueryBuilder queryBuilder, ListQueryDefinition queryDefinition)
 {
     return(new ListQueryBuilder()
     {
         Configuration = new ListQueryBuilderConfiguration()
         {
             QueryBuilder = queryBuilder,
             DataAdapter = new DapperListDataAdapter(queryBuilder.Connection, queryBuilder.Transaction),
             QueryDefinition = queryDefinition,
             Validations = new Dictionary <string, ListFieldMetadata>(StringComparer.OrdinalIgnoreCase),
             Handlers = new Dictionary <string, ListQueryFilterHandler>(StringComparer.OrdinalIgnoreCase)
         }
     });
 }
        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_ListFilterOperator_IsNullFalse()
        {
            var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName] IS NOT NULL);";
            var queryDef    = new ListQueryDefinition()
            {
                Filters = new List <ListFilter>()
                {
                    new ListFilter()
                    {
                        Name = "FirstName", Operator = ListFilterOperator.IsNull, Value = false
                    }
                }
            };

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

            Assert.AreEqual(expectedSql, stmt.CommandText);
        }
        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_BuildWithNoValidation()
        {
            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 stmt = ListQueryBuilder.Create(query, queryDef)
                       .NoValidation()
                       .UseStreamingResult()
                       .Apply()
                       .Render();

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(1, stmt.Parameters.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);
        }
        public void Test_ListFilterOperator_Between()
        {
            var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE (([t0].[FirstName]>=@p0) AND ([t0].[FirstName]<=@p1));";
            var queryDef    = new ListQueryDefinition()
            {
                Filters = new List <ListFilter>()
                {
                    new ListFilter()
                    {
                        Name = "FirstName", Operator = ListFilterOperator.Between, Value = new string[] { "Bob", "Dole" }
                    }
                }
            };

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

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(2, stmt.Parameters.Count);
        }
        public void Test_ListFilterOperator_NotEquals()
        {
            var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]<>@p0);";
            var queryDef    = new ListQueryDefinition()
            {
                Filters = new List <ListFilter>()
                {
                    new ListFilter()
                    {
                        Name = "FirstName", Operator = ListFilterOperator.NotEqual, Value = "Bob"
                    }
                }
            };

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

            Assert.AreEqual(expectedSql, stmt.CommandText);
            Assert.AreEqual(1, stmt.Parameters.Count);
        }
Exemple #15
0
 public static IListQueryBuilder ListQuery(this IDbQueryBuilder queryBuilder, ListQueryDefinition queryDef)
 {
     return(Csg.ListQuery.Sql.ListQueryBuilder.Create(queryBuilder, queryDef));
 }
 public Task <ListQueryResult <WeatherForecast> > GetWidgetsAsync(ListQueryDefinition queryDef)
 {
     throw new NotImplementedException();
 }