WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
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 LINQyou 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, andalthough not directly shown hereeliminates 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.