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 = "" +
"" +
"" +
" ";
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 = "" + _
"" + _
"" + _
" "
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