Saturday 20 October 2012

DataTable Filter using LINQ | DataTable SORT using LINQ

DataTable Filter using LINQ 

We shall see, how to implement a Client side DataTable Filter using LINQ, without hitting the database. 
First we will define the GridView control in the .aspx page, which will display the results of the filtered DataTable here is the code


<asp:GridView
ID="grdUsers"
runat="server"
AutoGenerateColumns="true">
   
<HeaderStyle
BackColor="Navy"
ForeColor="White"
Font-Bold="true" />
   
<RowStyle
BackColor="White"
ForeColor="Black" />
   
<AlternatingRowStyle
BackColor="Wheat"
ForeColor="Black" />
</asp:GridView>

Once we are done with the declaration of the GridView tag, we can switch to the code behind file (.cs/.vb file) to implement the DataBinding and Filter logic for the DataTable. Here is the code for the DataTable filter

string strSQL = "SELECT * FROM USERS";
DataSet dsUsers = new DataSet();
dsUsers = DataAccessLayer.GetDataSet(strSQL,"dtUSers");
//
// Implement your own DataAccess logic here to get data from the DB.
//
// Filter the DataTable using a LINQ Expression
DataTable dtFilterTable = dsUsers.Tables["dtUsers"].AsEnumerable()
            .Where(row => row.Field<String>("USER_NAME").Contains(txtFilter.Text))
            .CopyToDataTable();
//
grdUsers.DataSource = dtFilterTable;
grdUsers.DataBind();
Here the DataTable is filtered on the USER_NAME column based on the filter text entered in the txtFilter TextBox.
That’s it we have filtered the contents of DataTable control using LINQ filters.

 

 

DataTable SORT using LINQ

We shall see, how to implement a Client side DataTable SORT, using LINQ, without hitting the database. 
First we will define the GridView control in the .aspx page, which will display the results of the filtered DataTable here is the code


<asp:GridView
ID="grdUsers"
runat="server"
AutoGenerateColumns="true">
   
<HeaderStyle
BackColor="Navy"
ForeColor="White"
Font-Bold="true" />
   
<RowStyle
BackColor="White"
ForeColor="Black" />
   
<AlternatingRowStyle
BackColor="Wheat"
ForeColor="Black" />
</asp:GridView>

Once we are done with the declaration of the GridView tag, we can switch to the code behind file (.cs/.vb file) to implement the DataBinding and SORT logic for the DataTable. Here is the code for the DataTable filter

string strSQL = "SELECT * FROM USERS";
DataSet dsUsers = new DataSet();
dsUsers = DataAccessLayer.GetDataSet(strSQL,"dtUSers");
//
// Implement your own DataAccess logic here to get data from the DB.
//
// SORT the DataTable using a LINQ Expression
DataTable dtSortedTable = dsUsers.Tables["dtUsers"].AsEnumerable()
.OrderBy(row => row.Field<string>("USER_NAME"))            
.CopyToDataTable();
//
grdUsers.DataSource = dtSortedTable;
grdUsers.DataBind();
Here the DataTable is ordered by the column USER_NAME
To sort the column in the Descending order we can use the Expression

.OrderByDescending(row => row.Field<string>("USER_NAME"))
That’s it we have sorted the column of DataTable control using LINQ expression.


No comments :