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 <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
- Created Some Sample Data Sources (Created DataTable, SQL Database and One XML File)
- 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.
![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.