A Phased Approach for Converting SQL-centric Applications to LINQ

lmost all applications need to integrate some sort of data, often from multiple data sources such as SQL Server or Oracle database tables. In .NET languages, you would traditionally perform these operations using SQL or an SQL variant in conjunction with a data access technology such as ADO.NET. But when applications need to perform similar data operations against XML files, you would need to write code using the XML classes provided by the .NET Framework. While the intent—retrieving and updating data from a store—is the same in both cases, the knowledge and the code implementations required differ significantly for each data store.

In other words, until now, data access in the .NET framework has lacked a unified and consistent approach to accessing data from disparate data sources. To solve these data access inconsistencies, Microsoft introduced LINQ, the Language INtegrated Query Framework, which adds general-purpose query facilities for querying all types of information, including (but not limited to) relational and XML data. But as usual, technological advances present a quandary; although LINQ provides an excellent set of features for data access, you can't simply throw away your existing investments in ADO.NET and SQL in favor of LINQ. Instead, you need a seamless way to migrate existing SQL-centered applications to use LINQ. This article shows you how to take a phased approach to migrate a SQL-centered ASP.NET application to leverage the features of LINQ.

Introduction to LINQ
LINQ is a family of technologies that provides querying features and class patterns. At this point, the technologies that make up LINQ are LINQ to SQL (formerly DLINQ), which focuses specifically on querying relational data sources, and LINQ to XML (formerly XLINQ), which is geared towards querying XML data.

The SQL-based Implementation
For the purposes of this article, consider a web site named AdventureWorks that displays the categories and products details from the AdventureWorks sample database. In addition, the sample web site also allows users to view and add new departments to the database. From a user viewpoint, the application can be understood by describing three pages: a Product Categories page, a Product Details page, and a Departments page.

The Product Categories Page
The sample web site has a "product categories" page that displays a list of categories and information on the corresponding products in each category. Listing 1 shows the page implementation.

The salient point in code in Listing 1 is how it uses the ObjectDataSource controls, which invoke appropriate middle-tier methods to retrieve the category list and the related product information. For example, the categorySource control's TypeName and SelectMethod attributes are set to AdventureWorksLib.ProductCategory and GetCategories respectively as shown below:

  <asp:ObjectDataSource runat="server" ID="categorySource" 
     SelectMethod="GetCategories" 
     TypeName="AdventureWorksLib.ProductCategory"/>

As the name suggests, the ProductCategory class acts as the data access layer, abstracting communications with the database.

To group the data access layer classes in their own project in this manner, add a new Visual C# Class Library project named AdventureWorksLib to the existing solution, rename the default class to "ProductCategory," and modify its code to look like Listing 2.

The method is straightforward ADO.NET data-retrieval code. It retrieves a connection string, opens a connection, initializes a SqlCommand object with a SQL query, and uses a SqlDataAdapter to fill a DataSet with the query results, returning the first DataTable in the DataSet.

Figure 1. The Product Categories Page: This page displays categories and the products belong to that category.

The other middle tier data-access classes this project follow a similar data access pattern, so I won't discuss those in depth, but I will provide the code as listings. However, you need to know a few other features of the web site before beginning the migration process. So briefly, the Product class abstracts the communication with the Product table in the AdventureWorks database in much the same manner (see Listing 3).

As the names suggest, the GetProductsByCategoryID() method returns products that belong to a particular category, while the GetProductDetailsByProductID() method retrieves product details based on a supplied CategoryID and ProductID.

Navigating to the page from a browser displays the output shown in Figure 1. Note that each product displayed in the GridView control in Figure 1 is hyperlinked. Clicking on a specific product takes you to a product details page.

Next Page