Filtering a View

Filtering can be implemented in the WHERE clause of the SELECT statement or by manipulating a View. The View supports dynamic filtering with the RowFilter property. To implement dynamic filtering (and sorting) you bind the DataGrid to a View, not directly to a table in the DataSet. You can dynamically set the RowFilter property in the View and then refresh the DataGrid to reflect the newly filtered data by calling DataBind(). You need to persist the filtering criteria in the ViewState.

Add a Filter and Clear Button to the Form

Using the Design view, you can add a "Filter" button to the form and name this button "buttonFilter". You will also want to add a button to clear the form. Name this button "buttonClear".

Add Event Handlers for Each Button

You can add event handlers for each button automatically by simply double clicking on a button in the Design view. The IDE will automatically register the event handler in the wizard's hidden InitializeComponent function and create an empty event handler for each button.

Persist the Filter Criteria to the ViewState

In order to support filtering on post back, you must persist the state of the filtering criteria to the ViewState. Here is the Page_Load function:

private void Page_Load(object sender, System.EventArgs e)
{
    // Put user code to initialize the page here
    // This code execute every time
    sqlDataAdapter1.Fill(dataSet11);
    view = dataSet11.Tables[0].DefaultView;
    textBoxMessage.Text= "";
       
    // This code executes the first time only
    if (!IsPostBack)
    {                                
        ViewState["LastSortOrder"]="ASC";
        ViewState["LastSortColumn"]= "au_id";
        ViewState["LastFilter"]= "";
        view.Sort = "au_id"+ " ASC";
        DataGrid1.DataBind();
    }
    else
    // This code executes only on post back
    {
        string lastSortColumn= (string)ViewState["LastSortColumn"];
        string lastSortOrder= (string)ViewState["LastSortOrder"];
        string lastFilter= (string)ViewState["LastFilter"];
        view.Sort= lastSortColumn+ " "+ lastSortOrder;
        view.RowFilter= lastFilter;
    }
}

On the initial page load, you initialize the LastFilter to an empty string:

    // This code executes the first time only
    if (!IsPostBack)
    {
        ViewState["LastSortOrder"]="ASC";
        ViewState["LastSortColumn"]= "au_id";
        ViewState["LastFilter"]= "";
        view.Sort = "au_id"+ " ASC";
        DataGrid1.DataBind();
    }

On post back, you reload the latest filtering values from the ViewState:

    else
    // This code executes only on post back
    {
        string lastSortColumn= (string)ViewState["LastSortColumn"];
        string lastSortOrder= (string)ViewState["LastSortOrder"];
        string lastFilter= (string)ViewState["LastFilter"];
        view.Sort= lastSortColumn+ " "+ lastSortOrder;
        view.RowFilter= lastFilter;
    }

Implement the Event Handlers in WebForm1.aspx.cs

Finally, you need implement the filter and clear event handlers in the WebForm1.asp.cs file. The buttonClear_Click event handler is pretty simple. It just clears the text boxes:

// clear textBoxes for INSERT or FILTER
protected void buttonClear_Click(object sender, System.EventArgs e)
{
    textBoxID.Text= "";
    textBoxLast.Text= "";
    textBoxFirst.Text= "";
    textBoxAddress.Text= "";
    textBoxCity.Text= "";
    textBoxState.Text= "";
    textBoxPhone.Text= "";
    textBoxZip.Text= "";
}

The buttonFilter_Click event handler is a bit more complicated. Note the need to convert a boolean criteria to 0 or 1. Here is our buttonFilter_Click event handler:

