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