Applications using Linq to SQL

John Charles Olamendy
15y
21.7k
0
0

Article

Introduction

In this article, I want to illustrate the principles, techniques and tools to develop applications using Linq to SQL after a deep research on this topic.

If you look for this topic on Internet, we can see that a lot of .NET developers are blogging and discussing about this new feature of the languages in Microsoft.NET platform and its support by Visual Studio.NET 2008.

Getting to know Linq

Linq stands for Language Integrated Query and it's a new approach to access efficiently any type of data, making querying data a first class concept on .NET languages. It enables creating queries using less code, so the resulting queries are easier to understand.

It's remarkable to say that Linq is not specific to data sources, so it does not matter where the data resides. In the actual implementation, Linq is divided into four common types: Linq to Objects, Linq to DataSet, Linq to SQL, and Linq to XML. You can also extend the Linq framework to support other data source such as LDAP, SAP, etc.

Linq is part of .NET Framework 3.5; consequently we need to use Visual Studio.NET 2008 to work effectively with Linq. Microsoft has also decided to focus attention on C# when using Linq, because this language provides extensions to make working with Linq easier, although most of the techniques in Linq can be applied in Visual Basic.NET as well.

In this article, we're going to focus on Linq to SQL. Linq to SQL seamlessly maps the object model in the application to the data model in the relational data source trying to reduce the object-relational mapping mismatch. There are two main methods for the creation of required business entities and relationships associated to the data model:

SQLMetal, a command line tool.

Object relational designer, an easy to use graphical user interface.

The mechanism to access to the relational database is through the DataContext class. So, we need to create a new class derived from DataContext class, and usually this derived class has the same name than the database which contains the data model. Linq to SQL also relies on classes for specific entities such as tables. In most cases, an entity class is associated to a particular table on the database. This entity class has attributes representing the fields in the underlying table as well as for the primary and foreign keys. You can also find properties that point to a collection of child elements, so representing parent-child relationship in the database.

Getting started with Linq to SQL examples

In order to illustrate the concepts, we're going to create a Windows Forms Application, an ASP.NET Web Applications and a Class Library projects in Microsoft Visual Studio.NET 2008 to test the features of Linq to SQL technology. As the back-end, we're going to use the AdventureWorks database shipped with Microsoft SQL Server 2005/2008 as well as the Production.Product and Production.ProductSubcategory tables.

Next step is to add a Linq to SQL artifact to the Class Library by selecting the Project | Add New Item option (see Figure 1).

Figure 1

Modeling the business entities

After that the Object Relational (O/R) design is launched which enables to model the classes that represent the underlying data model and the methods for each stored procedure. It will also create a strongly typed DataContext class. Unlike the DataSet/TableAdapter approach, when we're defining the entity classes in the designer, we don't have to specify the SQL queries; instead we have to focus on defining the entity classes and the mapping to the underlying data model. SQL to Linq framework will generate the appropriate SQL statements at runtime when you interact and use the business entities.

In order to create the business entities, the easy way is to open a database connection in the Server Explorer windows, select the tables and views you want to model and map from it, and drag and drop them onto the designer surface.

In this case, drag and drop the Production.Product and Production.ProductSubcategory tables on the designer surface (see Figure 2). After that you can see the business entities and their relationship.

Figure 2

Linq to SQL enables modeling stored procedures as methods of the DataContext class. Let's suppose we need to return a list of a product based on a given product subcategory id (see Listing 1).

create procedure spSelectProductBySubcategory
      @pSubcategoryId int
as
begin
      select *
      from Production.Product
      where ProductSubcategoryID=@pSubcategoryId;
end;

go

Listing 1

Now we can use the Server Explorer windows to drag and drop this stored procedure on the in the right pane of the designer surface (see Figure 3).

Figure 3

Executing CRUD operations

Now that we have defined our object model representing the data model, we are ready to execute CRUD (create, read, update, delete) operations over the relational data schema through the object model in C#. For example, to get the products whose makeflag is true (see Listing 2).

ProductionDataContext objDataContext = new ProductionDataContext();
var objResultset = from objProduct in objDataContext.Products
where objProduct.MakeFlag == true

select objProduct;

Listing 2