// Dynamically create an SQL WHERE statement
protected void buttonFilter_Click(object sender, System.EventArgs e)
{
    string @au_id= Convert(textBoxID.Text);
    string @au_lname= Convert(textBoxLast.Text);
    string @au_fname= Convert(textBoxFirst.Text);
    string @address= Convert(textBoxAddress.Text);
    string @city= Convert(textBoxCity.Text);
    string @state= Convert(textBoxState.Text);
    string @zip= Convert(textBoxZip.Text);
    string @phone= Convert(textBoxPhone.Text);
    // Use StringBuilder to concatenate strings
    StringBuilder sb=     new StringBuilder("(au_id LIKE '");
    sb.Append(@au_id);
    sb.Append("%' OR au_id IS NULL) AND (au_lname LIKE '");
    sb.Append(@au_lname);
    sb.Append("%' OR au_lname IS NULL) AND (au_fname LIKE '");
    sb.Append(@au_fname);
    sb.Append("%' OR au_fname IS NULL) AND (phone LIKE '");
    sb.Append(@phone);
    sb.Append("%' OR phone IS NULL) AND (address LIKE '");
    sb.Append(@address);
    sb.Append("%' OR address IS NULL) AND (city LIKE '");
    sb.Append(@city);
    sb.Append("%' OR city IS NULL) AND (state LIKE '");
    sb.Append(@state);
    sb.Append("%' OR state IS NULL) AND (zip LIKE '");
    sb.Append(@zip);
    sb.Append("%' OR zip IS NULL)");
    try
    {
        bool isContract= Boolean.Parse(textBoxContract.Text);
        if (isContract)
        {
            sb.Append( " AND (contract = 1 OR contract IS NULL)");
        }
        else
        {
            sb.Append(" AND (contract = 0 OR contract IS NULL)");
        }
    }
    catch{}
           
    string s= sb.ToString();
    ViewState["LastFilter"]= s; // persist filter on postback
    view.RowFilter= s;
    DataGrid1.EditItemIndex = -1;
    DataGrid1.CurrentPageIndex= 0; // important! goto first page
    DataGrid1.DataBind();
}

You build a new WHERE clause by extracting the user input from the text boxes:

string @au_id= Convert(textBoxID.Text);
string @au_lname= Convert(textBoxLast.Text);
string @au_fname= Convert(textBoxFirst.Text);
string @address= Convert(textBoxAddress.Text);
string @city= Convert(textBoxCity.Text);
string @state= Convert(textBoxState.Text);
string @zip= Convert(textBoxZip.Text);
string @phone= Convert(textBoxPhone.Text);

You need to call the utility function Convert() on the user text to double any single quotes such that O'Neil becomes O''Neil. You must do this since a singe quote is the SQL7 string delimiter!

Here is the Convert() function:

// Our single UTILITY function that doubles single quotes for use
// in an SQL statement
protected string Convert(string inString)
{
    return inString.Replace("'","''");
}

Although it is possible to build the WHERE clause with (immutable) string assignment, it is much more efficient to use a mutable StringBuilder for string concatenation:

// Use StringBuilder to concatenate strings
StringBuilder sb= new StringBuilder("(au_id LIKE '");
sb.Append(@au_id);
sb.Append("%' OR au_id IS NULL) AND (au_lname LIKE '");
sb.Append(@au_lname);
sb.Append("%' OR au_lname IS NULL) AND (au_fname LIKE '");
sb.Append(@au_fname);
sb.Append("%' OR au_fname IS NULL) AND (phone LIKE '");
sb.Append(@phone);
sb.Append("%' OR phone IS NULL) AND (address LIKE '");
sb.Append(@address);
sb.Append("%' OR address IS NULL) AND (city LIKE '");
sb.Append(@city);
sb.Append("%' OR city IS NULL) AND (state LIKE '");
sb.Append(@state);
sb.Append("%' OR state IS NULL) AND (zip LIKE '");
sb.Append(@zip);
sb.Append("%' OR zip IS NULL)");

Handling a boolean input is a bit more difficult. First you try to Parse the input to a boolean value. On success, you can append the appropriate SQL clause. On exception, the input is ignored:

try
{
    bool isContract= Boolean.Parse(textBoxContract.Text);
    if (isContract)
    {
        sb.Append( " AND (contract = 1 OR contract IS NULL)");
    }
    else
    {
        sb.Append(" AND (contract = 0 OR contract IS NULL)");
    }
}
catch{}

The newly constructed filter clause is then passed to the View:

string s= sb.ToString();
ViewState["LastFilter"]= s; // persist filter on postback
view.RowFilter= s;

Note the need to persist the "new" filtering criteria to the ViewState:

string s= sb.ToString();
ViewState["LastFilter"]= s; // persist filter on postback
view.RowFilter= s;

Finally, you reset the page index to the first page and call DataBind() to refresh the DataGrid with the updated View:

DataGrid1.EditItemIndex = -1;
DataGrid1.CurrentPageIndex= 0; // important! goto first page
DataGrid1.DataBind();

If you forget to reset the page index to one, the user may be left on a non existent page!