006 LINQ to SQL

LINQ to SQL is an infrastructure for manipulation of data by LINQ with  SQL.The infrastructure  send the query to the database and database retrun output which is later translate to the LINQ object. .Net Framework introduces a class called "LINQ to SQL Class" ,for this purpose. To execute  queries , you need to fast include  file to the project.LINQ to SQL classes map the data model object  with tables.Here are the steps how implement LINQ to SQL

1)From project -->Add-->new item , select LINQ to SQL class.

 

 2)Include database in your project , click on test connection.

3)You can later implement the connection string of your database to the "LINQ to SQL Class" during instance creations.

4)From left hand Database Explorer tab, drag table on
 "LINQ to SQL Class" , you will notice graphical representation of your table will be created automatically.

1) Below is a simple example of select statement
via LINQ to SQL

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Data;

using System.Linq;

using System.Collections;

using System.Xml;

using System.Text;

using System.Xml.Linq;

using System.IO;

namespace ConsoleApplication1

{

            class Program

           {

                static void Main(string[] args)

               {

LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

           /***************Example of Simple Select Statment****************************/

            Console.WriteLine("***********************List of Student*************************");

           var arr = from obj in db.students

                           select new

                          {

                                obj.Name,

                                obj.Roll,

                                obj.Age

                          };

          foreach (var arrobj in arr)

         {

                 Console.WriteLine("Name Id = {0} , Roll = {1}, Age = {2}",

                 arrobj.Name, arrobj.Roll, arrobj.Age);

        }

            Console.ReadKey();

         }

      }

}

class student

{

string Name;

int Roll;

int Age;

}

Output

***********************List of Student*************************
Name Id = John1 , Roll = 123, Age = 12
Name Id = John2 , Roll = 124, Age = 13
Name Id = John 12345 , Roll = 8888, Age = 55
Name Id = John4 , Roll = 126, Age = 15
Name Id = John5 , Roll = 127, Age = 16
Name Id = John6 , Roll = 128, Age = 17
Name Id = John7 , Roll = 129, Age = 18
Name Id = New Student , Roll = 999, Age = 26
Name Id = New Student , Roll = 999, Age = 26
Name Id = New Student , Roll = 999, Age = 26


2)Below is a simple example of statement with condition

 using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Data;

using System.Linq;

using System.Collections;

using System.Xml;

using System.Text;

using System.Xml.Linq;

using System.IO;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

/***************Example of Simple Select Statment with where condition****************************/

          Console.WriteLine("***********************List of Student with roll number 125*************************");

            var arr1 = from ord1 in db.students

             where ord1.Roll == 125

                    select new

                         {

                            ord1.Name,

                           ord1.Roll,

                           ord1.Age

                        };

                 foreach (var arrobj in arr1)

                  {

                     Console.WriteLine("Name Id = {0} , Roll = {1}, Age = {2}",

                      arrobj.Name, arrobj.Roll, arrobj.Age);

             }

                Console.ReadKey();

            }

         }

}

class student

{

             string Name;

            int Roll;

            int Age;

}

Output

***********************List of Student with roll number 125*************************
Name Id = New Student , Roll = 125, Age = 125

3)Below is a example of update with condition

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Data;

using System.Linq;

using System.Collections;

using System.Xml;

using System.Text;

using System.Xml.Linq;

using System.IO;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

/***************Example of Update Record where condition****************************/

Console.WriteLine("***********************Student List After update*************************");

                 var stu2 = from ord3 in db.students

                                   where ord3.Roll == 125

                                   select ord3;

                          foreach (student ord in stu2)

                          {

                               ord.Name = "John 12345";

                               ord.Roll = 8888;

                               ord.Age = 55;

                               db.SubmitChanges();

                        }

                      Console.WriteLine("Sucessfully Update");

                     var arr4 = from ord4 in db.students

                                         select new

                                        {

                                            ord4.Name,

                                            ord4.Roll,

                                           ord4.Age

                                       };

                       foreach (var arrobj in arr4)

                      {

                               Console.WriteLine("Name Id = {0} , Roll = {1}, Age = {2}",

                                  arrobj.Name, arrobj.Roll, arrobj.Age);

                      }

                         Console.ReadKey();

          }

}

}

class student

{

          string Name;

           int Roll;

          int Age;

}

 Output :

***********************Student List After update*************************
Sucessfully Update
Name Id = John1 , Roll = 123, Age = 12
Name Id = John2 , Roll = 124, Age = 13
Name Id = John 12345 , Roll = 8888, Age = 55
Name Id = John4 , Roll = 126, Age = 15
Name Id = John5 , Roll = 127, Age = 16
Name Id = John6 , Roll = 128, Age = 17
Name Id = John7 , Roll = 129, Age = 18
Name Id = New Student , Roll = 999, Age = 26
Name Id = New Student , Roll = 999, Age = 26
Name Id = John 12345 , Roll = 8888, Age = 55

1) Below is a simple example of select statement
via LINQ to SQL

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Data;

using System.Linq;

using System.Collections;

using System.Xml;

using System.Text;

using System.Xml.Linq;

using System.IO;

namespace ConsoleApplication1

{

            class Program

            {

                 static void Main(string[] args)

                {

                      LINQtoSQLDataContext db = new LINQtoSQLDataContext(@"Data Source=.\SQLEXPRESS; AttachDbFilename=C:\aa\tutorial.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

/***************Example of Deletee Record where condition****************************/

Console.WriteLine("***********************Student List After Delete*************************");

                      student objStu = db.students.Single(student => student.Roll == 125);

                      db.students.DeleteOnSubmit(objStu);

                      db.SubmitChanges();

                      var arr5 = from ord5 in db.students

                                       select new

                                       {

                                               ord5.Name,

                                               ord5.Roll,

                                               ord5.Age

                                       };

              foreach (var arrobj in arr5)

             {

                        Console.WriteLine("Name Id = {0} , Roll = {1}, Age = {2}",

                        arrobj.Name, arrobj.Roll, arrobj.Age);

             }

             Console.ReadKey();

}

}

}

class student

{

string Name;

int Roll;

int Age;

}

***********************Student List After Delete*************************
Name Id = John1 , Roll = 123, Age = 12
Name Id = John2 , Roll = 124, Age = 13
Name Id = John 12345 , Roll = 8888, Age = 55
Name Id = John4 , Roll = 126, Age = 15
Name Id = John5 , Roll = 127, Age = 16
Name Id = John6 , Roll = 128, Age = 22
Name Id = New Student , Roll = 999, Age = 26
Name Id = New Student , Roll = 999, Age = 26
Name Id = John 12345 , Roll = 8888, Age = 55