Data binding with Linq to SQL and ASP.NET

Introduction

Linq to SQL is an ORM technology shipped within Microsoft.NET 3.5 enabling to map the object model of your solution with the relational data schema in the database system. You can also use this technology to query the database and execute transactional operations such as update/insert/delete.

In this article, I will illustrate how to use the new features of ASP.NET 3.5 to support data binding with Linq to SQL, specifically using the <asp:LinqDataSource> control (similar to ObjectDataSource and SQLDataSource) to specify declaratively the data binding from Web controls and the object model in Linq.

Getting started with the solution

In this solution, we’re going to create a Web application for showing a list of products by category. The users will be able to skip the product list using the paging mechanism, and finally the users will be able to edit (in a detail view of the products) and delete products from the list.

This Web application will be implemented using the three-layer approach. The business rules, business process logic and domain model will be in the business logic layer. The Web pages, Web controls and binding mechanism is part of the presentation layer. And finally, the data access logic and the underlying mapping to the application domain model is part of the data access layer. For this application, the ORM technology is Linq to SQL.

Now let’s get started with the solution by opening the Visual Studio.NET 2008 IDE and creating an ASP.NET Web Application for the presentation layer and a Class Library project for the business logic and data access layers. The main data source for this solution is the Production.Product and Production.ProductSubcategory tables in the AdventureWorks database shipped with Microsoft SQL Server 2005/2008.

First step is to define the data model in the Class Library project by adding a “Linq to SQL Classes” artifact (see Figure 1).

Figure 1

Then connect to the back-end database, and define the object model and the underlying mappings to the underlying relational data source (see Figure 2).

Figure 2

Then compile this project and add a reference to it from the Web application and open to the Default.aspx page.

Next step is to add GridView Web control in the Web form and we’re going to use the <asp:linqdatasource> control to bind the object model to the GridView control. Visual Studio.NET has a built-in support for this operation, so we’re going to switch into the design view, select the GridView control, click on the smart tag and then choose the “New data source” option (see Figure 3).

Figure 3

Then choose the Linq option in the “Data Source Configuration Wizard” wizard and click on the OK button (see Figure 4).

Figure 4

In the “Choose a Context Object” page, you can see the available Linq to SQL DataContext classes that the application is defined or is referenced in other assemblies and click on the Next button (see Figure 5).

Figure 5

In the “Configure Data Selection” page, you can select the table, the fields to display and grouping option. You can also define a filter and establish an order of the records. In this case, we’re going to show all the records and fields of the Product table. After this configure, you can click on the Finish button (see Figure 6).

Figure 6

You can see the result in the Figure 7.

Figure 7

In order to define the paging, editing and deleting features on the GridView, you need to click on the smart tag of LinqDataSource object (ldsProducts) and check the “Enable Delete” and “Enable Update” options (see Figure 8).

Figure 8

Then click on the smart tag of the GridView control and check the “Enable Paging”, “Enable Editing” and “Enable Deleting” options (see Figure 9).

Figure 9

If you switch into the Source view, you can see the underlying generated code (see Listing 1).

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

 

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;

 

<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >

<head runat=”server”>

<title>Linq to SQL data binding testing</title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”True”

AutoGenerateColumns=”False” DataKeyNames=”ProductID” DataSourceID=”ldsProducts”>

<Columns>

<asp:CommandField ShowEditButton=”True” />

<asp:CommandField ShowDeleteButton=”True” />

<asp:BoundField DataField=”ProductID” HeaderText=”ProductID”

InsertVisible=”False” ReadOnly=”True” SortExpression=”ProductID” />

<asp:BoundField DataField=”Name” HeaderText=”Name” SortExpression=”Name” />

<asp:BoundField DataField=”ProductNumber” HeaderText=”ProductNumber”

SortExpression=”ProductNumber” />

<asp:CheckBoxField DataField=”MakeFlag” HeaderText=”MakeFlag”

SortExpression=”MakeFlag” />

