LINQ in Visual Studio 2008

LINQ was first introduced in C# 3.0 and added to Visual Basic 9.0 with .NET 3.5.

LINQ and the Visual Studio 2008 IDE make it easy to pull data from a SQL Server database via a template item called LINQ to SQL Classes that you may add to a project. A dbml resource is created when you add this item type with a default name (that you may change) of DataClasses1.dbml.
Once the item is added, the O/R Designer pane opens within Visual Studio. This allows you to drag and drop database items (tables, views, stored procedures) to the O/R Designer which generates the dbml file, which provides the mapping between the LINQ to SQL classes and database objects. The O/R Designer also generates the typed DataContext and the entity classes.
There are two main areas within the O/R Designer pane: Entities and Methods. The main area of the design area is for entities like Tables and Views, along with their hierarchies and relationships. The Methods pane includes stored procedures and functions that are mapped to methods of the DataContext class. The O/R Designer currently supports SQL Server 2000, SQL Server 2005, and SQL Server Express Edition.

Suppose a Web Form contains an instance of the GridView controlwhich displays data via LINQ. Code is placed in the form’s Page_Load event, so the data is loaded when the page is opened.

The first step is to create an instance of the DataContext class. This provides database access, so I can access its included table as a property of the DataContext class. Once the DataContext class is created, I can pull data from it via a query.
The code includes a query that pulls all data records and columns from the Customers table. The data is sorted by the second column, and the query is executed when it is bound to the GridView control on the page.
[C#]
protected void Page_Load(object sender, EventArgs e) {
DataClasses1DataContext db = new DataClasses1DataContext();
var customers = from p in db.Customers
orderby 1 "1 means the second column"
select p;
gvCustomers.DataSource = customers;
gvCustomers.DataBind();
}
[VB]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As DataClasses1DataContext = New DataClasses1DataContext()
Dim customers As Object
customers = From p In db.Customers Order By 1 Select p
gvCustomers.DataSource = customers
gvCustomers.DataBind()
End Sub

When the page loads, all data from the Customers table is displayed in the GridView control.
As a further example, I can easily call the SQL Server stored procedure as a method of my DataContext object. The results of the method – stored procedure — can be used to populate a data control like the previously used GridView, as the next code snippet demonstrates:
[C#]
protected void Page_Load(object sender, EventArgs e) {
DataClasses1DataContext db = new DataClasses1DataContext();
var top_customers = db.Ten_Most_Expensive_Products();
gvCustomers.DataSource = top_customers;
gvCustomers.DataBind();
}
[VB]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As DataClasses1DataContext = New DataClasses1DataContext()
Dim customers As Object
customers = db.Ten_Most_Expensive_Products()
gvCustomers.DataSource = customers
gvCustomers.DataBind()
End Sub

These simple examples demonstrate query syntax and how to use the O/R Designer within Visual Studio 2008. LINQ allows you to manipulate (update, delete, add) and read data.
Details at Microsoft MSDN...

0 comments:

Post a Comment

Note: Only a member of this blog may post a comment.