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

Migrating the Product Class
To migrate the SQL-based data access layer in the Product class to LINQ to SQL, add a new C# class named Product to the AdventureWorksLINQLib project and modify its code 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 Product
     {
       public Product(){}
       
       public object GetProductsByCategoryID(int categoryID)
       {
         string connectionString = WebConfigurationManager.
           ConnectionStrings["AdventureWorks"].ConnectionString;
         SqlConnection connection = new 
           SqlConnection(connectionString);
         AdventureWorks db = new AdventureWorks(connection); 
         var products = from product in db.Production.Product
           where product.ProductSubcategoryID == categoryID                           
           orderby product.ProductID
           select product;        
         return products;
       }
   
       public object GetProductDetailsByProductID(int productID)
       {
         string connectionString = WebConfigurationManager.
           ConnectionStrings["AdventureWorks"].ConnectionString;
         SqlConnection connection = new 
           SqlConnection(connectionString);
         AdventureWorks db = new AdventureWorks(connection); 
         var products = from product in db.Production.Product
           where product.ProductID == productID                           
           select product;        
         return products;           
       }
     }
   }
   

Although this class is similar to the ProductCategory class, it uses a where clause in the LINQ queries to filter the results.

Migrating the Department Class
In addition to providing powerful query features, LINQ to SQL also exposes an API through which you can make changes to the in-memory data and submit those changes back to the database. To accomplish this, all you do is to modify the in-memory object and then call the SubmitChanges() method on the object. Note that updatable objects must derive from the DataContext class for SubmitChanges() to function properly. In this case, the AdventureWorks class derives from the DataContext class. The DataContext class maintains enough tracking information to be able to submit a change to the database. It also provides facilities for configuring optimistic concurrency behaviors. The following code shows an example that adds a new department to the Department table through the LINQ to SQL layer.

   public void InsertDepartment(DepartmentInfo dept)
   {
     string connectionString = WebConfigurationManager.
       ConnectionStrings["AdventureWorks"].ConnectionString;
     SqlConnection connection = new SqlConnection(connectionString);
     AdventureWorks db = new AdventureWorks(connection);
     Department department = new Department();
     department.Name = dept.Name;
     department.GroupName = dept.GroupName;            
     department.ModifiedDate = dept.ModifiedDate;
     db.HumanResources.Department.Add(department);
     db.SubmitChanges();
   }
   

Here's how it works. To add a new department row to the table, the code initializes a Connection object and an AdventureWorks object, and then goes through the following steps:

1. Create a new Department object that represents a department instance.
2. Populate the properties of the Department object with appropriate values.
3. Add the department object to the Department collection using the Add() method.
4. Commit the changes back to the database using the SubmitChanges() method. Note that LINQ does not commit changes to the database until you invoke the SubmitChanges() method!

Internally, when you call SubmitChanges(), the DataContext translates all your in-memory updates into corresponding INSERT, UPDATE, and DELETE SQL commands. If you don't like the default submission behavior, you can customize it by overriding the auto-generated code with custom commands.

This article discussed a phased approach to migrate an existing SQL-centered ASP.NET Web site to leverage LINQ. You saw how to create LINQ replacements for mid-tier ADO.NET classes that execute select and insert queries against a data store. You should take away two main points. First, you don't have to completely rewrite your applications to migrate to LINQ—you can convert data-access classes as have time permits, with minimal impact to your front-end code. Second, migrating to LINQ lets you work directly with data in an object-oriented way, and—although not directly shown here—eliminates the need to know different SQL flavors and data-access APIs to work with different stores.

To apply these techniques to your own applications, you should start by studying the key LINQ concepts, particularly its query syntax. You should also familiarize yourself with the various options for using the sqlmetal utility to generate object-mapping code.

Previous Page

Other Articles by this Author