11.02.2011

Quick LINQ Tutorial

We can query database tables, XML, Arrays, Lists and other objects. Usually there were different methods to query each of these data sources. As a developer we had to learn all these different techniques.

To make things easier and in standard form, LINQ was invented. LINQ is short for Language-Integrated Query. The reason I mention XML and other data sources in the first paragraph is that many beginner developers mistake LINQ as something for Database only. Please keep in mind that you can use LINQ for almost anything.

Let’s look at different examples of LINQ.


Query an array using LINQ


C#


        // Data source.
        Int32[] numbers = {0, 1, 2, 3, 4, 5, 6};

        // Query creation.
        var MyQuery = from num in numbers  where num > 1 select num;

        // Query execution.
        foreach (Int32 number in MyQuery)
        {
                Response.Write(number + " ");
        }



VB

        Dim numbers As Integer() = {0, 1, 2, 3, 4, 5, 6}
        Dim MyQuery = From num In numbers Where num > 1 Select num

        For Each number In MyQuery
            Response.Write(number.ToString() + " ")
        Next


Output: 2 3 4 5 6

Query XML using LINQ


C#

    public struct MyXMLRecords 
    {
        public string pageURL;
        public string paegText;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        string XMLString = "" + 
            "" +
                "" +
                   "Home" +
                    "http://www.example.com" +
                "" +
            "";

        XDocument parsedXML  = XDocument.Parse(XMLString);
        var MyItems = (from item in
                           (from temp in parsedXML.Descendants("Menu") select new MyXMLRecords() { pageURL = temp.Element("pageURL").Value, 
            paegText = temp.Element("text").Value }) select item);

        foreach (MyXMLRecords recordsItems in MyItems)
        {
            Response.Write("The page text is: " + recordsItems.paegText + " and the url is: " + recordsItems.pageURL);
        }
    }



VB

    Public Structure MyXMLRecords
        Public pageURL As String
        Public paegText As String
    End Structure

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim XMLString As String = "" + _
            "" + _
                "" + _
                   "Home" + _
                    "http://www.example.com" + _
                "" + _
            ""

        Dim parsedXML As XDocument = XDocument.Parse(XMLString)
        Dim MyItems = (From item In (From temp In parsedXML.Descendants("Menu") Select New MyXMLRecords() With {.pageURL = temp.Element("pageurl").Value, _
                                                                                                              .paegText = temp.Element("text").Value}) Select item)

        For Each recordsItems In MyItems
            Response.Write("The page text is: " + recordsItems.paegText + " and the url is: " + recordsItems.pageURL)
        Next



Output:
The page text is: Home and the url is: http://www.example.com

Query MS SQL Table using LINQ



Lets suppose we have the following table:




You can Insert, Update, Delete SQL data using LINQ. Let’s start with the following steps:

1. Create new DBML file


2. Drag the SQL table to DBML


3. The DBML is your DataContext


Select Statement

C#

        MyExampleDataContext DBHandle = new MyExampleDataContext();

        var myRecords = from p in DBHandle.MyTables where p.State == "OH" select p;

        foreach (MyTable recordsItems in myRecords)
        {
            Response.Write("The Name is: " + recordsItems.Name + " and the ID is: " + recordsItems.ID.ToString());
        }


VB

        Dim DBHandle As MyExampleDataContext = New MyExampleDataContext()

        Dim myRecords = From p In DBHandle.MyTables Where p.State = "OH" Select p

        For Each recordsItems In myRecords
            Response.Write("The Name is: " + recordsItems.Name + " and the ID is: " + recordsItems.ID.ToString())
        Next


Output:
The Name is: Raza and the ID is: 1
The Name is: Joe and the ID is: 2
The Name is: John and the ID is: 3


Delete Statement:

C#


        MyExampleDataContext DBHandle = new MyExampleDataContext();

        var myRecords = from p in DBHandle.MyTables where p.State == "OH" select p;

 //Basically write a query above which will fetch the record(s) that you wish to delete and call the following lines.
        DBHandle.MyTables.DeleteAllOnSubmit(myRecords);
        DBHandle.SubmitChanges();


VB

        Dim DBHandle As MyExampleDataContext = New MyExampleDataContext()

        Dim myRecords = From p In DBHandle.MyTables Where p.State = "OH" Select p

        DBHandle.MyTables.DeleteAllOnSubmit(myRecords)
        DBHandle.SubmitChanges()



Update Statement:

C#

        MyExampleDataContext DBHandle = new MyExampleDataContext();

        var myRecords = (from p in DBHandle.MyTables where p.ID == 1 select p).Single();

        myRecords.Name = "My Updated Name";
        myRecords.State = "NY";

        DBHandle.SubmitChanges();


VB

        Dim DBHandle As MyExampleDataContext = New MyExampleDataContext()
        Dim myRecords = (From p In DBHandle.MyTables Where p.State = "OH" Select p).Single()
        myRecords.Name = "My Updated Name"
        myRecords.State = "NY"
        DBHandle.SubmitChanges()


Insert Statements:

C#

        MyExampleDataContext DBHandle = new MyExampleDataContext();

        MyTable newInsert = new MyTable
        {
            Name = "John Doe", State="DE"
        };

        DBHandle.MyTables.InsertOnSubmit(newInsert);
        DBHandle.SubmitChanges();


VB

        Dim DBHandle As MyExampleDataContext = New MyExampleDataContext()
        Dim newInsert As New MyTable With {.Name = "John Doe", .State = "DE"}
        DBHandle.MyTables.InsertOnSubmit(newInsert)
        DBHandle.SubmitChanges()


Limiting results on the server side

This similar to limits in MySQL.

C#


        MyExampleDataContext DBHandle = new MyExampleDataContext();

      
        var myRecords = (from p in DBHandle.MyTables select p).Skip(2).Take(3);
        
        foreach (MyTable recordsItems in myRecords)
        {
            Response.Write("The Name is: " + recordsItems.Name + " and the ID is: " + recordsItems.ID.ToString());
        }


VB


        Dim DBHandle As MyExampleDataContext = New MyExampleDataContext()
        Dim myRecords = (From p In DBHandle.MyTables Select p).Skip(2).Take(3)

        For Each recordsItems In myRecords
            Response.Write("The Name is: " + recordsItems.Name + " and the ID is: " + recordsItems.ID.ToString())
        Next



Output

The Name is: Albert and the ID is: 4
The Name is: Martha and the ID is: 5
The Name is: Kim and the ID is: 7

0 comments:

Post a Comment