Last week, I introduced the basics about the .NET Language-Integrated Query (LINQ) technology. Now I’ll cover how the O/R Designer feature in Visual Studio 2008 that allows you to use LINQ to access SQL Server.

(Before diving into the code, I want to mention that TechRepublic member hajray posted a question about LINQ’s inclusion in the .NET Framework. It is worth noting that LINQ was first introduced in C# 3.0 and added to Visual Basic 9.0 with .NET 3.5.)

O/R Designer

LINQ and the Visual Studio 2008 IDE make it easy to pull data from a SQL Server database. Visual Studio includes 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. The O/R Designer 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.

Before diving into an example, let’s take a closer look at LINQ queries.

The query

There are three steps with accessing data via LINQ: obtain data source, create query, and execute the query. A query is used to define what data is retrieved from a data source. In addition, you may manipulate the data via sorting, grouping, and so forth. A query is stored in a query variable and initialized with a query expression.

Queries have their own syntax, which strongly resembles standard SQL. A query expression has three clauses: from, where, and select. The from clause defines the data source; the select clause defines what is returned; and the where clause (which is optional) lets you refine a search to include only data that meets certain criteria. MSDN provides more information on LINQ queries.

The query is not executed until it is accessed; this is referred to as deferred execution. The next example provides a peek at its use.

An example

I created a Web site project that includes a LINQ to SQL Classes item, which connects to the standard Northwind database on SQL Server 2000. One entity is created for the Customers table. In addition, a method is created for the ‘Ten Most Expensive Products’ stored procedure. The O/R Designer creates the dbml file named DataClasses1.dbml.

Once the DataContext class is created for the table and the stored procedure in the Northwind database, I can use it in other code. When using the created DataContext class, it is the class name with DataContext appended to the name. For my example, I use DataClasses1DataContext. (Visual Studio IntelliSense fully supports LINQ and its classes, so you can easily avoid a typo.)

I use the following ASP.NET Web Form in my example:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LINQTest._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml" >


<head runat="server">


<title>LINQ Example</title></head>


<body>


<form id="frmLINQTest" runat="server">


<div>


<asp:GridView ID="gvCustomers" runat="server"></asp:GridView>

</div></form></body></html>

This Web Form contains an instance of the GridView control; this control 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.

protected void Page_Load(object sender, EventArgs e) {

DataClasses1DataContext db = new DataClasses1DataContext();


var customers = from p in db.Customers


orderby 1


select p;


gvCustomers.DataSource = customers;


gvCustomers.DataBind();

}

Here is the equivalent Visual Basic code:

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:

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();

}

Here is the equivalent Visual Basic code:

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.

Will developers use LINQ?

I wonder how widely developers will adopt LINQ. My guess is that experienced developers will stick with their current approach, and newer developers will dive in with LINQ. I see promise for manipulating other data sources like XML with LINQ. I will take a look at using LINQ to work with XML next week.

Do you see a future for LINQ over existing techniques for working with databases? Do you plan on using LINQ in current or upcoming projects? Share your thoughts with the Visual Studio Developer community.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

—————————————————————————————

Get weekly development tips in your inbox
TechRepublic’s free Visual Studio Developer newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically subscribe today!