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