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