Using DataTable (DataView) Filter expressions, Sorting

In some cases it is necessary to be made some calculations or data filtering and sorting of local data - already received from a database. These things can be made with the DataTable or DataView classes in a way similar to SQL Select queries.
When we have a filled DataTable we can easy retrieve only the rows we need with DataView.RowFilter. This property gets or sets the expression used to filter which rows are viewed in the DataView (often used for DataSourse in data components).

DataTable dt= ......... //filling the data table
DataView dv= dt.DefaultView;
dv.RowFilter="AreaId="+searchId+" AND SubAreaName LIKE 's*'; //getting all records for searched area whic name starts with 's'
dv.Sort="Name desc, number asc"; //sorting data

dv.RowFilter = "Name LIKE 's*'" // values that start with "s"'
dv.RowFilter = "Name LIKE '%s%'" // values that contain 's'
dv.RowFilter = "Name NOT LIKE 's*'" // values that don't start with 's'
dv.RowFilter = "Name <> 'MyName'" // values different than 'MyName'
dv.RowFilter = "Name IN ('MyName','MySecondName')" // value equal to 'MyName' or 'MySecondName'


If the pattern string in a LIKE statement contains special characters like * % [ ], they must be escaped in brackets [ ] in this way [*], [%], [[] or []].

RowFilter is an expression string that can be evaluated to true or false, and uses the syntax described in the DataColumn.Expression documentation.

It is very useful to use also aggregate functions for calculation. For example

dt.Columns("ItemCount").Expression = "Count(ItemID)"

dt.Columns("ItemCount").Expression = "FirstName = 'John'"   

dt.Columns("ItemCount").Expression = "Date < #1/31/82#" 

dt.Columns("ItemCount").Expression = (LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John' 

Here is the MSDN reference for the filter expressions:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx