003 LINQ to DataSet

DataSet is standard Ado.Net object of .Net Framework. It is disconnected in nature. It may contain one or more datatable  filled by  fillDataset method. DataSet can be filled form DataAdaptor or  datatable can be dynamically populated and added to the DataSet. LINQ offer query on DataSet also. First we need to include System.LINQ namespace in our project. During query on DataSet , table are  consider  as Enumerable (AsEnumerable()). You can  query simple select statement or complex joining statement or update datatable value.

You should remember , you can query on datatable only as it is enumerable.You cannot query on data row collection.

Below the first example is of a simple query  on a datatable.

Below the second the example of  a with joining statement of two datatable.

Below the third example is of update statement.


Example 

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Data;

using System.Linq;

namespace ConsoleApplication1

{

           class Program

            {

              static void Main(string[] args)

              {

                    DataSet ds = new DataSet();

                    DataTable dt1 = new DataTable();

                    dt1.Columns.Add("Name", typeof(string));

                    dt1.Columns.Add("RollNumber", typeof(string));

                    dt1.Columns.Add("RegistrationNumber", typeof(int));

                    dt1.Rows.Add("John1", "12", 122);

                    dt1.Rows.Add("John2", "13", 123);

                    dt1.Rows.Add("John3", "14", 124);

                    dt1.Rows.Add("John4", "15", 125);

                    dt1.Rows.Add("John5", "16", 126);

                    dt1.Rows.Add("John6", "17", 127);

                    dt1.Rows.Add("John7", "18", 128);

                    dt1.Rows.Add("John8", "19", 129);

                    dt1.Rows.Add("John9", "20", 130);

                   ds.Merge(dt1);

                   DataTable dt2 = new DataTable();

                   dt2.Columns.Add("RegistrationNumber", typeof(int));

                   dt2.Columns.Add("Fees", typeof(Decimal));

                   dt2.Columns.Add("Marks", typeof(int));

                   dt2.Rows.Add(122, 215.57, 85);

                   dt2.Rows.Add(123, 315.57, 86);

                   dt2.Rows.Add(124, 250.25, 87);

                   dt2.Rows.Add(125, 275.50, 98);

                   dt2.Rows.Add(126, 295.85, 99);

                   dt2.Rows.Add(127, 260.40, 84);

                   dt2.Rows.Add(128, 250.45, 83);

                   dt2.Rows.Add(129, 275.65, 82);

                   dt2.Rows.Add(130, 285.57, 96);

                    ds.Merge(dt2);

/*****************Example of a simple query ************/

Console.WriteLine("************************ Select Records 'John2'*******");

var res = from exp in ds.Tables[0].AsEnumerable()

where exp.Field<string>("Name") == "John2"

                    select new

                    {

                              MyName = exp.Field<string>("Name"),

                              MyRoll = exp.Field<string>("RollNumber"),

                    };

                    foreach (var q in res)

                   {

                         Console.WriteLine("Name = {0} , RollNumber = {1} ",

                        q.MyName, q.MyRoll);

                     }

                       Console.ReadKey();

/*****************Example with joining statement of two datatables ************/

Console.WriteLine("************************ List of Student , whose marks above 85 *******");

                     var query = from p in ds.Tables[0].AsEnumerable()

                                                join i in ds.Tables[1].AsEnumerable()

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

                                                 i.Field<int>("RegistrationNumber")

                                                 where i.Field<int>("Marks") > 85

                                                 select new

                                                  {

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

                                                           MyRoll = p.Field<string>("RollNumber"),

                                                           MyFees = i.Field<decimal>("Fees"),

                                                    };

                                            foreach (var obj in query)

                                           {

                                                   Console.WriteLine("Name = {0} , RollNumber = {1}, Fees = {2}",

                                                   obj.MyName, obj.MyRoll, obj.MyRoll);

                                            }

                                            Console.ReadKey();

/*****************Example of update statement ************/

Console.WriteLine("************************ Example of update statement *******");

ds.Tables[0].AsEnumerable().Where(row => row.Field<string>("Name") == "John1")

.Select(b => b["RollNumber"] = "999")

.ToList();

var alldata = from exp in ds.Tables[0].AsEnumerable()

                           select new

                           {

                                   MyName = exp.Field<string>("Name"),

                                   MyRoll = exp.Field<string>("RollNumber"),

                             };

                            foreach (var q in alldata)

                            {

                                         Console.WriteLine("Name = {0} , RollNumber = {1} ",

                                          q.MyName, q.MyRoll);

                             }

                    Console.ReadKey();

                    }

      }

}

Output :

