Filter data dispalyed in a DataGridView using BindingSource and DataView

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.