Use of Linq to Dataset with Example

This blog containing text about Linq to Dataset object :

The Dataset is a standerd object used in ado.net to work with disconnected data from a variety of data sources and optionally update data  source at a later time with changes made working  in disconnected mode. Linq to dataset lets you query dataset objects using linq queries.Linq to Dataset also lets you easily and flexible solutions to support tasks such as generic reporting and analysis. A linq to dataset query is shown below in following example :-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data;

 

namespace LinqToDataset

{

    class Program

    {

        static void Main(string[] args)

        {

            string connectString = "Data Source=RAVI;" +

                "Integrated security=true;Initial Catalog=Super_old;";

 

            string sqlSelect = "SELECT * FROM mstore;" +

                "SELECT * FROM msalesman;";

 

            // Create the data adapter to retrieve data from the database

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString);

            // Create table mappings

            da.TableMappings.Add("Table", "mStore");

            da.TableMappings.Add("Table1", "mSalesMan");

            // Create and fill the DataSet

            DataSet ds = new DataSet();

            da.Fill(ds);

 

            DataRelation dr = ds.Relations.Add("StoreId_key",

                         ds.Tables["mStore"].Columns["StoreId"],

                         ds.Tables["mSalesMan"].Columns["StoreId"]);

 

            DataTable Store = ds.Tables["mStore"];

            DataTable SaleMan = ds.Tables["mSalesMan"];

 

            var query = from p in Store.AsEnumerable()

                        join i in SaleMan.AsEnumerable()

                            on p.Field<int>("StoreId") equals

                                i.Field<int>("StoreId")

                        where p.Field<int>("StoreId") == 2

                        select new

                        {

                            StoreId = p.Field<int>("StoreId"),

                            Name = p.Field<string>("Name"),

                            SalesManName = i.Field<string>("Name")

 

                        };

 

            foreach (var q in query)

            {

                Console.WriteLine("StoreId = {0} , StoreName = {1} , SalesManName = {2}",

                    q.StoreId, q.Name, q.SalesManName);

            }

 

            Console.WriteLine("\nPress any key to continue.");

            Console.ReadKey();

        }

    }

}

 

The First step in a Linq Query is to obtain a data source. With Linq to dataset ,Name clears that we need to fill dataset  object from the data source. You do this using dataadapter object.

In the second step Dataset is filled with two datatable mStore and mSalesMan from the super_OLD.  In next step I created relationship between both tables named StoreId_key.

Now, In third step I created a linq query against both related table.In Linq, this done using join clause to specify the elements being related. The select clause returns three fields StoreId, StoreName SalesMan Name in the result set.

The final step uses the foreach loop to show the result set…
 

Ebook Download
View all
Learn
View all