.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> LINQ >> Post New Resource Bookmark and Share   

 Subscribe to Articles

LINQ to SQL, LINQ to XML and Accessing DataTable using LINQ

Posted By:Kirtan       Posted Date: April 20, 2010    Points: 25    Category: LINQ    URL: http://www.dotnetspark.com  

This article demonstrates how to use LINQ on DataTable, XML Data using LINQ to XML and SQL server data base using LINQ to SQL Classes.
 

Introduction:

In this article I will show you how to access basic data sources like Data Table, SQL server database using LINQ to SQL Classes and XML data using LINQ to XML.

4-17-2010 7-15-19 PM.gif

Technology:

CSharp 3.5/4.0

Implementation:

I will introduce the LINQ first for the Beginners who are working first time with the LINQ.

LINQ is Language integrated Query that is used to query on Data Source Objects like Arrays Collection Datasets etc .For example we can select some data based on some criteria from DataTable, We can select some Elements from array.

Now I think you are familiar with the basic purpose of the LINQ. So let's learn basic syntax of LINQ query.

LINQ query's syntax is somewhat similar to the Structured Query language. So if you are familiar with the SQL syntax it will be very easy to understand for you.

Query starts with 'from' keyword and ends with 'select'.

from in where select

Where element name is name of object which you are retrieving from data Source condition can be anything according to Element Type you are receiving from Data Source.

In sample code below I will show you sample code in which we will see how to access elements from array, Collection and DataTable.

Basic Steps I have done in Sample Application are as below

  1. Created Some Sample Data Sources (Created DataTable, SQL Database and One XML File)
  2. Accessed each with LINQ
static void Main(string[] args)
{
    /*************************************************
    * Creating DataSources for Manipulating by LINQ
    *************************************************/
    //Build DataTable
    DataTable dt = new DataTable();
    dt.Columns.Add("Fruite");
    dt.Columns.Add("Color");
    //Add Few Rows to DataTable
    DataRow dr = dt.NewRow();
    dr[0] = "Orange";
    dr[1] = "Orange";
    dt.Rows.Add(dr);
    DataRow dr1 = dt.NewRow();
    dr1[0] = "Apple";
    dr1[1] = "Red";
    dt.Rows.Add(dr1);
    DataRow dr2 = dt.NewRow();
    dr2[0] = "Banana";
    dr2[1] = "Yellow";
    dt.Rows.Add(dr2);
    DataRow dr3 = dt.NewRow();
    dr3[0] = "Cherry";
    dr3[1] = "Red";
    dt.Rows.Add(dr3);
    /*
    ***********************************
    Accessing DataTable using LINQ
    **********************************
    */
    //Select Elements Where color is red 
    IEnumerable TableData = from e in dt.AsEnumerable() where e[1].ToString() == "Red" select e;
    //print the Result
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine("\n\n************Filtered Data From  DataTable using LINQ*******\n\n");
    Console.ForegroundColor = ConsoleColor.White;
    foreach (DataRow row in TableData)
    {
        Console.WriteLine(String.Format("{0} {1}", row[0], row[1]));
    }
    /* ***************************************
    * Access XML Document using LINQ
    * ***************************************/
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine("\n\n***********Access XML Document using LINQ ******************\n\n");
    Console.ForegroundColor = ConsoleColor.White;
    //Find the books in XML file which belongs to Genre Computer 
    var CompuData = from e in XElement.Load("XMLFile1.xml").Elements("book") where e.Element("genre").Value.ToString() == "Computer" select e;
    // Print the Result of LINQ Query 
    foreach (var obj in CompuData)
    {
        Console.WriteLine(obj);
    }
    /**************************************
    * Access SQL Database using LINQ
    * *************************************/
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine("\n\n*************** LINQ -> SQL *********************\n\n");
    Console.ForegroundColor = ConsoleColor.White;
    //Print Data in Table to Screen
    Database1DataContext db = new Database1DataContext();
    var UserData = from u in db.UserDatas select u;
    foreach (var x in UserData)
    {
        Console.WriteLine(x.ID + " " + x.Username + " " + x.Password);
    }
    Console.ReadKey();
}



Explanation of the Code:

First we are creating some data sources Data Table using simple C# code.
We created a Data Table in which we have added two data Column and added 4 rows to it.

//Select Elements Where color is red

IEnumerable TableData = from e in dt.AsEnumerable() where e[1].ToString() == "Red" select e;

//print the Result
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("\n\n************Filtered Data From  DataTable using LINQ*******\n\n");
Console.ForegroundColor = ConsoleColor.White;


foreach (DataRow row in TableData)
{
    Console.WriteLine(String.Format("{0} {1}", row[0], row[1]));
}


In above code we are accessing DataTable till now we were working with String type so we were using IEnumerable but here we are dealing with DataTable that is consist of the DataRow type so we will get result in IEnumerable object . Here in result we want rows in which color column value is 'Red'.

So we placed condition e[1].ToString() == 'Red' and rest of thing as it is : )


Now lets understand second part  accessing XML Data using LINQ

Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("\n\n***********Access XML Document using LINQ ******************\n\n");
Console.ForegroundColor = ConsoleColor.White;
//Find the books in XML file which belongs to Genre Computer
var CompuData = from e in XElement.Load("XMLFile1.xml").Elements("book") where e.Element("genre").Value.ToString() == "Computer" select e;

// Print the Result of LINQ Query
foreach (var obj in CompuData)
{
    Console.WriteLine(obj);
}

In above code we have selected Book Elements from the XML file and the filtered the book elements those who re having genre == Computer and printed the result on the screen.


and Finally by below code we are accessing the SQL  Database Table using LINQ ...


Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("\n\n*************** LINQ -> SQL *********************\n\n");
Console.ForegroundColor = ConsoleColor.White;


//Print Data in Table to Screen
Database1DataContext db = new Database1DataContext();


var UserData = from u in db.UserDatas select u;


foreach (var x in UserData)
{
    Console.WriteLine(x.ID + " " + x.Username + " " + x.Password);
}

For working with above code we need to prepare a Database and LINQ -> SQL Classes first then we can use this code ..lets se how to do that first..


 I created Database1.mdf from Solution Explorer and in the database I created one table called userData for demo purpose and added some records. 


4-17-2010 7-05-38 PM.gif


Now after building database we need to create LINQ->SQL Classes for that we will add class by going to Solution Explorer >> Right Click Solution and add LINQ to SQL Classes.


4-17-2010 7-09-58 PM.gif


After that we need to create the classes that thing we will do using Graphical Editor Provided by visual studio


4-17-2010 7-08-12 PM.gif


Now you can do the code that I have mentioned above :)

That's it. You have successfully leant how to use LINQ with DataTable XML Data and SQL Server Database using LINQ to SQL classes.

Conclusion:

Article demonstrates how to use LINQ with DataTable, XML Data using LINQ to XML and SQL server data base using LINQ to SQL Classes.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend