Filter data dispalyed in a DataGridView using BindingSource and DataView

Introduction

Filter data is one among many important functionalities that users may want to have in a Website. You can imagine a case in witch a user needs to know every thing about his sales turnover related to a given product(s) by week, by month, by trimester, by semester or even during a given period. In this case we have two alternatives.

Alternative one

1. Build an SQL request (in a data base case for e.g.: Oracle,SQL server, Access) and parameter the ADO objects such as connection and command conforming to this request.
2. Use Xpath or/and XSLT transformation or/and others like objects in System.Xml namespace to get output data from XML files

Alternative two

Set the either the BindingSource or DataView object as a DataGridView data source and then use, respectively, either the Filter ( ) or RowFilter( ) method, and that can be applied in both situations, I mean either the data source is a data base or an XML/XSL file.

To explain how to do this I invite you to follow those steps

For this tutorial we use the Products table of the Northwind Database sample that you can download from the Microsoft official web site, you can even use another data source, never mind, because the procedure is the same.

First of all you create a new Windows application:

1. Open the IDE Visual Studio 2005 for e.g
2. Select File --> New Project --> Visual C# --> Windows --> WindowsApplication
3. Name the project and click OK
4. Drag and drop a DataGridView into the Form1
5. Rename the DataGidView for e.g myGridView
6. Drag and drop a Label control into the Form1 and change its text property to Products list from the minimum price to
7. Drag and drop a combo box into the Form 1 and rename it for e.g PriceCombo

The Form1 will appear as below

Once the Form1 design is done, implement the Form1_load(Object sender, EventArgs e) as follow:

BindingSource use case:

BindingSource myBindingSource;

DataSet myDataSet;
private void Form1_Load(object sender, EventArgs e)
{
    using (SqlConnection oConnection = new SqlConnection(/*Parameter the connectionstring recording to the personal data
    source, for my case I use*/"Data Source = STANDARD;Initial Catalog = Northwind; Integrated Security = true"))
    {
        SqlCommand oCommand = new SqlCommand("Select Products.* From Products", oConnection);
        SqlDataAdapter oAdapter = new SqlDataAdapter(oCommand);
        myDataSet = new DataSet();
        oAdapter.Fill(myDataSet);
    }
    myBindingSource = new BindingSource();
    myBindingSource.DataSource = myDataSet;
    myBindingSource.DataMember = myDataSet.Tables[0].TableName;
    myGridView.DataSource = myBindingSource;
}

When you fire up the application all data in the table Products appear in the data grid view. Now, If you want to filter data by price interval for e.g. Populate the Price combo with some prices, you can even bind it to a given data source but in order to simplify the deal, one is satisfied quite simply to do the first alternative. So fill it with those values 0, 10, 20, 30, 50 for e.g.

Now, double click on the Price combo and implement its PriceCombo_SelectedIndexChanged(object sender, EventArgs e) with the following code:

private void PriceCombo_SelectedIndexChanged(object sender, EventArgs e)
{
    if (PriceCombo.Text == "0") myBindingSource.Filter = "UnitPrice <= 0";
    if (PriceCombo.Text == "10") myBindingSource.Filter = "UnitPrice <= 10";
    if (PriceCombo.Text == "20") myBindingSource.Filter = "UnitPrice <= 20";
    if (PriceCombo.Text == "30") myBindingSource.Filter = "UnitPrice <= 30";
    if (PriceCombo.Text == "50") myBindingSource.Filter = "UnitPrice <= 50";
}

DataView use case

Rem: If DataView control doesn't appear in the Tool box, select Choose Tool Box in Tools menu, then select the .Net Framework Components tab. You can find DataView component there, select it and click OK.

DataView myDataView;
DataSet myDataSet;
private void Form1_Load(object sender, EventArgs e)
{
    using (SqlConnection oConnection = new SqlConnection("Data Source = STANDARD;Initial Catalog = Northwind; Integrated 
    Security = true  "))
    {
        SqlCommand oCommand = new SqlCommand("Select Products.* From Products", oConnection);
        SqlDataAdapter oAdapter = new SqlDataAdapter(oCommand);
        myDataSet = new DataSet();
        oAdapter.Fill(myDataSet);
    }
    myDataView = new DataView();
    myDataView.Table = myDataSet.Tables[0];
    myGridView.DataSource = myDataView;
}

When you run the application all data appear as in the previous case. Now, If you want to filter data by price interval. Populate the Price combo with some values 10, 30, 50, 70, 90 for e.g.

Now double click on the Price combo and implement its PriceCombo_SelectedIndexChanged(object sender, EventArgs e) as mentioned below:

private void PriceCombo_SelectedIndexChanged(object sender, EventArgs e)
{
    if (PriceCombo.Text == "0")  myDataView.RowFilter = "UnitPrice <= 0";
    if (PriceCombo.Text == "10") myDataView.RowFilter = "UnitPrice <= 10";
    if (PriceCombo.Text == "20") myDataView.RowFilter = "UnitPrice <= 20";
    if (PriceCombo.Text == "30") myDataView.RowFilter = "UnitPrice <= 30";
    if (PriceCombo.Text == "50") myDataView.RowFilter = "UnitPrice <= 50";
}

Now, run the application and select value among those in the Price combo and you will remark that only products with prices inferior or equal to the value mentioned in the Combo price are listed and the others are hided.

For this example the price is choosen as a criteria of selection but you can choose whatever you want in terms of Data members such as ProductID, ProductName and others to create selection criteria.

Now, I give you some details and remarks about how to deal with the filter expression :

First of all let us say that all expressions used to filter data have string as format

1. If the member type is string you must wrap the criteria value in quotation marks like this sample : "ProductID = 'PR0001k12'"
2. If the member type is number you can write the expression like this "Maximum = 50"
3. If the member has a name composed by two or more words that are separated by space, in a such case, you must wrap it in brakets [ ] like this sample: "[First Name] = 'Dihia' "
4. If the member has a name that contains one of those characters \n (newline) \t (tab) \r (carriage return) ~ ( ) # \ / = > < + - * % & | ^ ' " [ ]
   Those symbols are considered as special characters, so you must another once, as the example above I mean the third remark, wrap it in brakets [ ] like this sample: "[FirstName#] = 'Dihia' "
5. If the member name has this format xxx[ ],[ ]xxx or x[ ]xx, because the brakets are used here as special characters, you must use a slash ‘\' to escape the brakets as follow: "[First Name[\]] = 'Dihia' "
6. If the member type is a date, in this case, you must wrap the criteria value in pound signs # such as "[Date of birth] = #11/04/78# ", the month at first, the day at second and the year at third position.
7. You can combine more than one criteria at once by using AND,OR and NOT key words for example "(Maximum = 50 OR Minimum = 20) AND Weight = 30"
8. You can use operators like =,<,>,<=,>=,IN,LIKE for example: "(Maximum = 50 OR Minimum > 20) AND [Product Name] LIKE 'Moster' "
9. The arithmetic operators + - * / % can also be used as follow: "[Reduced Price] =[Original Price] * 1.2 "
10. You can use agregate too, like Sum:Sum,Max:Maximum,Min:Minimum,Avg:Average,Var:Variance ,StDev:Standard deviation as follow "[Estimated Price] = (Price – Avg(Price))/StDev(Price)" if, of Corse, the statistic variable Price follows a stochastic process related to the normal standard Gauss' law.

These are the most importants methods that one can use in order to build a filter expression.