Home > Linq > LINQ # 6 – LINQ To Dataset (Part I) (Querying the dataset using LINQ )

LINQ # 6 – LINQ To Dataset (Part I) (Querying the dataset using LINQ )

Hi friends,

Today we will see the very interesting feature of LINQ to Query the Dataset.

As we know ADO.Net is now the heart of the developers to manipulate the database

it is important to learn how to fire query against DATASET using LINQ.

* DataSet is a collection of DataTable objects, which contains rows and columns similar to  the database tables. DataSet also holds primary key and foreign keys. DataSets can be typed or un-typed

* Typed DataSets derive the schema for table and column structure, and are easier to program. Even though a DataSet has lots of capabilities, they are fairly limited. It provides methods for selecting, sorting and filtering data, and provides methods like GetChildRows and GetParentRows for navigation

* But even for complex conditions developer needs to write lots of T – Sql Queries to get the  desired result which adds an additional maintainance.

                     In such a complex conditions LINQ To Dataset plays a vital role which helps the developers to bring down the level of complexity.

* Before we use the LINQ to DataSet queries against DataSet, it should be populated with data. This can be done using DataAdapter class or other features supported by LINQ to SQL.

 

* To see how to populate the data on dataset please refer Populate data into the DataSet.

* Now we considet we have a dataset populated with the data as follows

image

You can see DataTables listed in the DataSet Visualizer. On selecting DataTable, DataRows are listed in the grid that is shown in the visualizer. This is another way of verifying DataSet content.

* Querying the dataset using LINQ

Note :- To be a part of LINQ queries, DataTables should be enumerable and the source for the LINQ query should be IEnumerable<T>. Querying can be done on enumeration of DataRow objects so that we will have all DataColumns available for the query expressions.

# Example 1 “Query a single DataTable in DataSet”

As we see in above dataset we have a table “Categories” .

* We will make the table as enumerable first to be eligible for querying as follows

var categories = dataSetDeserts.Tables[0].AsEnumerable();
var items = dataSetDeserts.Tables[1].AsEnumerable();

var rowCategories = from p in categories
where p.Field<int>("CategoryID") == 1
select p;
foreach (var cat in rowCategories)
{
Console.WriteLine(cat[0] + " " + cat[1] + " " + cat[2]);
}
 

# Example 2 “Query multiple DataTable in DataSet”

 Following is the example in which we want to join the two tables “Categories and Items”

on the filed “CategoryID” to select the items for the particular category.

var rowItemCategories = from cats in categories
join item in items
on cats.Field<int>("CategoryID") equals
item.Field<int>("CategoryID")
where cats.Field<int>("CategoryID") == 1
select new
{
itemID = item.Field<int>("IItemID"),
category = cats.Field<string>("CategoryName"),
itmName = item.Field<string>("Name")
};
foreach (var itmcat in rowItemCategories)
{
Console.WriteLine("ItemID:" + itmcat.itemID + " Category:" +
itmcat.category + " Name:" + itmcat.itmName);
}
 
* The join operator used in the previous query,to fetch details from two different tables
by relating a column in each table, can be avoided by introducing a relation 
between the tables in DataSet itself. This is shown in the following code:
// Data Relation
DataRelation CatItem = new DataRelation("CategoryItems", dataSetDeserts.Tables[0].Columns["CategoryID"], dataSetDeserts.Tables[1].Columns["CategoryID"], true);
dataSetDeserts.Relations.Add(CatItem);
//Now try to fetch the records as below
foreach (var cat in rowCategories)
{
foreach (var item in cat.GetChildRows("CategoryItems"))
{
Console.WriteLine("ItemID:" + item["IItemID"] + " Category:" +
cat["CategoryName"] +" Name:" + item["Name"]);
}
}
 

The rowCategories is the same query used in the earlier single table query methods. The first foreach loop is to loop through each category in the Categories table. The second loop refers to the Detail table which is related to the main table used in the query and fetches the Detail table records also. This can be obtained by executing the GetChildrows method on the main query

In next blog we will cover up the Dataset Query Operatots

Thanks,

Advertisements
Categories: Linq
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: