LINQ to SQL (Part 5 - Binding UI using the ASP:LinqDataSource Control)
page 4 of 8
by Scott Guthrie
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 51323/ 101

Cleaning up the SupplierID and CategoryID Columns

Currently we are displaying the foreign-key integer values in our GridView for the Supplier and Category of each Product: 

Figure 15

While accurate from a data model perspective, it isn't very end-user friendly.  What I really want to-do is to display the CategoryName and SupplierName instead, and provide a drop-downlist while in Edit mode to enable end-users to easily associate the SupplierID and CategoryID values.

I can change the GridView to display the Supplier Name and Category Name instead of the ID's by replacing the default <asp:BoundField> in our GridView with an <asp:TemplateField>.  Within this TemplateField I can add any content I want to customize the look of the column. 

In the source code below I'm going to take advantage of the fact that each Product class in the LINQ to SQL data model we created has a Supplier and Category property on it. What this means is that I can easily databind their Supplier.CompanyName and Category.CategoryName sub-properties within our Grid:

Figure 16

And now when I run the application I get the human readable Category and Supplier name values instead:

Figure 17

To get drop-down list UI for the Supplier and Category columns while in Edit-Mode in the Grid, I will first add two additional <asp:LinqDataSource> controls to my page.  I will configure these to bind against the Categories and Suppliers within the LINQ to SQL data model we created earlier:

Figure 18

I can then go back to the <asp:TemplateField> columns we added to our GridView earlier and customize their edit appearance (by specifying an EditItemTemplate).  We'll customize each column to have a dropdownlist control when in edit mode, where the available values in the dropdownlists are pulled from the categories and suppliers datasource controls above, and where we two-way databind the selected value to the Product's SupplierID and CategoryID foreign keys:

Figure 19

And now when end-users click edit in the GridView, they are presented a drop-down list of all valid Supplier's to associate the product with:

Figure 20

And when they hit save the Product is updated appropriately (the GridView will use the DropDownList's currently selected value to bind the SupplierID).

Step 4: Filtering our Product Listing

Rather than show all products within the database, we can update our UI to include a dropdownlist that allows the user to filter the products by a particular category. 

Because we already added a <asp:LinqDataSource> control to the page earlier that references our Categories within our LINQ to SQL data model, all I need to-do to create a drop-downlist control at the top of the page that binds against this.  For example:

Figure 21

When I run the page I'll now get a filter dropdownlist of all categories at the top of the page:

Figure 22

My last step is to configure the GridView to only show those Products in the category the end-user selects from the dropdownlist.  The easiest way to-do this is by selecting the "Configure DataSource" option in the GridView smart task:

Figure 23

This will bring me back to the <asp:LinqDataSource> control's design-time UI that we used at the very beginning of this tutorial.  I can select the "Where" button within this to add a binding filter to the datasource control.  I can add any number of filter expressions, and declaratively pull the values to filter by from a variety of places (for example: from the querystring, from form-values, from other controls on the page, etc):

Figure 24

Above I'm going to choose to filter by the Products by their CategoryID value, and then retrieve this CategoryID from the DropDownList control we just created on our page:

Figure 25

When we hit finish, the <asp:linqdatasource> control in our page will have been updated to reflect this filter clause like so:

Figure 26

And when we now run the page the end-user will now be able to select from the available Categories in the filter drop-downlist and page, sort, edit and delete just the products in that category:

Figure 27

The <asp:LinqDataSource> control will automatically apply the appropriate LINQ filter expression when working against our LINQ to SQL data model classes to ensure that only the required data is retrieved from the database (for example: in the Grid above only the 3 rows of Product data from the second page of Confection products will be retrieved from the database).

You can optionally handle the Selecting event on the <asp:LinqDataSource> if you want to write a custom LINQ expression in code to completely customize the query instead.

Step 5: Adding Business Validation Rules

As I discussed in Part 4 of this LINQ to SQL series, when we define LINQ to SQL data models we will automatically have a default set of schema based validation constraints added to our data model classes.  This means that if I try and enter a null value for a required column, try and assign a string to an integer, or assign a foreign-key value to a row that doesn't exist, our LINQ to SQL data model will raise an error and ensure that our database integrity is maintained.

Basic schema validation is only a first step, though, and is rarely enough for most real-world applications.  Typically we'll want/need to add additional business rules and application-level validation to our data model classes.  Thankfully LINQ to SQL makes adding these types of business validation rules easy (for details on the various validation approaches available, please read Part 4 of my LINQ to SQL series).

Example Business Validation Rule Scenario

For example, let's consider a basic business logic rule we might want to enforce.  Specifically, we want to ensure that a user of our application can't discontinue a product while we still have units on backorder for it:

Figure 28

If a user tries to save the above row, we'll want to prevent this change from being persisted and throw an appropriate error telling the user how to fix it.

View Entire Article

User Comments

Title: Add new   
Name: Aditya
Date: 2010-05-29 1:38:06 AM
Hi boss this is really nice.
Title: Add column on selecting   
Name: Peter
Date: 2010-04-18 7:19:36 PM
Can you actually add column on the linq data source while selecting?
Title: Nice   
Name: vijay
Date: 2009-03-02 5:52:16 AM
Nice detailed article.
Title: Adding new item   
Name: Evans
Date: 2009-02-25 1:28:39 PM
Can we add new item directly with linq?
Title: Cleaning Up SupplierID and CategoryID   
Name: brian
Date: 2008-10-12 4:03:05 PM
If the associated category or supplier no longer exists, you will get a NullReferenceException if you attempt to sort on the associated column in the GridView. Is there some way to handle this?

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-05-23 2:04:18 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search