Tuesday, April 3, 2012

ADO.Net - DataTable.Select() Vs DataTable.Rows.Find Vs DataView.

Asp.Net provides many options to FILTER and SORT records in the client side, the available options are as follows.


Each of these options have their own advantages and disadvantages, we will have to use the right options at the right place to maximize performance.

Let us not see the Best Practices for using these options.

The DataTable.Rows.Find options is best suited wile filtering specific rows, based on the PrimaryKey of the Table. This option gives the best performance for filtering limited rows with straight forward filter Option on the PrimaryKey.

ADO.Net builds an index based on the PrimaryKey column, and this option works like an Index Seek to reach out the exact row using the Index, this is the fastest option to select rows based on PrimaryKey.

Recommended while filtering a single/very few rows based on the PrimaryKey
Not Recommended for complex filters and filter returning more number of rows

// Set the PrimaryKey of the DataTable
DataColumn[] arrPrimaryKey = new DataColumn[1];
arrPrimaryKey[0] = dsEmpInfo.Tables["dtEmpInfo"].Columns["KEY_ID"];
dsEmpInfo.Tables["dtEmpInfo"].PrimaryKey = arrPrimaryKey;
//Filter rows based on the PrimaryKey
DataRow dr1 = dsEmpInfo.Tables["dtEmpInfo"].Rows.Find("1");

The above experssion will return a single DataRow, whoose PrimaryKey value (KEY_ID) is 1.

The Datable.Select method is a good options when you want to perform only a single or very few filters on a large DataTable, this methods scans through the entire table and retrieves the rows based on the filter condition.

The DataTable.Select method can be used to perform multiple and complex filters on a DataTable

dsEmpInfo.Tables["dtEmpInfo"].Select("KEY_ID = '1'");
The above expression will return the employee details whose ID is 1.

dsEmpInfo.Tables["dtEmpInfo"].Select("Name Like 'A%'");
The above expression will return a list of employees whose name starts with A.

This option works much like a Table Scan, best suited when the filter is expected to return a large number of rows.

Recommended when performing a single (or very few) filter from a DataTable, which is expected to return more number of rows.
Not recommended to filter based on PrimaryKey & for performing multiple filters on the same DataTable

This option is similar to the DataTable.Select method, the differences is that the view first creates and index of the data based on the SORT order, and reuses the view to filter out the data. The Index creating is a one time activity, this will take time, but the subsequent filters will be quick.

when we need to perform multiple filters on the same DataTable.
Not Recommended for just a single filter, as there is an overhead in creating the Index

dvEmpInfo = new DataView(dsEmpInfo.Tables["dtEmpInfo"]);
dvEmpInfo.RowFilter = "KEY_ID = 1";
dvEmpInfo.Sort = "KEY_ID ASC";

That's it, we have compared the Pros and Cons of the various options available for filtering and sorting data from a DataTable in ADO.Net

Search Flipkart Products:


Neetu Maheshwari said...

thank you friend u save my day

LOL Boost said...

many thanks a great deal with regard to discussing. we wanted something similar to which.

LOL Boost

Cheap FUT 14 Coins

Tanika Co Valda said...

Very much useful article. Kindly keep blogging

Java Training in Chennai

Java Online Training India