Friday, June 29, 2012

How to search record in DataTable?


DataTable is server side object in .Net which is collection of Rows and Columns. It represents the in-memory table's data. It can hold data of any single table but using join we can hold data from multiple table. In programming it is used to hold data after getting data from database.

Generally we use DataTable to hold result-set (query's result), but in some of the cases we want to perform searching operation on DataTable. Here I am explaining -
How to search record in DataTable?

If we want to search any data in DataTable object then it can be done by using looping, but this is not a good approach because DataTable have some inbuilt methods to complete this task.

There are following methods-

1. Find () 
- Using this method we can perform searching on DataTable based on Primary  key, it   
                    returns DataRow object.

2. Select()
- Using this method we can perform searching on DataTable based on search criteria   
                    means by using column name we can search , it returns array of DataRow object.

Here I am explaining both methods, Follow this code-

 private void btnSearch_Click(object sender, EventArgs e)
                //Creating DataTable object
                DataTable dtRecords = new DataTable();

                //Creating DataColumn object
                DataColumn colName = new DataColumn("Name");
                DataColumn colID = new DataColumn("ID");

                //Adding DataColumns to DataTable

                //Creating Array of DataColumn to set primary key for DataTable
                DataColumn[] dataColsID = new DataColumn[1];
                dataColsID[0] = colID;

                //Setting primary key
                dtRecords.PrimaryKey = dataColsID;

                //Adding static data to DataTable by creating DataRow object
                DataRow row1 = dtRecords.NewRow();
                row1["ID"] = 1;
                row1["Name"] = "Rajendra";

                DataRow row2 = dtRecords.NewRow();
                row2["ID"] = 2;
                row2["Name"] = "Rakesh";

                DataRow row3 = dtRecords.NewRow();
                row3["ID"] = 3;
                row3["Name"] = "Pankaj";

                DataRow row4 = dtRecords.NewRow();
                row4["ID"] = 4;
                row4["Name"] = "Sandeep";

                DataRow row5 = dtRecords.NewRow();
                row5["ID"] = 5;
                row5["Name"] = "Rajendra";

                //Find() Method

                //Finding data in DataTable based on primary key using Find() method
                DataRow FindResult = dtRecords.Rows.Find("3");

                if (FindResult != null)
                   //Showing result to grid
                    DataTable dtTemp = new DataTable();
                    dtTemp = dtRecords.Clone();
                    dataGridFind.DataSource = dtTemp;

               //Select () Method

               //Selecting data from DataTable based on search criteria using Select() method
                DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

                if (SelectResult != null)
                   //Showing result to grid
                    DataTable dtTemp = SelectResult.CopyToDataTable();
                    dataGridSelect.DataSource = dtTemp;
            catch (Exception)
                //Handle exception here

Note -

Before using Find () of DataTable it is must that we need to Primary key for DataTable object. Because Find () method works based on primary key value only.

In above code, I have created on DataTable object (dtRecords) using DataTable class, and I have filled this DataTable object with some static records. For this I have used DataColumn and DataRow classes.
I have set Primary key column for DataTable object, so that we can use Find () method over DataTable.

//Setting primary key
 dtRecords.PrimaryKey = dataColsID;

Here  dataColsID is array of DataColumn.
After setting primary key to DataTable, I have Find () method like this-

//Finding data in DataTable based on primary key using Find () method
 DataRow FindResult = dtRecords.Rows.Find("3");

Here Find method will return result as DataRow object, for fetching data from DataTable object using Find () method we need to pass primary key value because based on that it will return result.

For getting data using Select () method, I have written code like this-

//Selecting data from DataTable based on search criteria using Select () method
DataRow[] SelectResult = dtRecords.Select("name='Rajendra'");

Select () method works based on search criteria, Here ("name='Rajendra'“) is search criteria for Select () method, it returns array of DataRow object.

Finally to show result I have used two separate DataGridVIew controls and output will be like this-

Result of Find() and Select() method

As we can see in image, For Find () method, one record in coming because in DataTable object we have only one record with ID=3.
And for Select () Method, 2 records are coming because we have 2 records in DataTable obejct with name = "Rajendra".

To perform search operation on DataTable Find() and Select() methods can be used but both methods have different criteria. 

Note - 

1. If we have primary key in table then Find() and Select() both methods can be used
2. If we don't have primary key then we can use only Select() method.



  1. Previously I have doubt about Find() method and Search() methods of DataTable, By reading your post I got clear idea about these methods.

  2. If you have any other doubt then let me know.


  3. Which one is faster ?

    1. Hi,

      You can use both methods based on our need.
      If you want to search based on primary key then go for Find() else go for select() method.

      Searching with primary key is always faster, so Find() method is faster.

  4. Thanks for this code
    but finally you copied result row to a table, but i need the single row value to text box

    so can i write textbox1.text=selectresult(0)
    or how to return a col value???