DataGridView Filter & Expressions C# 5.0


Often we need to filter a DataGridView in our database programs. The C# datagrid is the best tool for database display & modification. There is an easy shortcut to filtering a DataTable in C# for the DataGridView. This is done by simply applying an expression to the required column. The expression contains the value of the filter to be applied. The filtered DataTable must be then set as the DataSource of the DataGridView.



In this program we have a simple DataTable containing a total of 5 columns: Item Name, Item Price, Item Quantity, Item Tax & Item Total. This DataTable is then displayed in the C# datagrid. The fourth & fifth columns are to be calculated as a multiplied value(by multiplying 2nd and 3rd columns). 

We simply add rows with values set for three columns. We let the Tax & Total columns get calculated automatically through the Expression value of the Columns. The application of expressions is simple, just type what you want it to be! For example, if you want the 10% Tax Column to generate values automatically, you can set the Column.Expression as: 

"<Column1> = <Column2> * <Column3> * 0.1"

where the Columns are Tax, Price & Quantity respectively. Note a hint here: the columns are specified as a decimal type and 0.1 means precisely 10%.

Finally after all rows have been set, we can apply appropriate filters on the columns in the C# datagrid control. This is accomplished by setting the filter value in one of the three TextBoxes & clicking the corresponding filter button. The Filter expressions are calculated by simply picking up the values from the validating TextBoxes & matching them to their Column name. 

Note that the text changes dynamically on the Button. The Text property allows you to easily preview a filter value. In this way we achieve the TextBox validation. 

namespace Filter_a_DataGridView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        DataTable dt = new DataTable(); // Form Table that Persists  

      //throughout and its structure and Column values never change
       
      //once you click the "Show Table with Tax & Amount" Button

        private void Form1_Load(object sender, EventArgs e)
        {
            DataColumn Item_Name  = new DataColumn("Item_Name",    // Define
            Type.GetType("System.String"));                        //
            DataColumn Item_Price = new DataColumn("Item_Price",   // the input
            Type.GetType("System.Decimal"));                       //
            DataColumn Item_Qty   = new DataColumn("Item_Qty",     // Columns
            Type.GetType("System.Decimal"));

            DataColumn Item_Tax = new DataColumn("Item_tax",       // Define Tax
            Type.GetType("System.Decimal"));
            // "Item_Tax" column is calculated (10% Tax)
            Item_Tax.Expression = "Item_Price * Item_Qty * 0.1" 

            DataColumn Item_Total = new DataColumn("Item_Total",   // Define Total
            Type.GetType("System.Decimal"));
            // "Item_Total" column is calculated as (Price * Qty + Tax)
            Item_Total.Expression = "Item_Price * Item_Qty + Item_Tax";

            dt.Columns.Add(Item_Name);    // Add 4
            dt.Columns.Add(Item_Price);   // Columns
            dt.Columns.Add(Item_Qty);     // to
            dt.Columns.Add(Item_Tax);     // the
            dt.Columns.Add(Item_Total);   // Datatable
        }


        private void btnInsert_Click(object sender, EventArgs e)
        {
            dt.Rows.Add(txtItemName.Text, txtItemPrice.Text, txtItemQty.Text);
           
            MessageBox.Show("Row Inserted");
        }

        private void btnShowFinalTable_Click(object sender, EventArgs e)
        {
            this.Height = 637; // Extend

            dgv.DataSource = dt;
            btnShowFinalTable.Enabled = false;
        }


        private void btnPriceFilter_Click(object sender, EventArgs e)
        {
            // Creating a new table allows to preserve
            //original data and work the filters on the new DataTable

            DataTable NewDT = new DataTable();  //Create a new DataTable
            NewDT = dt.Copy();                  //Copy existing data
           
            //Apply Filter Value
            NewDT.DefaultView.RowFilter = "Item_Price = ' " + txtPriceFilter.Text + " ' ";

            //Set new table as DataSource
            dgv.DataSource = NewDT;            
        }

        private void txtPriceFilter_TextChanged(object sender, EventArgs e)
        {
            // Change/Update Button Text Dynamically
            btnPriceFilter.Text = "Filter DataGridView by Price : " + txtPriceFilter.Text;
        }


        private void btnQtyFilter_Click(object sender, EventArgs e)
        {
            // Creating a new table allows to preserve
            //original data and work the filters on the new DataTable

            DataTable NewDT = new DataTable();
            NewDT = dt.Copy();
            NewDT.DefaultView.RowFilter = "Item_Qty = ' " + txtQtyFilter.Text + " ' ";
            dgv.DataSource = NewDT;
        }

        private void txtQtyFilter_TextChanged(object sender, EventArgs e)
        {
            // Change/Update Button Text Dynamically
            btnQtyFilter.Text = "Filter DataGridView by Total : " + txtQtyFilter.Text;
        }


        private void btnTotalFilter_Click(object sender, EventArgs e)
        {
            // Creating a new table allows to preserve
            //original data and work the filters on the new DataTable

            DataTable NewDT = new DataTable();
            NewDT = dt.Copy();
            NewDT.DefaultView.RowFilter = "Item_Total = ' " + txtTotalFilter.Text + " ' ";
            dgv.DataSource = NewDT;
        }

        private void txtTotalFilter_TextChanged(object sender, EventArgs e)
        {
            // Change/Update Button Text Dynamically
            btnTotalFilter.Text = "Filter DataGridView by Total : " + txtTotalFilter.Text;
        }


        private void btnFilterReset_Click(object sender, EventArgs e)
        {
            DataTable NewDT = new DataTable();
            NewDT = dt.Copy();
            dgv.DataSource = NewDT;
        }
    }
}




Please Note :
** Do not Copy & Paste code written here ; instead type it in your Development Environment
** Testing done in .Net Framework 4.5 but code should be very similar for previous versions of .Net
** All Program Codes on Blog are 100%  tested & running.

Custom Search