Dynamic Search In DataGridView

Introduction

In this article, I am going to share with you information on Dynamic Search in C# DataGridView.

Here, we are going to learn to display the Excel file data in DataGridView and Dynamic Search on the basis of Header columns in C# Windows. Forms applications. We are going to make a small single-form application that takes an Excel file from the user and displays the  Excel data in a Data GridView so that the user searches the data on the basis of header column names.

Targeted Audiences

People with basic knowledge of C#.

Explanation

Things to do.

• Make a C# WinForm application.
• Create Excel File / can use Exists file.
• Create UI
• Code

Create a new project and give it a suitable name, as I gave the project name ‘DynamicSearchDGV’.

After creating a project, create an Excel file with sample data, I am using the following data; if you download the project files, you will get the Excel file too. Else, you can use your own Excel file too.

Now, make a UI that contains four Buttons, two Textboxes, one ComboBox, a single DataGridView, and an OpenFileDialog tool. So, here, our UI looks like the following.

Now, I will explain to you the functionality of these components which we used in the form. We are binding the DataGridView on FormLoad. Here, we're taking the default data from the ‘Students Data.xlsx’ Excel file, which we are setting default in the code, and if we want to change the Excel file, we can browse the new file by clicking on the Browse button. After selecting the file, click on ‘Import Excel File’ to import the Excel data into DataGrdiView. The ‘Total No of Records’ field will show the number of records displayed in DataGridView, and now, you can search the data by Column Name, which lists out in the ComboBox that we placed on the top of the form.

First, we have to select the search by field, then type the search keyword, and then click on the ‘Search’ button. It will show the search result. If the user wants to see all the data, then there is a button ’Show All’ that will show you all the data again.

using System.Data;
using System.Data.OleDb;

Create LoadData Function. In this function, we are converting the Excel data into DataTable and binding this data to DataGridView, as shown in the below code.

// Loading Data From Excel to DataGridView
private void LoadData()
{
    try
    {
        cmbSearchType.Items.Clear(); // Clear Search Box Data

        DataTable dt = GetTableDataFromXl(XlFile); // Converting Excel Data into DataTable
        dataGrid2.DataSource = dt;

        lbRowCount.Text = (dataGrid2.Rows.Count - 1).ToString(); // Total No of Records

        string[] ColNameList = dt.Columns.OfType<DataColumn>().Select(x => x.ColumnName).ToArray();
        cmbSearchType.Items.AddRange(ColNameList); // Adding Column Names in ComboBox List

        if (cmbSearchType.Items.Count > 0)
            cmbSearchType.SelectedIndex = 0;
    }
    catch (Exception ex)
    {
        // Handle the exception appropriately
    }
}

In the ‘GetTableDataFromXl’ function, we are simply converting the Excel data into ‘DataTable’. This function returns a DataTable and, in the next function, displays this DataTable data into DataGridView.

private DataTable GetTableDataFromXl(string XlFile)
{
    DataTable dt = new DataTable();
    try
    {
        string Ext = Path.GetExtension(XlFile);
        string connectionString = "";

        if (Ext == ".xls") // For Excel 97-03
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
        }
        else if (Ext == ".xlsx") // For Excel 07 and greater
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
        }
        OleDbConnection conn = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand();
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        cmd.Connection = conn;
        // Fetch 1st Sheet Name
        conn.Open();
        DataTable dtSchema;
        dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
        conn.Close();
        // Read all data of fetched Sheet to a Data Table
        conn.Open();
        cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
        dataAdapter.SelectCommand = cmd;
        dataAdapter.Fill(dt);
        conn.Close();
    }
    catch (Exception ex) { }
    return dt;
}

This is the code for the Form Load event. Here, we are calling the ‘LoadData’ function on form load so it will by default, load the ‘Students Data.xlsx’ file.

private void Form1_Load(object sender, EventArgs e)
{
    LoadData();
}

In the ‘CreateDataTableFromXml’ function, we are simply converting the XML data into ‘DataTable’. This function returns a DataTable, and we are exporting this DataTable into the Excel file.

// Creating DataTable With Xml Data
public System.Data.DataTable CreateDataTableFromXml(string XmlFile)
{
    System.Data.DataTable Dt = new System.Data.DataTable();
    try
    {
        DataSet ds = new DataSet();
        ds.ReadXml(XmlFile);
        Dt.Load(ds.CreateDataReader());
    }
    catch (Exception ex) { }
    return Dt;
}

Code for the file browse button click event.

private void btnImportExcelPath_Click(object sender, EventArgs e)
{
    DialogResult drResult = OFD.ShowDialog();
    if (drResult == System.Windows.Forms.DialogResult.OK)
        txtImportExcelPath.Text = OFD.FileName;
}

Code for the file ‘Import Excel File’ button clicks event.

private void btnImportExcel_Click(object sender, EventArgs e)
{
    if (txtImportExcelPath.Text != "" && File.Exists(txtImportExcelPath.Text))
    {
        XlFile = txtImportExcelPath.Text;
        LoadData();
        MessageBox.Show("File Imported!!!");
    }
}

Code for the ‘Search’ button click event.

Here, we are searching the data by using the ‘RowFilter’ Property of DataGridView. Here, "Search By" is the combo box value that we are taking from DataGridView Header Column Names, and the Search Key is search TextBox’s Value.

private void btnSearch_Click(object sender, EventArgs e)
{
    try
    {
        ((DataTable)dataGrid2.DataSource).DefaultView.RowFilter = string.Format("{0} like '%{1}%'", cmbSearchType.Text, txtSearchBox.Text.Trim().Replace("'", "''"));
        lbRowCount.Text = (dataGrid2.Rows.Count - 1).ToString();
    }
    catch (Exception ex) { }
}

Code for the ‘Show All’ button click event.

It is reloading the data from the Excel file.

private void btnShowAll_Click(object sender, EventArgs e)
{
    LoadData();
}

Output

By using these easy and simple methods, we can search the data in DataGridView, and the best thing about this article is that there is no dependency on data format and column format. It will accept each type of data, and it allows any Excel file to load and display the data, and according to its column’s header, it will create the search criteria. Also, this is the best way to search within a DataGridView.

Please give your valuable feedback in the comments section.