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