************************ Select Records 'John2'*******
Name = John2 , RollNumber = 13
************************ List of Student , whose marks above 85 *******
Name = John2 , RollNumber = 13, Fees = 13
Name = John3 , RollNumber = 14, Fees = 14
Name = John4 , RollNumber = 15, Fees = 15
Name = John5 , RollNumber = 16, Fees = 16
Name = John9 , RollNumber = 20, Fees = 20
************************ Example of update statement *******
Name = John1 , RollNumber = 999
Name = John2 , RollNumber = 13
Name = John3 , RollNumber = 14
Name = John4 , RollNumber = 15
Name = John5 , RollNumber = 16
Name = John6 , RollNumber = 17
Name = John7 , RollNumber = 18
Name = John8 , RollNumber = 19
Name = John9 , RollNumber = 20

002 LINQ Environment

          LINQ can be written in all language of Visual Studio .But syntax are different for languages different.LINQ is available from Visual Studio 2010 and (.Net Framework 3.5) and upper version. Now , we will learn how to create a setup LINQ environment. 

           To do this , you have to install Visual Studio first. There are two options, download Visual Studio Express  or purchase license version of Visual Studio for professional uses. In the example we have downloaded Visual Studio 2015 . First  we need to click on setup, the setup will  start. 

Installation has two option "default" or "customs" , you need to choose default options, Visual Studio will proceed, progress bar will show the progress of installation. 

After that you will see a message that  the installation is completed. If you have downloaded Express version, you need to sign up Microsoft to smooth  performance of Visual Studio.


To  create  a console application 

click  1)File   --> New Project 
       2)Choose --> Console Application

you will get this editor. Please keep in mind, LINQ can be used  for desktop application, web application , mobile application also. For the purpose of training we have chosen console application.




Below is the example of LINQ. There are three example. The first example is simple LINQ query  over an array. The second example of utility of IEnumerable interface.  IEnumerableis heart of LINQ queries, you will learn details it in the letter chapter.The third  example is how complex  object is handle by LINQ queries.

Example

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Linq;

using System.Collections;

namespace ConsoleApplication1

{

      class Program

     {

        static void Main(string[] args)

          {

                         string[] country = { "India", "Brazil", "China", "USA", "Pakistan", "South Afrika", "Japan", "Russia", "Poland", "Sweden" };

 

             /* Example 1 (Simple Select Statement) */

                      var AllCountry = from c in country select c;

                     Console.WriteLine("List of Country");

                     Console.WriteLine("----------------");

 
            foreach (var c in AllCountry)

                    {

                              Console.WriteLine(c);

                      }

                       Console.ReadLine();

                      /* Example 1 (All output as reverse list) */

                     var uppercseCountry = country.Reverse().ToArray();

                    Console.WriteLine("Revese List of Country");

                    Console.WriteLine("----------------");

                     foreach (var c in uppercseCountry)

                     {

                               Console.WriteLine(c);

                      }

                     Console.ReadLine();

                     /* Example 3 (LINQ on Array List) */

                      ArrayList myList = new ArrayList();

                     myList.Add(

                     new student

                      {

                          name = "John",

                          roll = 12,

                           age = 9

                         });

                        myList.Add(

                         new student

                       {

                            name = "John1",

                            roll = 14,

                             age = 8

                        });

                        myList.Add(

                        new student

                        {

                          name = "John2",

                          roll = 13,

                          age = 7

                           });

                         myList.Add(

                         new student

                         {

                                name = "John3",

                                roll = 15,

                                 age = 8

                          });

                        var query = from student s in myList

                                          where s.age > 7

                                          select s;

                       Console.WriteLine("List of Student Over 7 Years of Age");

                        Console.WriteLine("--------------------------------------");

                         foreach (student c in query)

                        {

                                      Console.WriteLine("Name : "+c.name);

                                       Console.WriteLine("Roll : " + c.roll);

                                       Console.WriteLine("Age : " + c.age);

                                       Console.WriteLine("\n");
             }

                        Console.ReadLine();

                        }

                   }

}

public class student

{

                 public string name { get; set; }

                 public int roll { get; set; }

                  public int age { get; set; }

}



After you have written the code, press F5  run the code. You will get the output one by one  as you press the key.


Output :
 

List of Country

----------------

India

Brazil

China

USA

Pakistan

South Afrika

Japan

Russia

Poland

Sweden

Revese List of Country

----------------

Sweden

Poland

Russia

Japan

South Afrika

Pakistan

USA

China

Brazil

India

List of Student Over 7 Years of Age

--------------------------------------

Name : John

Roll : 12

Age : 9

Name : John1

Roll : 14

Age : 8

Name : John3

Roll : 15

Age : 8