In Focus

Filterable DataGridView using Linq and reflections

In this article we will see how to make a Filterable DataGridView using Linq and reflections.


In today's article we will see how to make a Filterable DataGridView using Linq and reflections.

OK, open your Visual Studio -> New Project -> Windows Forms Application and name it: AdvancedDataGridView.
 
OK, now go to the designer page of the AdvancedDataGridView form and add a binding navigator control to your designer page and dock it to top, by setting the Dock = Top in properties windows of your project.
 
On the binding Navigator click the last item which enables adding more items to our binding navigator, something like below on my development environment:

1.gif
 
Now click the Label option to add a new label to our toolbar (binding navigator), give the text to the label as "Search:", now add a textbox to the toolbar, much in the same way as we did for our label. 
 
Ok, now we are good to go and add a DataGridView to our form; for the same we'll drag and drop a DataGridView to our designer and dock it to fill the entire space available, for the same, go to the properties page and set the "Dock" property to "Fill". 

After performing the above steps, you should end up with something like the following :
 
2.gif  
 
For this tutorial I am going to use Sql Server for building up some data and Enterprise Library - 4.1 to pull out the data from our database.
 
The database which I am using is Adventure Works.
 
To begin with, first add reference to "Microsoft.Practices.EnterpriseLibrary.Common" and "Microsoft.Practices.EnterpriseLibrary.Data".
Next, add a configuration file to your project by right clicking the project and selecting "New Item".
 
From the given templates select the "Application Configuration File" template and to this file add the following details under :

  <connectionStrings>
    <add name="AdventureWorksConnection"
         connectionString="Server=SUCHIT-PC;database=AdventureWorks;Integrated Security=SSPI"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>
 
Above, I have added a new connectionStrings section in my app config file with name: "AdventureWorksConnection" and connectionString as "Server=[Your Server Name];database=[database name(AdventureWorks, in our case)];Integrated Security=[(true/SSPI)/false] (in case you set it to false, you have to supply additional username,password fields to the string to connect to your sql server), with true we are telling that we are going to use Windows Authentication to connect to Sql Server, which is located on same machine on which I wrote the application.

Ok, now go to the code behind of Form1.cs, and add the following reference:

using Microsoft.Practices.EnterpriseLibrary.Data;
 
Next add two private member variables to Form1.cs,

        private DataSet     m_dataSet   = null;
        private DataTable   m_dataTable = null;
 
Followed by the function :

private void LoadData()
{
    Database dbAdventureWorks = DatabaseFactory.CreateDatabase("AdventureWorksConnection");
    m_dataSet = dbAdventureWorks.ExecuteDataSet(CommandType.Text, "SELECT ProductID,Name,ProductNumber,ListPrice FROM Production.Product");
    if (m_dataSet != null)
    {
        m_dataTable = m_dataSet.Tables[0];
        if (m_dataTable != null)
        {
            m_dataTable.Columns[0].ColumnMapping = MappingType.Hidden;
            dataGridView1.DataSource = m_dataTable;
        }
    }
}
 
If you are using Visual Studio 2010, just like me then you might face an issue of reference to the classes : Database,DatabaseFactory might be resolved even after adding reference to Enterprise Lib, for this you will have to right click your project and there:

Change Target Framework from .NET Framework 4 Client Profile to .NET Framework 4, this is essential as Microsoft.Practices.EnterpriseLibrary.Data has certain dependencies that are not satisfied by .NET 4 Client Profile.
 
Till, now things were very straight forward now let's come over to the essential feature : implementing a search feature in our AdvancedGridView using Linq :

Go to the designer of the form and select the textbox which we added to our binding navigator in above steps, go to the properties page (or press F4), there select the event corresponding to text changed:
 
3.gif 
 
In the event handler for the same add the following code:

if (m_dataTable != null && m_dataTable.Rows.Count > 0)
{
    DataTable dataTable = m_dataTable;
    var result = from row in dataTable.AsEnumerable()
                 where row.Field<string>(1).StartsWith(toolStripTextBox1.Text)
                 select row;
    try
    {
        dataGridView1.DataSource = result.CopyToDataTable();
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message);
    }
}

Here we are using the extension methods available from the .NET framework for DataTable to filter out data bound to DataGridView, corresponding to every event for text changed in textbox in our binding navigator.

In the above code snippet; I am searching the column in DataGridView with index 1, row.Field<string>(1) which is of string type with text starting with the one user types in the textbox. With each text typed in, filtering of data in DataGridView happens, and as we remove the text, original data comes in back.

On running the application, the following is the output:

4.gif 
 
Let's search for entries starting with keyword : ch, typing the same in textbox, output is:
 
5.gif 
 
Improvements Required:
  1. Search is restricted to the Name field.
  2. No support printing,pdf or excel export.
We'll explore more on these points in later next part of the article series.This concludes our first part of the series, till then, ENJOY n KEEP EXPLORING.