<asp:CheckBoxField DataField=”FinishedGoodsFlag” HeaderText=”FinishedGoodsFlag”

SortExpression=”FinishedGoodsFlag” />

<asp:BoundField DataField=”Color” HeaderText=”Color” SortExpression=”Color” />

<asp:BoundField DataField=”SafetyStockLevel” HeaderText=”SafetyStockLevel”

SortExpression=”SafetyStockLevel” />

<asp:BoundField DataField=”ReorderPoint” HeaderText=”ReorderPoint”

SortExpression=”ReorderPoint” />

<asp:BoundField DataField=”StandardCost” HeaderText=”StandardCost”

SortExpression=”StandardCost” />

<asp:BoundField DataField=”ListPrice” HeaderText=”ListPrice”

SortExpression=”ListPrice” />

<asp:BoundField DataField=”Size” HeaderText=”Size” SortExpression=”Size” />

<asp:BoundField DataField=”SizeUnitMeasureCode”

HeaderText=”SizeUnitMeasureCode” SortExpression=”SizeUnitMeasureCode” />

<asp:BoundField DataField=”WeightUnitMeasureCode”

HeaderText=”WeightUnitMeasureCode” SortExpression=”WeightUnitMeasureCode” />

<asp:BoundField DataField=”Weight” HeaderText=”Weight”

SortExpression=”Weight” />

<asp:BoundField DataField=”DaysToManufacture” HeaderText=”DaysToManufacture”

SortExpression=”DaysToManufacture” />

<asp:BoundField DataField=”ProductLine” HeaderText=”ProductLine”

SortExpression=”ProductLine” />

<asp:BoundField DataField=”Class” HeaderText=”Class” SortExpression=”Class” />

<asp:BoundField DataField=”Style” HeaderText=”Style” SortExpression=”Style” />

<asp:BoundField DataField=”ProductSubcategoryID”

HeaderText=”ProductSubcategoryID” SortExpression=”ProductSubcategoryID” />

<asp:BoundField DataField=”ProductModelID” HeaderText=”ProductModelID”

SortExpression=”ProductModelID” />

<asp:BoundField DataField=”SellStartDate” HeaderText=”SellStartDate”

SortExpression=”SellStartDate” />

<asp:BoundField DataField=”SellEndDate” HeaderText=”SellEndDate”

SortExpression=”SellEndDate” />

<asp:BoundField DataField=”DiscontinuedDate” HeaderText=”DiscontinuedDate”

SortExpression=”DiscontinuedDate” />

<asp:BoundField DataField=”rowguid” HeaderText=”rowguid”

SortExpression=”rowguid” />

<asp:BoundField DataField=”ModifiedDate” HeaderText=”ModifiedDate”

SortExpression=”ModifiedDate” />

</Columns>

</asp:GridView>

<asp:LinqDataSource ID=”ldsProducts” runat=”server”

ContextTypeName=”LinqToSQLDataBindingDAL.ProductionDataContext”

EnableDelete=”True” EnableUpdate=”True” TableName=”Products”>

</asp:LinqDataSource>

</div>

</form>

</body>

</html>

Listing 1

Now let’s do some cosmetic changes to display the data in a very end-user friendly way. So, we’re going to replace the definition of the ProductSubcategoryID field using <asp:BoundField> element (see Listing 2) with the definition of the underlying reference ProductSubcategory name field using <asp:TemplateField> element (see Listing 3).

<asp:BoundField DataField=”ProductSubcategoryID”

HeaderText=”ProductSubcategoryID” SortExpression=”ProductSubcategoryID” />

Listing 2

As you can see in the Listing 3, we’re taking advantage of the fact that the Product business entity type has a relationship to the ProductSubcategory business entity type in the Linq to SQL object model (see Figure 2), so for each Product entity it’s possible to navigate to the associated ProductSubcategory entity through this relationship.

<asp:TemplateField HeaderText=”Subcategory” SortExpression=”ProductSubcategoryID”>

<ItemTemplate>

<%#Eval(“ProductSubcategory.Name”) %>

