Filterable DataGridView using Linq and reflections

Filterable DataGridView Implementation

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 add a binding navigator control to your designer page, and dock it to the top, by setting the Dock = Top in the 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.

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.

For this tutorial, I am going to use SQL Server to build up some data and Enterprise Library - 4.1 to pull out the data from our database.

The database that I am using is Adventure Works.

To begin with, first, add a 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.

<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 the 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 an 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 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 a 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 the .NET 4 Client Profile.

Till now things were very straightforward 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 that we added to our binding navigator in the above steps, go to the properties page (or press F4), there select the event corresponding to the text changed:

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, the original data comes back.

On running the application, the following is the output.

Let's search for entries starting with keyword: ch, typing the same in the textbox, the output is.

Improvements Required

1. Search is restricted to the Name field.
2. No support for 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 and KEEP EXPLORING.