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;
arrPrimaryKey = 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.
Recommended 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
DataView 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