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

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.



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 <ELEMENTNAME> in<DATA_SOURCE_NAME> where <YOUR_CONDITION_ON_DATA> select <ELEMENT_NAME>

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<DataRow> 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<DataRow> 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<string> but here we are dealing with DataTable that is consist of the DataRow type so we will get result in IEnumerable<DataRow> 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. 

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.

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

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.