Now let's create a new product subcategory item (see Listing 3).

ProductionDataContext objDataContext = new ProductionDataContext();

ProductSubcategory objProductSubcategory = new ProductSubcategory();
objProductSubcategory.ProductSubcategoryID = 38;
objProductSubcategory.ProductCategoryID = 4;
objProductSubcategory.Name = "My new category";
objProductSubcategory.ModifiedDate = DateTime.Today;

objDataContext.ProductSubcategories.InsertOnSubmit(objProductSubcategory);

objDataContext.SubmitChanges();

Listing 3

In the following step, we're going to retrieve the newly created product subcategory (its product subcategory id is 42) and update its name field (see Listing 4).

ProductionDataContext objDataContext = new ProductionDataContext();

var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
where objProdSubc.ProductSubcategoryID == 42
select objProdSubc).First();

objProductSubcategory.Name = "Good product subcategory";

objDataContext.SubmitChanges();

Listing 4

The last CRUD operation to test is the delete operation. Now we're going to delete the created product subcategory (its product subcategory id is 42) (see Listing 5).

ProductionDataContext objDataContext = new ProductionDataContext();

var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
where objProdSubc.ProductSubcategoryID == 42
select objProdSubc).First();

objDataContext.ProductSubcategories.DeleteOnSubmit(objProductSubcategory);

objDataContext.SubmitChanges();

Listing 5

Now let's call for the stored procedure defined in the Listing 1 (see Listing 6).

ProductionDataContext objDataContext = new ProductionDataContext();
var objProductSubcategory = objDataContext.spSelectProductBySubcategory(1);

Listing 6

In order to implement database paging, we need to use the Skip and Take methods (see Listing 7).
ProductionDataContext objDataContext = new ProductionDataContext();

ProductionDataContext objDataContext = new ProductionDataContext();

var objProductSubcategory = (from objProdSubc in objDataContext.ProductSubcategories
where objProdSubc.ProductSubcategoryID == 2

select objProdSubc).Skip(200).Take(10);

Listing 7

Data binding in Linq

Now let's add a Windows Form artifact to the Windows application and a DataGridView control (named m_dgvGridView) onto the form for binding purposes. Then add the following code as shown in Listing 8.

ProductionDataContext objDataContext = new ProductionDataContext();

var objResultset = from objProdSubc in objDataContext.ProductSubcategories
where objProdSubc.ProductCategoryID == 2
select objProdSubc;

this.m_dgvGridView.DataSource = objResultset;

Listing 8

When you run the application, we'll get the result as shown in Figure 4.

Figure 4

Let's suppose that we want to remove the productcategoryid and rowguid columns from the result in Figure 4, so we need to re-write the query as in the Listing 9.

ProductionDataContext objDataContext = new ProductionDataContext();

var objResultset = from objProdSubc in objDataContext.ProductSubcategories
where objProdSubc.ProductCategoryID == 2
select new { objProdSubc.ProductSubcategoryID, objProdSubc.Name, objProdSubc.ModifiedDate };

this.m_dgvGridView.DataSource = objResultset;

Listing 9

The result is shown in Figure 5.

Figure 5

Now let's work on the Web counterpart of the solution. Let's open the Default.aspx page on the Web project and add a GridView control onto the Web page (see Listing 10).

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplicationLinqTest._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h3>Product Subcategories</h3>
        <asp:GridView ID="m_gvGridView" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>

</html>

Listing 10

Now let's add the logic to the application (see Listing 11).

ProductionDataContext objDataContext = new ProductionDataContext();

this.m_gvGridView.DataSource = objResultset;

this.m_gvGridView.DataBind();

Listing 11

The result is shown in the Figure 6.

Figure 6

Executing transactions

When you execute the SubmitChanges method of the DataContext, then all the updates to the data are wrapped in a transaction, so the underlying relational database will never be in an inconsistent state after updating the changes.

If no transaction is already in the scope, then the SubmitChanges method will automatically start a database transaction. You can also define your transaction scope using the TransactionScope object. This makes very easy to integrate your transactional code with existing one.

Conclusion

In this article, I've covered the principles, techniques and tools to develop an enterprise application using Linq to SQL.