Dynamic Search In DataGridView

eIn this article, I am going to share with you the information of Dynamic Search in C# DataGridView.

Here, we are going to learn to display the Excel file data into DataGridView and Dynamic Seardch on the basis of Header columns in C# Windows.Forms applications. We are going to make a small single-form application which 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 which 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 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 ‘Students Data.xlsx’ Excel file which we are setting default in the code, and if we want to change Excel file, we can browse new file by clicking on 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 displaying 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.

Code

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 ComoBox List
        if (cmbSearchType.Items.Count > 0) cmbSearchType.SelectedIndex = 0;
    } catch (Exception ex) {}
}

In ‘GetTableDataFromXl’ function, we are simply converting the Excel data into ‘DataTable’. This function returns a DataTable and in 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 Form Load event. Here, we are calling ‘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 ‘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 click 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 ‘RowFilter’ Property of DataGridView. Here, "Search By" is the ComboBox 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("" + cmbSearchType.Text + " like '%{0}%'", 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

 

 

Conclusion

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.