</ItemTemplate>

</asp:TemplateField>

Listing 3

You can see the result in the Figure 10.

Figure 10

Now let’s add a drop-down list for the ProductSubcategory field in order to select the value while editing. First step is to add another <asp:LinqDataSource> control into the Web form. Then click on the smart tag of this control to configure the data source (see Figure 11).

Figure 11

The underlying ASP.NET code is shown in Listing 4.

<asp:LinqDataSource ID=”ldsProductSubcategories” runat=”server”

ContextTypeName=”LinqToSQLDataBindingDAL.ProductionDataContext”

TableName=”ProductSubcategories”>

</asp:LinqDataSource>

Listing 4

Now let’s go back to the <asp:Template> for the ProductSubcategory field and customize the edit appearance (see Listing 5).

<asp:TemplateField HeaderText=”Subcategory” SortExpression=”ProductSubcategoryID”>

<ItemTemplate>

<%#Eval(“ProductSubcategory.Name”) %>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID=”drlProductSubcategoriesInProductGridView” runat=”server”

DataSourceId=”ldsProductSubcategories” DataValueField=”ProductSubcategoryID”

DataTextField=”Name” SelectedValue='<%#Bind(“ProductSubcategoryID”)%>’>

</asp:DropDownList>

</EditItemTemplate>

</asp:TemplateField>

Listing 5

When you run the application, we can see the result as shown in Figure 12.

Figure 12

Let’s add filter drop-down list on the top of the Web form to display the products by its subcategories. On the Design view, drag and drop a dropdown list control from the Toolbox onto the Web form and then click on the smart tag to configure the data on it and select the “Choose Data Source” option. The setting is in the Data Source Configuration Wizard as shown in the Figure 13.

Figure 13

Next step is to go to the GridView control and click on the smart tag and select the “Configure Data Source” option in order to launch the “Configure Data Source” wizard. Go to the “Configure Data Selection” page and click on the Where button to add a binding filter to the linq data source control. We’re going to filter the Prouducts based on the ProductSubcategory field, and the value will be retrieved from the dropdown list (with a list of ProductSubcategory items) on the top of the Web form (see Figure 14).

Figure 14

And finally, in order to refresh the page when the users change the value in the ProductSubcategory drop-down list, we need to set the AutoPostBack property to True.

Conclusion

In this article, I’ve shown the data binding techniques applied to Linq to SQL and ASP.NET. Now you can adapt this solution to your own business problems.

Advertisements

8 thoughts on “Data binding with Linq to SQL and ASP.NET

  1. Pingback: MVP Factor

  2. Pingback: Data binding with Linq to SQL and ASP.NET - MSDN Blogs

  3. Same method give me one example
    display two tables data in a single Gridview And also perform Update delete operations,

    any methods plz help me the complete simple example

  4. You, your home entrepreneur, needs to be more creative and much
    more ingenious with regards to keeping up with the need for excellent customer service so that you will be
    able to fulfill the customer. If you might be using somebody else;
    s account, you will have to get a blank check from their store, their account number, and lastly their permission.

    They even provide countless hours of educational material regarding how to protect your
    cash in times of crisis.

  5. You, the property entrepreneur, needs to be more creative plus much
    more ingenious in relation to keeping up with the requirement for excellent customer service so you
    will be able to satisfy the customer. Countless hours of
    educational material and mentoring with the
    elite are standing in private meetings inside the worlds most
    exotic places. CPC is to get paid whenever someone follows one
    of your ads, CPM is when much you receive per 1,000 impressions and CPA is
    the place someone subscribes for the website your advertising or buys their service.

  6. This may be the primary issue I asked myself when I was researching the system.
    Just please remember Ad – Sense isn’t actually the one and only treatment for count on if you want to make money from home with Google. They even provide never ending hours of educational material regarding how to protect your cash in times of crisis.

  7. Normally I do not learn post on blogs, however I
    wish to say that this write-up very forced me to take a look at and do so!
    Your writing taste has been amazed me. Thank you, quite great article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s