(Language-Integrated Query)
FYI, IT managers like to develop information management systems rapidly with best quality. Moreover, they want to have a perfect access to RDBMS objects into the application during manufacture and maintenance phases. Furthermore, they need to a technology for easy connect to RDBMSs, high speed data transferring and low connection pool. Certainly, software companies were started to invent new programming technologies for solving these problems.
Many solutions are preferred but Oracle established SQLJ and Sun Microsystems offered Object-relational mapping (OR mapping) in 90s. SQLJ was a rapid application programming that programmers could write and execute SQL command embedded in JAVA application by it. It was decreased delivery line of code (DLOC) in JAVA-Oracle projects. OR mapping was a programming concept that it was integrated object programming language capabilities with relational databases managed by Oracle, DB2, Sybase, and other RDBMSs.
Microsoft Corporation discusses some questions in 2006 such as "Why don't we use these technologies together in a same programming framework?" or "Why doesn't we make relationships by these technologies with other data containers such as XML or flat-file databases?". The Microsoft offered the LINQ project into .NET framework 3.5 for marking of information management systems in January 2008. We will take about this technology as follow.
OR mapping:
Object-relational mapping (OR mapping) products integrate object programming language capabilities with relational databases managed by popular RDBMSs. Database objects appear as programming language objects in one or more existing object programming languages. Often, the interface for object-relational mapping products is the same as the interface for object databases.
The LINQ project:
The LINQ Project is a codename for a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.
LINQ to SQL:
LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.
Note: Relational data appears as a collection of two-dimensional tables (relations or flat files), where common columns relate tables to each other. To use LINQ to SQL effectively, you must have some familiarity with the underlying principles of relational databases.
In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.
LINQ integrates object programming language capabilities with relational databases as well as JAVA OR mapping and it is very rapider than ADO.net as well as SQLJ too. In LINQ, you don't need to successively control of database connection. Because it automatically creates database connection setting and it doesn't preformed like open database connectivity (ODBC). Let consider a full example that it contents all of basic necessary subject for getting start LINQ to SQL.
We have table in our database that it's called "Categories". This table has two columns, "Code" and "Category". Data type of "Code" is INT and data type of "Category" is VARCHAR. At first, we add a DataContext to our project and drag "Categories" table from Server Explorer into DataContext. The DataContext represents the main entry point for the LINQ to SQL framework.
public class DataContext : IDisposable
The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance.
In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operation.
In the first step, we import following namespace for working with LINQ to SQL.
using System.Data.Linq;
Insert:
You can use following method for inserting a row into "Categories" table.
public string Insert(int CategoryCode, string CategoryName)
{
try
{
DataClassesDataContext db = new DataClassesDataContext();
Categories tbl = new Categories()
{
Code = CategoryCode,
Category = CategoryName
};
db.Categories.InsertOnSubmit(tbl);
db.SubmitChanges();
return "Inserting was successfuly";
}
catch(Exception)
{
return "Inserting was failed";
}
}
Delete:
You can also use following method for deleting a row from "Categories" table.
public string Delete(int CategoryCode)
{
try
{
DataClassesDataContext db = new DataClassesDataContext();
var tbl = (from c in db.Categories
where c.Code == CategoryCode
select c).First();
db.Categories.DeleteOnSubmit(tbl);
db.SubmitChanges();
return "Deleting was successfuly";
}
catch(Exception)
{
return "Deleting was failed";
}
}
Update:
You can use following method for updating a specified row in "Categories" table.
public string Update(int CategoryCode, string CategoryName)
{
try
{
DataClassesDataContext db = new DataClassesDataContext();
var tbl = (from c in db.Categories
where c.Code == CategoryCode
select c).First();
tbl.Category = CategoryName;
db.SubmitChanges();
return "Updating was successfuly";
}
catch(Exception)
{
return "Updating was failed";
}
}
Binding a GridView in ASP.net:
If we have a GridView on our ASP form that it's called GridView1, we can bind data form "Categories" table into the GridView by LINQ as follow.
private void BindGrid()
{
DataClassesDataContext db = new DataClassesDataContext();
GridView1.DataSource = db.Categories;
GridView1.DataBind();
}
Covert Generic Table to DataTable:
At first, for converting generic table to DataTable in LINQ, you need to import following namespace.
using System.Reflection;
Then, you can convert "Categories" table's data to a DataTable by below method.
public DataTable GenericToDatatable()
{
DataTable dt = new DataTable();
DataClassesDataContext db = new DataClassesDataContext();
foreach (object obj in db.Categories)
{
Type t = obj.GetType();
PropertyInfo[] pis = t.GetProperties();
if (dt.Columns.Count == 0)
{
foreach (PropertyInfo pi in pis)
{
dt.Columns.Add(pi.Name, pi.PropertyType);
}
}
DataRow dr = dt.NewRow();
foreach (PropertyInfo pi in pis)
{
object value = pi.GetValue(obj, null);
dr[pi.Name] = value;
}
dt.Rows.Add(dr);
}
return dt;
}
Getting Report:
For providing a report from "Categories" table's data, we created a Crystal Report file for "Categories" table that it's called "CrystalReport1". Now, we can provide a report from "Categories" table by below method.
public CrystalReport1 GetReport()
{
DataClassesDataContext db = new DataClassesDataContext();
IEnumerable
select c;
CrystalReport1 cr = new CrystalReport1();
cr.SetDataSource(categories);
return cr;
}
Furthermore, you can see 101 LINQ samples at bellow link.
http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx
LINQ to XML:
LINQ to XML was developed with Language-Integrated Query over XML in mind and takes advantage of standard query operators and adds query extensions specific to XML.
LINQ to XML provides an in-memory XML programming interface that leverages the .NET Language-Integrated Query (LINQ) Framework. LINQ to XML uses the latest .NET Framework language capabilities and is comparable to an updated, redesigned Document Object Model (DOM) XML programming interface.
The LINQ family of technologies provides a consistent query experience for objects (LINQ), relational databases (LINQ to SQL), and XML (LINQ to XML).
By using LINQ to XML, you could run the following query to obtain the part number attribute value for every item element in the purchase order:
IEnumerable
from item in purchaseOrder.Descendants("Item")
select (string)item.Attribute("PartNumber");
As another example, you might want a list, sorted in part number order, of the items with a value greater than $100. To obtain this information, you could run the following query:
IEnumerable
from item in purchaseOrder.Descendants("Item")
where (int)item.Element("Quantity") *
(decimal)item.Element("USPrice") > 100
orderby (string)item.Element("PartNumber")
select item;
The ease with which you can create XML trees is particularly significant. For example, to create a small XML tree, you can write C# code as follows:
XElement contacts =
new XElement("Contacts",
new XElement("Contact",
new XElement("Name", "Patrick Hines"),
new XElement("Phone", "206-555-0144",
new XAttribute("Type", "Home")),
new XElement("Address",
new XElement("Street", "123 Main St"),
new XElement("City", "Mercer Island"),
new XElement("State", "WA"),
new XElement("Postal", "68042")
)
) );Convert to Generic List
using System.Xml.Linq;
using System.Collections.Generic;
public class MyClass
{
public List
{
XDocument xdoc = XDocument.Load(HttpContext.Current.Server.MapPath("~/App_LocalResources/MyXML.xml"));
var feeds = from c in xdoc.Descendants("Feed")
select new FeedDefination
{
name = c.Element("name").Value,
url = c.Element("url").Value
};
return feeds.ToList();
}
}
public class FeedDefination
{
public string name { get; set; }
public string url { get; set; }}
LINQ's Advantages:
You can use LINQ for classification of the use cases' domain. Moreover, it can increase" functional cohesion" and "message coupling" for business rule (BR) and data access (DA) layers. As a matter of fact, you can take a set of related tables in a data content class that they provide a particular framework in your system. LINQ merged common layer with data access layer in Microsoft 3-tire architecture. So, it decreased density and distribution of our codes in .NET projects.
LINQ's Usages:
The LINQ is best option for developing information management systems (IMS). Furthermore, LINQ is great tools for developing and improving enterprise resource planning (ERP) project. You can develop enterprise systems (ES) by LINQ at list possible time.
Author: Mahyar Esteki
Special Tanks: Morteza Akbarpour and Ahmadreza Seddighi
References:
· MSDN website
http://msdn.microsoft.com/
· Service Architecture website
http://www.service-architecture.com
· CodeProject website
http://www.codeproject.com
· CodePlex website
http://www.codeplex.com
0 comments:
Post a Comment