RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


A Phased Approach for Converting SQL-centric Applications to LINQ : Page 3

LINQ lets you use an object-centric approach to access and update data along with convenient automated mapping. Best of all, you don't have to completely rewrite your applications to take advantage of it.

Migrating the Application to LINQ
To migrate the AdventureWorks web site, I'll describe a seamless approach in which you simply migrate the data access layer to use the LINQ to SQL's features. This phased approach lets you introduce LINQ to SQL in the appropriate layers of your application without going through a big bang migration.

Figure 5. The Sqlmetal Utility: This utility generates the LINQ object model based on the schema of a supplied database. In one mode, the utility generates an object model containing classes decorated with attributes that indicate the relational-to-object mapping.
The first step in migration is to generate a LINQ object model that provides an object representation of the AdventureWorks database. Although you can hand code the object representation (if you have a good understanding of the internals of LINQ), the LINQ team provides you with a command line utility named sqlmetal that automates the code generation. If you have the May 2006 LINQ CTP edition installed, you can find the sqlmetal utility in the \Program Files\LINQ Preview\Bin folder. Navigate to this folder and run the sqlmetal utility as shown in Figure 5.

The command in Figure 5 generates a single C# file named AdventureWorks.cs. The auto-generated file contains a number of classes that follow the attribute-based mapping approach—meaning that all the classes are decorated with the required relational data mapping.

As an example, if you look at the declaration of the ProductSubcategory class, you will see the class being decorated with the name of the table, which in this case is also ProductSubCategory:

   public partial class ProductSubcategory : 
     private int _ProductSubcategoryID;  
     private int _ProductCategoryID;
     private string _Name;
In the class declaration above, note that the class names and class properties are decorated with the table names and column names respectively. Sometimes this tight coupling makes it difficult for you to upgrade the database schema. You can overcome this limitation by specifying the mapping information in an external XML file. This allows you to make changes to the runtime schema mapping on the fly without having to recompile the code. To generate this external XML mapping file, supply the /map switch to sqlmetal along with the name of the XML file as shown below:

   sqlmetal /server:localhost /database:AdventureWorks 
      /user:sa /password:thiru /map:AdventureWorksMapping.xml 
After establishing the data mappings and relationships, you can write LINQ to SQL code to execute CRUD operations against the database.

First, create a new Visual C# LINQ Class Library project named AdventureWorksLINQLib using Visual Studio 2005. After creating the project, add a class named ProductCategory and modify its code to look as follows:

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Data.DLinq;
   using System.Data.SqlClient;
   using System.Query;
   using System.Web.Configuration;
   namespace AdventureWorksLINQLib
     public class ProductCategory
       public ProductCategory(){}
       public object GetCategories()
         string connectionString = WebConfigurationManager.
         SqlConnection connection = new 
         AdventureWorks db = new AdventureWorks(connection);
         var categories = from category in 
           orderby category.ProductSubcategoryID           
           select category;        
         return categories;      
The preceding code starts by importing a set of namespaces, including such core LINQ namespaces as System.Query and System.Data.DLinq.

Just like the existing ADO.NET code, the GetCategories() method starts by retrieving the connection string from the Web.config file and creating a SqlConnection object.

   SqlConnection connection = new SqlConnection(connectionString);
Supply that SqlConnection object as an argument to the constructor of the AdventureWorks class.

   AdventureWorks db = new AdventureWorks(connection);     
Note that if you are using an external mapping file, you need to pass that information (in the form of XmlMappingSource object) to the AdventureWorks constructor in addition to the SqlConnection object.

After initializing the AdventureWorks class, you can now query any table in the AdventureWorks database using simple "dot" notation. As an example, you can retrieve all the categories from the ProdutSubcategory table by using the syntax db.Production.ProductSubcategory.

   var categories = from category in 
      orderby category.ProductSubcategoryID           
      select category;        
Similar LINQ syntax lets you query any type of data source in an object-oriented way that's conceptually similar to querying a table in T-SQL. In the example, the LINQ syntax is functionally similar to the T-SQL statement in that it allows you to query the ProductSubcategory table using the from..where..select syntax. The main difference is that the from category in db.Production.ProductSubcategory clause appears at the beginning, while the select category clause appears at the end.

Essentially, the query retrieves all the rows from the ProductSubcategory table. It is very important to note that you need a way to refer to the objects inside the query so that you can use standard query operators like "where" with those objects. In this case, "category" refers to each object inside the ProductSubcategory table. As the name suggests, the orderby clause lets you order the output—in this case, by the ProductSubcategoryID column.

Now that you have created the new middle tier object, you can consume it from the ProductCategories.aspx page. The only change you need to make is to update the ObjectDataSource control's TypeName property, substituting the new class name. Here's the modified ObjectDataSource control for retrieving the product categories information.

   <asp:ObjectDataSource runat="server" ID="categorySource" 
It's really that simple. If you navigate to the Web page, you will see the same output. As you can see, this migration process lets you take advantage of LINQ to SQL with minimal impact to existing code.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date