Easy QueryBuilder - A User-Friendly Ad-Hoc Advanced Search Solution
 
Published: 13 Apr 2010
Unedited - Community Contributed
Abstract
Constructing an easy and powerful QueryBuilder interface becomes more important for complex data grid filtering and accurate reporting services. In this article, I'll discuss how to build a query search engine using ASP.NET AJAX and dynamic SQL. The main goal is to provide an interactive interface to allow users select query attributes, operators, attribute values, and T-SQL operators so that the data context query list can be easily composed and a search engine is invoked.
by Bing Wang
Feedback
Average Rating: 
Views (Total / Last 10 Days): 22583/ 53

Introduction

One of the projects that I have been working on was to provide a user interface to manage corporate security information such as personnel, Platform, LoginID, etc.  I have designed and implemented a smart data search engine to filter various data based on queries from users. QueryBuilder was the choice for this design. To share the ideas with the ASP.NET community, the web form and major code snippets are listed below.

A User Control

In this web project, the query builder was designed as a user control that can be called from several parent pages such as Search Person, Search Platform, and Search ID. The figure below shows the design form of query builder and data grid. The query builder consists of four key elements: T-SQL operator (e.g., AND), query attribute which is dynamically read from tables (e.g., Last Name), query operator (e.g., Begins With), and the HTML control which are based on the attribute data types (e.g., text boxes, list boxes or calendar controls).

Web Form with AJAX

With the implementation of AJAX, only changes are updated on the web form.  Other sections remain unchanged. This avoids unnecessary full page reloading.

Event Code

Users can easily compose the query context such as adding queries to filter list, deleting query item, or navigating the filter through the list. Update panel makes sure only the list box is posted back. See the C# code snippets:

Listing 1 - Add List Item

protected void btnAddFilter_OnClick(object sender, EventArgs e)
    {
        string filterText = "", filterValue = "", filterTextValue = "";
        if (lstCriteria.Items.Count > 0)
        {
            filterText = ddlANDOR.SelectedItem.Text + " ";
            filterValue = ddlANDOR.SelectedItem.Text + ";";
            filterTextValue = ddlANDOR.SelectedItem.Text + ";";
        }
        filterText += ddlCols.SelectedItem.Text + " ";
        filterTextValue += ddlCols.SelectedItem.Text + ";";
        filterValue += ddlCols.SelectedValue + ";";
        filterText += ddlOperator.SelectedValue + " ";
        filterValue += ddlOperator.SelectedValue + ";";
        filterTextValue += ddlOperator.SelectedValue + ";";
        filterText += txtCriteria.Text.Trim();
        filterTextValue += txtCriteria.Text.Trim();
        filterValue += txtCriteria.Text.Trim();
        lstCriteria.Items.Add(new ListItem(filterText, filterTextValue + 
            "|" + filterValue));
        upCriteria.Update();
  }

Listing 2 - Delete List Item

protected void btnRemove_OnClick(object sender, EventArgs e)
    {
        if ((lstCriteria.SelectedIndex == 0) && (lstCriteria.Items.Count > 1))
        {
            ListItem item = lstCriteria.Items[1];
            int idx = item.Text.IndexOf(' ');
            string strAndOr = item.Text.Substring(0, idx);
            string[] itemValues = item.Value.Split("|".ToCharArray());
            string filterTextValue = itemValues[0].Substring(idx + 1,
                  itemValues[0].Length - idx - 1);
            string filterValue = itemValues[1].Substring(idx + 1, 
                  itemValues[1].Length - idx - 1);
            item.Text = item.Text.Substring(idx + 1, item.Text.Length - idx - 1);
            item.Value = filterTextValue + "|" + filterValue;
        }
        lstCriteria.Items.RemoveAt(lstCriteria.SelectedIndex);
    }

Listing 3 - Move Up List Item

protected void btnUp_OnClick(object sender, EventArgs e)
    {
        if (lstCriteria.SelectedIndex == 1)
        {
            ListItem item = lstCriteria.SelectedItem;
            ItemMove(item);
        }
        if (lstCriteria.SelectedIndex > 0)
        {
            int idxSelected = lstCriteria.SelectedIndex;
            int idxOther = idxSelected - 1;
            lstCriteria.Items.Insert(idxSelected + 1, 
                  lstCriteria.Items[idxOther]);
            lstCriteria.Items.RemoveAt(idxOther);
        }
    }

Listing 4 - Move Down List Item

protected void btnDown_OnClick(object sender, EventArgs e)
    {
        if ((lstCriteria.SelectedIndex == 0) && (lstCriteria.Items.Count > 1))
        {
            ListItem item = lstCriteria.Items[1];
            ItemMove(item);
        }
        if (lstCriteria.SelectedIndex < lstCriteria.Items.Count - 1)
        {
            int idxSelected = lstCriteria.SelectedIndex;
            int idxOther = idxSelected + 1;
            lstCriteria.Items.Insert(idxSelected, lstCriteria.Items[idxOther]);
            lstCriteria.Items.RemoveAt(idxOther + 1);
        }
    }
Conclusion

Equipped with a simple interface with clean event-driven code, this QueryBuilder has all the necessary functionality and exhibits excellent performance efficiency. It can be created as a user control for ASP.NET web application, a win form for windows application or plug-in template for certain reports.



User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-11 8:16:46 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search