How to exploit a DataTable using Linq.

By David E.

Sometimes DataTable objects are used as data sources for some of the controls in your application, either desktop or web application. At times the DataTable objects contain more items than needed, and you need to apply a filter for those records. An easy way to apply this filter can be by the AsEnumerable of the datatable object. This method is available from. net framework 3.5.

Below is a simple example:

//Get the dataset from Database

DataSet ds = GetDataSetFromDataLayer();

//Verify the DS

if (ds != null && ds.Tables.Count > 0)

{

//Get the required table

DataTable table = ds.Tables["TableName"];

//Get rows as enumerable

EnumerableRowCollection<DataRow> collection = table.AsEnumerable();

//Filter Rows on collection

var filteredRows = (from s in collection where s.Field<string>("StringTypedColumn").Equals("StringCondition") select s);

//Recover the resulting datatable

DataTable finalTable = filteredRows.CopyToDataTable();

}

Let’s see the code above step by step:

1. The first step is to get the dataset and fill it with the necessary information. Generally, this object is populated by calling a method that is in the data layer of our solution.
DataSet ds = GetDataSetFromDataLayer ();

2. Then, after validating if the dataset has information within it, we need to extract the table to convert IEnumerable and apply the method that performs the conversion.

//Get the required table

DataTable table = ds.Tables["TableName"];

//Get rows as enumerable

EnumerableRowCollection<DataRow> collection = table.AsEnumerable();

3. Once transformed to IEnumerable, we have available all the features of LINQ to make operations, including "Aggregates" functions which would let us make operations group, sums, averages, among others. In the present example, it is performed a simple filter condition. It should be noted that when referring to each field in the condition using the Field extension method is necessary to use data type C # in the column.

Escuchar

Leer fonéticamente

//Filter Rows on collection

var filteredRows = (from s in collection where s.Field<string>("StringTypedColumn").Equals("StringCondition") select s);

4. Finally, once we have our collection of records filtered, we can transform back into a datatable and return it as a data source for a control or to perform more operations.

//Recover the resulting datatable

DataTable finalTable = filteredRows.CopyToDataTable();