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.

Related Post

What is LINQ?
DataTable Filter using LINQ
Binding a C# List to a GridView control

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.

Related Post
What is LINQ?
Binding a C# List to a GridView control

What is LINQ?

LINQ is an acronym of Language Integrated Query.

As the name suggests LINQ is a query language which can be integrated with .Net code, LINQ is Microsoft’s approach to combine .Net code and the query language in a single place.

In the conventional approach we will defined the User interface elements in the Windows or Web interface, the data will be stored in the database and we use queries or stored procedures to fetch the data into the User interface, this required the developer to be aware of both the User Interface programming and the database query programming.

With LINQ the queries can be integrated with the .Net code, hence the developer need not be aware of SQL queries; he can use LINQ queries instead and fetch data from the data sources to use it in the UI.

LINQ offers powerful data binding, which enables us to link to multiple types of data sources, at present LINQ supports the following data sources.

          

  LINQ to Object  - Collection of in-memory (Array, List etc)
  LINQ to SQL       - Relational databases SQL Server
  LINQ to DataSet - ADO.Net DataTable and DataSet objects
  LINQ to XML      - XML files and in-memory XML strings
  LINQ to Entities   - Querying Entity Data Model entities 

The advantage of using LINQ is that the LINQ queries remain same irrespective of whatever data source is used, in conventional methods each of these data sources will have to be queried using its own way, LINQ standardizes this and makes it easier for the developer to query multiple data sources using the same standard LINQ.