public void TestEnrollmentQuery()
{
var entityTypeResolver = new StaticEntityTypeResolver();
var entityTypeRepository = new DSEntityTypeRepository(new MemoryCache(), entityTypeResolver);
var search = new DSSearch(DSEnrollment.EntityName);
search.AddSelect(DSEnrollment.Attrs.EnrollmentID);
search.AddSelect(DSEnrollment.Attrs.Student.LastName);
search.AddSelect(DSEnrollment.Attrs.Student.ID);
search.AddSelect(DSEnrollment.Attrs.Course.Title);
search.AddSelect(DSEnrollment.Attrs.Course.Department.Name);
search.AddSelect(DSEnrollment.Attrs.Grade);
search.Where.DSContains(DSEnrollment.Attrs.Course.Title, "e");
search.AddOrder(DSEnrollment.Attrs.Student.LastName);
search.Page = 1;
search.PageSize = 25;
var queryBuilder = DSSearchTestHelpers.CreateSearchBuilder(entityTypeRepository, search, true);
var expectedQuery = "SELECT e.EnrollmentID, p.LastName [Student.LastName], p.ID [Student.ID], c.Title [Course.Title], d.Name [Course.Department.Name], e.Grade" +
" FROM dbo.Enrollment e WITH(NOLOCK)" +
" JOIN dbo.Person p WITH(NOLOCK) ON e.StudentID = p.ID" +
" JOIN dbo.Course c WITH(NOLOCK) ON e.CourseID = c.CourseID" +
" JOIN dbo.Department d WITH(NOLOCK) ON c.DepartmentID = d.DepartmentID" +
" WHERE c.Title LIKE '%'+@p0+'%'";
Assert.AreEqual(DSSearchTestHelpers.AddDefaultQueryOrder(expectedQuery, "p.LastName, e.EnrollmentID"), queryBuilder.ToString());
}
/// <summary>
/// Initialize a proper search builder for the supplied filter.
/// </summary>
/// <param name="testWhere">The filter to generate a search builder for.</param>
/// <returns>Search builder for the supplied filter</returns>
private DSSQLFilterBuilder GetBuilder(DSPredicate testWhere)
{
var sqlBuilder = new DSSQLSearchBuilder();
var searchParser = new DSSearchParser(_entityTypeRepository, sqlBuilder);
var search = new DSSearch(TestObjectC.ClassName);
search.Where = testWhere;
searchParser.BuildSearch(search);
return((DSSQLFilterBuilder)sqlBuilder.WhereBuilder);
}
/// <summary>
/// Handles the Load event of the page. Displays results of a bunch of sample queries converted to JSON.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
var searchRequest = new DSSearchRequest();
DSSearch search;
searchRequest.APIContext = new DSAPIContext {
UserID = 943, SessionID = Guid.Empty.ToString()
};
search = new DSSearch(DSEnrollment.EntityName);
search.AddSelect(DSEnrollment.Attrs.EnrollmentID);
search.AddSelect(DSEnrollment.Attrs.Student.LastName);
search.AddSelect(DSEnrollment.Attrs.Student.ID);
search.AddSelect(DSEnrollment.Attrs.Course.Title);
search.AddSelect(DSEnrollment.Attrs.Course.Department.Name);
searchRequest.Search = search;
AppendSearch("Simple search", searchRequest);
search = new DSSearch(DSEnrollment.EntityName);
search.AddSelect(DSEnrollment.Attrs.EnrollmentID);
search.AddSelect(DSEnrollment.Attrs.Student.LastName);
search.AddSelect(DSEnrollment.Attrs.Student.ID);
search.AddSelect(DSEnrollment.Attrs.Course.Title);
search.AddSelect(DSEnrollment.Attrs.Course.Department.Name);
search.AddSelect(DSEnrollment.Attrs.Grade);
search.Where.Add(new DSPredicate
{
Column = DSEnrollment.Attrs.Course.Title,
Operator = DSSearchOperatorEnum.Contains,
Value = "e",
});
search.AddOrder(DSEnrollment.Attrs.Student.LastName);
searchRequest.Search = search;
AppendSearch("Search with filter", searchRequest);
searchRequest.Search.Where.OrGroup = true;
searchRequest.Search.Where.DSContains(DSEnrollment.Attrs.Course.Title, "s");
AppendSearch("Search with multiple filters", searchRequest);
}
/// <summary>
/// Executes a supplied DSSearch against a SQL Server database.
/// </summary>
/// <param name="search">The Search to execute.</param>
/// <param name="ctx">The current data context.</param>
/// <returns>Results from the search or any erorr messages generated.</returns>
public DSSearchResponse ExecuteSearch(DSSearch search, DSDataContext ctx)
{
var queryBuilder = new DSSQLSearchBuilder(); // TODO - should this be injected? possible to use different one with different Filter Builder for ex.
var searchParser = new DSSearchParser(_entityTypeRepository, queryBuilder);
searchParser.BuildSearch(search);
// TODO - Implement the Parameterize capability
var sql = queryBuilder.ToString();
var connString = ctx.GetConnectionString();
var results = new DataTable();
using (var da = new SqlDataAdapter(sql, connString))
{
da.Fill(results);
}
int totalRecords = results.Rows.Count;
if (totalRecords == search.PageSize)
{
// We need to see if there are more than just this page
var countResults = new DataTable();
using (var da = new SqlDataAdapter(queryBuilder.ToStringForCount(), connString))
{
da.Fill(countResults);
}
totalRecords = (int)countResults.Rows[0][0];
}
else
{
totalRecords += (search.Page - 1) * search.PageSize;
}
return(new DSSearchResponse
{
Errors = null,
TotalRecords = totalRecords,
Results = results.ToEntityList <DSEntity>()
});
}
public void TestAddSelect()
{
var testSearch = new DSSearch();
testSearch.AddSelect(DSEnrollment.Attrs.EnrollmentID);
Assert.AreEqual(1, testSearch.Select.Count);
Assert.AreEqual("EnrollmentID", testSearch.Select[0]);
testSearch.AddSelect(
DSEnrollment.Attrs.Course,
DSEnrollment.Attrs.Course.Title,
DSEnrollment.Attrs.Student,
DSEnrollment.Attrs.Student.FirstName);
Assert.AreEqual(5, testSearch.Select.Count);
Assert.AreEqual("Course", testSearch.Select[1]);
Assert.AreEqual("Course.Title", testSearch.Select[2]);
Assert.AreEqual("Student", testSearch.Select[3]);
Assert.AreEqual("Student.FirstName", testSearch.Select[4]);
}
/// <summary>
/// Build out the supplied search.
/// </summary>
/// <param name="search">Search definition to build.</param>
public void BuildSearch(DSSearch search)
{
_entity = _entityTypeRepository.Get(search.Type);
if (_entity == null)
{
throw new SearchBuilderException(string.Format("Entity type '{0}' not found.", search.Type));
}
_searchBuilder.AddTable(_entity.StorageName);
_searchBuilder.Distinct = search.Distinct;
foreach (var item in search.Select)
{
ParseSelect(item);
}
if (search.ShouldSerializeWhere())
{
BuildFilter(search.Where);
}
if (search.ShouldSerializeOrder())
{
foreach (var item in search.Order)
{
_searchBuilder.AddSort(GetQualifiedName(item.Name), item.Desc);
}
}
// Have to add in default sort by the Key to ensure we will be paging consistently
_searchBuilder.AddSort(GetQualifiedName(_entity.KeyName), false);
// Apply paging
_searchBuilder.AddPaging(search.Page, search.PageSize);
_searchBuilder.Complete();
}
/// <summary>
/// Generate a Search Builder instance based on the supplied information.
/// </summary>
/// <param name="entityTypeRepository">List of entity definitions known to the system.</param>
/// <param name="search">Search instance to build.</param>
/// <param name="searchParser">Output parameter to return the initialized Search Parser.</param>
/// <param name="parameterize">When true, will parameterize the search, otherwise will not.</param>
/// <returns>Search Builder instance run against supplied Search.</returns>
public static IDSSearchBuilder CreateSearchBuilder(IDSEntityTypeRepository entityTypeRepository, DSSearch search, out DSSearchParser searchParser, bool parameterize = false)
{
var queryBuilder = new DSSQLSearchBuilder(parameterize);
searchParser = new DSSearchParser(entityTypeRepository, queryBuilder);
searchParser.BuildSearch(search);
return(queryBuilder);
}
/// <summary>
/// Generate a Search Builder instance based on the supplied information.
/// </summary>
/// <param name="entityTypeRepository">List of entity definitions known to the system.</param>
/// <param name="search">Search instance to build.</param>
/// <param name="parameterize">When true, will parameterize the search, otherwise will not.</param>
/// <returns>Search Builder instance run against supplied Search.</returns>
public static IDSSearchBuilder CreateSearchBuilder(IDSEntityTypeRepository entityTypeRepository, DSSearch search, bool parameterize = false)
{
DSSearchParser searchParser;
return(CreateSearchBuilder(entityTypeRepository, search, out searchParser, parameterize));
}
