Introduction
I have been working on C#, ASP.Net and SQL Server long time. Now the time has come for the need to explore the latest .Net framework 3.0 and 3.5 features. This is because many companies are looking forward to call their windows based applications through web as Smart client. Some applications need animated (2D/ #D) graphics in their sites.
I hope all the latest applications (.Net based) use the goodness of the latest .Net Technologies to build web based data centric enterprise applications.
As may of us developing enterprise data oriented applications, we have some difficulties in debugging the database objects like stored procedures, functions etc. We need to write a bit more code to get data from database, update and save it back to database, searching for the data and for providing pagination and sorting for the data.
LINQ solves all the above discussed difficulties
.NET Language-Integrated Query
After two decades, the industry has reached a stable point in the evolution of object-oriented (OO) programming technologies. Programmers now take for granted features like classes, objects, and methods. In looking at the current and next generation of technologies, it has become apparent that the next big challenge in programming technology is to reduce the complexity of accessing and integrating information that is not natively defined using OO technology. The two most common sources of non-OO information are relational databases and XML.
Rather than add relational or XML-specific features to our programming languages and runtime, with the LINQ project we have taken a more general approach and are adding general-purpose query facilities to the .NET Framework that apply to all sources of information, not just relational or XML data. This facility is called .NET Language-Integrated Query (LINQ).
LINQ has a great power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable
• LINQ to Object {Queries performed against the in-memory data}
• LINQ to ADO.Net
• LINQ to SQL (formerly DLinq) {Queries performed against the relation database only Microsoft SQL Server Supported}
• LINQ to DataSet {Supports queries by using ADO.NET data sets and data tables}
• LINQ to Entities {Microsoft ORM solution}
• LINQ to XML (formerly XLinq) { Queries performed against the XML source}
In this article I am trying to extract data from multiple database tables using LINQ. We need to have some knowledge in simple SQL query writing. LINQ looks like a simple SQL query for extracting the data. We can use LAMDA expressions and Extension methods in this process. We can easily execute stored procedures and functions
The DataContext class provides the main entry point for the LINQ to SQL framework. 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 operations.
In my Example I am extracting data from Northwind Database (in SQL 2005). You can download sample databases from Microsoft site (http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en) and it is very easy to install the databases in SQL server 2005
Step #1: Create a new website (C#) in Visual Studio 2008 / Visual Studio 2005.
Step #2: Right click on the project and click on “Add + New Item”. Select “Visual C#” in categories and “LINQ to SQL classes” from templates, rename the file (.dbml) name as “Northwind.dbml”
Step #3: Click on View menu in Visual Studio IDE and select Server Explorer. This shows the available Database connections. Right click on “Data Connections” in Server explorer and then click on “Add New Connection”. Provide the required information to get connected with “Northwind” database in SQL Server
Step #4: Explore the new data base connection for tables, select “Categories”, “Order Details”, “Orders” and “Products” tables from the lists. Drag and drop the selected tables on the “LINQ to SQL Classes” (Northwind.dbml) designer. As specified below
Step #5: Add a GridView control to Default.aspx page. Open the Default.aspx.cs file and add the below code
protected void Page_Load(object sender, EventArgs e)
{
int startRow = Convert.ToInt32(Request.QueryString["Row"]);
BindProducts(startRow);
}
private void BindProducts(int startRow)
{
NorthwindDataContext db = new NorthwindDataContext();
var products = from p in db.Products
//where p.Category.CategoryName == "Beverages"
//select p;
//where p.OrderDetails.Count > 2 && p.Category.CategoryName == "Beverages"
where p.Order_Details.Count > 2
select new
{
ID = p.ProductID,
Name = p.ProductName,
NumOrders = p.Order_Details.Count,
Revenue = p.Order_Details.Sum(o => o.UnitPrice * o.Quantity)
};
GridView1.DataSource = products.Skip(startRow).Take(10);
GridView1.DataBind();
}
}
Run the application, we can see the data in GridView control
As we can debug the query (in code), put some break points at “var products” and watch the below query
“{SELECT [t0].[ProductID] AS [ID], [t0].[ProductName] AS [Name], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[ProductID] = [t0].[ProductID]
) AS [NumOrders], (
SELECT SUM([t4].[value])
FROM (
SELECT [t3].[UnitPrice] * (CONVERT(Decimal(29,4),[t3].[Quantity])) AS [value], [t3].[ProductID]
FROM [dbo].[Order Details] AS [t3]
) AS [t4]
WHERE [t4].[ProductID] = [t0].[ProductID]
) AS [Revenue]
FROM [dbo].[Products] AS [t0]
WHERE ((
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t1]
WHERE [t1].[ProductID] = [t0].[ProductID]
)) > @p0 }
This is interesting thing, actually LINQ expression are converted into SQL when they get executed at database server end.
One more interesting thing is LINQ supports for paging also, look at the below line of code
GridView1.DataSource = products.Skip(startRow).Take(10);
We can start at a specific position of the row and take the no of other required rows. This can be done in LINQ using method extensions. Wow! How simple it is now for paging the data in the grid view
Hope this helps you
Regards,
Krishna
No comments:
Post a Comment