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:
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.
The Product Details Page
Like the product categories page in Listing 1, the ASP.NET page that displays product details also uses an ObjectDataSource control. The key difference is that the ObjectDataSource control retrieves a ProductID value (passed via a URL query string) and passes that as an argument to the GetProductDetailsByProductID() method (see Listing 4).
When you click on a specific product hyperlink in the product categories page, you’ll see a page similar to Figure 2.
 | ? |
| Figure 2. Product Details Page: This page displays the details of a specific product. |
The Department Page
In addition to displaying the products and categories information, the AdventureWorks web site also exposes a feature to add a new department to the Department table. Listing 5 shows the code for the web page supporting that feature:
Note that the deptSource control has its InsertMethod attribute set to InsertDepartment(), which accepts an argument of type DepartmentInfo. To pass an object as an argument to the method, you set the DataObjectTypeName attribute to the name of the object. The TypeName attribute is set to “Departments,” which is the class that handles the communication required to perform CRUD operations against the Department table (see Listing 6).
The Departments class has two methods: GetDepartments() and InsertDepartment(). The InsertDepartment() method accepts an object of type DepartmentInfo. As the name suggests, this is a placeholder class that just contains properties for holding department related attributes (see Listing 7).
If you navigate to the department Web page from the browser you’ll see a page similar to Figure 3.
|
|
To add a department, click on the “New” link in Figure 3. You will be redirected to a page wherein you can enter the new department details (see Figure 4).
Now that I’ve described the application’s structure, the rest of this article concentrates on migrating the application to use LINQ.
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
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:
[Table(Name="[Production].[ProductSubcategory]")] public partial class ProductSubcategory : System.Data.DLINQ.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged { 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 /code:AdventureWorks.csAfter 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. ConnectionStrings["AdventureWorks"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); AdventureWorks db = new AdventureWorks(connection); var categories = from category in db.Production.ProductSubcategory 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 db.Production.ProductSubcategory 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.
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.
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:
- Create a new Department object that represents a department instance.
- Populate the properties of the Department object with appropriate values.
- Add the department object to the Department collection using the Add() method.
- 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.
