Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

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.


advertisement
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.



Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap