Applications using Linq to SQL

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();

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

this.m_gvGridView.DataSource = objResultset;