Wednesday, 4 July 2018

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

005 LINQ to XML

XML is well known format to hold data. It is widely accepted all over the world. Formatting XML data depend upon the node ,attribute, elements ect . You can easily access , modify node , attribute , elements through programming languages. LINQ to XML is it functionality of.Net framework to query and modify the XML in memory.  LINQ to XML increase the productivity, developer need to write less code and it is compact. To  implement LINQ to XML, you need to load XML in memory first, then only you can modify or query in the XML document. The advantage of these approaches is, the process is integrated with languages and very lightweight, during the compilation of program , checking is done over the query and modification. After the query or notification is over , now you can save the XML document to physical file and you can do the necessary work with memory  XML file.  System.Xml.Linq  is the namespace  is used for XML manipulation.

Below Example Show 
1)A Simple select query ,return list of student.
2)A Simple select query with where condition,return list of student whose marks greater than 15
3)A Simple select query with where condition,return list of student whose Roll is 14.



Example

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)
             {

               StringBuilder sb = new StringBuilder();
               sb.Append("");
               sb.Append("");
               sb.Append("John1");
               sb.Append("12");
               sb.Append("12.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John2");
               sb.Append("13");
               sb.Append("13.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John3");
               sb.Append("14");
               sb.Append("14.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John4");
               sb.Append("14");
               sb.Append("14.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John5");
               sb.Append("15");
               sb.Append("15.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John6");
               sb.Append("16");
               sb.Append("16.50");
               sb.Append("");

               sb.Append("");
               sb.Append("John7");
               sb.Append("17");
               sb.Append("17.50");
               sb.Append("");
               sb.Append("");

       TextReader tr = new StringReader(sb.ToString());
       XDocument xdoc = XDocument.Load(tr);

       /******************LINQ to XML --Simple Select Query*******************/

       Console.WriteLine("*********************List of Student*******************");
       var lv1s = from lv1 in xdoc.Descendants("student")
                  select new
                  {

                  Name = lv1.Element("Name").Value,
                  Roll = lv1.Element("Roll").Value,
                  Marks = lv1.Element("Marks").Value
                 };

              foreach (var lv1 in lv1s)
              {

                 Console.WriteLine("Name = {0} , RollNumber = {1} ,Marks = {1} ",
                 lv1.Name, lv1.Roll, lv1.Marks);

              }

              Console.ReadKey();

        Console.WriteLine("*********************List of Student, Marks greated than 15***********");

            var lv1a = from lva in xdoc.Descendants("student")
                      where Convert.ToDecimal(lva.Element("Marks").Value) >= 15
                      select new
                      {

                         Name = lva.Element("Name").Value,
                         Roll = lva.Element("Roll").Value,
                         Marks = lva.Element("Marks").Value
                     };


             foreach (var lv1 in lv1a)
             {

                Console.WriteLine("Name = {0} , RollNumber = {1} ,Marks = {1} ",
                 lv1.Name, lv1.Roll, lv1.Marks);

             }
             Console.ReadKey();


Console.WriteLine("*********************List of Student, Filter by Roll=14***********");

          IEnumerable<XElement> stu = from el in xdoc.Elements("student")

          where (string)el.Element("Roll") == "14"

          select el;


           foreach (var stuElm in stu)
          {

               Console.WriteLine("Name = {0}", stuElm.Name);

          }
             Console.ReadKey();

         }

     }

}

Output :
*********************List of Student*******************
Name = John1 , RollNumber = 12 ,Marks = 12
Name = John2 , RollNumber = 13 ,Marks = 13
Name = John3 , RollNumber = 14 ,Marks = 14
Name = John4 , RollNumber = 14 ,Marks = 14
Name = John5 , RollNumber = 15 ,Marks = 15
Name = John6 , RollNumber = 16 ,Marks = 16
Name = John7 , RollNumber = 17 ,Marks = 17
*********************List of Student, Marks greated than 15***********
Name = John5 , RollNumber = 15 ,Marks = 15
Name = John6 , RollNumber = 16 ,Marks = 16
Name = John7 , RollNumber = 17 ,Marks = 17
*********************List of Student, Filter by Roll=14***********

বাঙালির বেড়ানো সেরা চারটি ঠিকানা

  বাঙালি মানে ঘোড়া পাগল | দু একদিন ছুটি পেলো মানে বাঙালি চলল ঘুরতে | সে সমুদ্রই হোক , পাহাড়ি হোক বা নদী হোক। বাঙালির ...