LINQ to SQL (Part 5 - Binding UI using the ASP:LinqDataSource Control)
Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.
Below are the first four parts of my LINQ to SQL series:
- Part 1: Introduction to LINQ to SQL
- Part 2: Defining our Data Model Classes
- Part 3: Querying our Database
- Part 4: Updating our Database
In these previous LINQ to SQL blog posts I focused on how you can programmatically use LINQ to SQL to easily query and update data within a database.
In today's blog post I'll cover the new <asp:LinqDataSource> control that is shipping as part of ASP.NET in the upcoming .NET 3.5 release. This control is a new datasource control for ASP.NET (like the ObjectDataSource and SQLDataSource controls that shipped with ASP.NET 2.0) which makes declaratively binding ASP.NET UI controls to LINQ to SQL data models super easy.
Sample Application We'll be Building
The simple data editing web application I'll walkthrough building in this tutorial is a basic data entry/manipulation front-end for products within a database:
The application will support the following end-user features:
- Allow users to filter the products by category
- Allow users to sort the product listing by clicking on a column header (Name, Price, Units In Stock, etc)
- Allow users to skip/page over multiple product listings (10 products per page)
- Allow users to edit and update any of the product details in-line on the page
- Allow users to delete products from the list
The web application will be implemented with a clean object-oriented data model built using the LINQ to SQL ORM.
All of the business rules and business validation logic will be implemented in our data model tier - and not within the UI tier or in any of the UI pages. This will ensure that: 1) a consistent set of business rules are used everywhere within the application, 2) we write less code and don't repeat ourselves, and 3) we can easily modify/adapt our business rules at a later date and not have to update them in dozens of different places across our application.
We will also take advantage of the built-in paging/sorting support within LINQ to SQL to ensure that features like the product listing paging/sorting are performed not in the middle-tier, but rather in the database (meaning only 10 products are retrieved from the database at any given time - we are not retrieving thousands of rows and doing the sorting/paging within the web-server).
What is the <asp:LinqDataSource> control and how does it help?
The <asp:LinqDataSource> control is an ASP.NET control that implements the DataSourceControl pattern introduced with ASP.NET 2.0. It is similar to the ObjectDataSource and SqlDataSource controls in that it can be used to declaratively bind other ASP.NET controls on a page to a datasource. Where it differs is that instead of binding directly to a database (like the SqlDataSource) or to a generic class (like the ObjectDataSource), the <asp:linqdatasource> is designed to bind against a LINQ enabled data model.
One of the benefits of using the <asp:linqdatasource> control is that it leverages the flexibility that LINQ based ORMs provide. You don't need to define custom query/insert/update/delete methods for the datasource to call - instead you can point the <asp:linqdatasource> control at your data model, identify what entity table you want it to work against, and then bind any ASP.NET UI control against the <asp:linqdatasource> and have them work with it.
For example, to get a basic product listing UI on my page that works against Product entities within a LINQ to SQL data model, I could simply declare a <asp:linqdatasource> on my page that points to my LINQ to SQL datacontext class, and identify the entities (for example: Products) in the LINQ to SQL data model I want to bind against. I could then point a GridView at it (by settings its DataSourceID property) to get a grid-like view of the Product content:
Without having to-do anything else, I can run the page and have a listing of my Product data with built-in support for paging and sorting over the data. I can add a edit/delete button on the Grid and automatically have update support as well. I don't need to add any methods, map any parameters, or write any code for the <asp:LinqDataSource> to handle both these querying and updating scenarios - it can work against the LINQ to SQL data model we point it against and do these operations automatically. When updates are made, the LINQ to SQL ORM will automatically ensure that all business rules and validation logic we've added (as partial methods) to the LINQ to SQL data model pass before persisting anything to the database.
Important: The beauty of LINQ and LINQ to SQL is that it obviously isn't tied to being used only in UI scenarios - or with particular UI binding controls like the LinqDataSource. As you've seen in my previous posts in this series, writing code using the LINQ to SQL ORM is extremely clean. You can always write custom UI code to directly work against your LINQ to SQL data model if you prefer, or when you find a UI scenario that isn't particularly suited to using the <asp:linqdatasource>.
The below sections walkthrough using LINQ to SQL and the <asp:LinqDataSource> control to build the web application scenario I defined above.
Step 1: Define our Data Model
We'll begin working on the application by first defining the data model we'll use to represent our database.
I discussed how to create a LINQ to SQL data model using VS 2008's LINQ to SQL designer in Part 2 of this series. Below is a screenshot of the data model classes I can quickly create using the LINQ to SQL designer to model the "Northwind" sample database:
We'll revisit our data model in Step 5 of this tutorial below when we add some business validation rules to it. But to begin with we'll just use the above data model as-is to build our UI.
Step 2: Creating a Basic Product Listing
We'll start our UI by creating an ASP.NET page with a <asp:gridview> control on it and use some CSS to style it:
We could write code to programmatically bind our data model to the GridView (like I did in Part 3 of this series), or alternatively I could use the new <asp:linqdatasource> control to bind the GridView to our data model.
VS 2008 includes build-in designer support to make it easy to connect up our GridView (or any other ASP.NET server control) to LINQ data. To bind our grid above to the data model we created earlier, we can switch into design-view, select the GridView, and then select the "New Data Source..." option within the "Choose Data Source:" drop-down:
This will bring up a dialog box that lists the available datasource options to create. Select the new "LINQ" option in the dialog box and name the resulting <asp:linqdatasource> control you want to create:
The <asp:linqdatasource> designer will then display the available LINQ to SQL DataContext classes that your application can use (including those in class libraries that you are referencing):
We'll want to select the data model we created with the LINQ to SQL designer earlier. We'll then want to select the table within our data model that we want to be the primary entity for the <asp:linqdatasource> to bind against. For this scenario we'll want to select the "Products" entity class we built. We'll also want to select the "Advanced" button and enable updates and deletes for the datasource:
When we click the "Finish" button above, VS 2008 will declare a <asp:linqdatasource> within our .aspx page, and update the <asp:gridview> to point to it (via its DataSourceID property). It will also automatically provide column declarations in the Grid based on the schema of the Product entity we choose to bind against:
We can then pull up the "smart task" context UI of the GridView and indicate that we want to enable paging, sorting, editing and deleting on it:
We can then press F5 to run our application, and have a product listing page with full paging and sorting support (note the paging indexes at the bottom of the grid below):
We can also select the "edit" or "delete" button on each row to update the data:
If we flip into source view on the page, we'll see that the markup of the page contains the content below. The <asp:linqdatasource> control points at the LINQ to SQL DataContext we created earlier, as well as the entity table we want to bind against. The GridView then points at the <asp:linqdatasource> control (via its DataSourceID) and indicates which columns should be included in the grid, what their header text should be, as well as what sort expression to use when the column header is selected.
Now that we have the basics of our web UI working against our LINQ to SQL data-model, we can go ahead and further customize the UI and behavior.
Step 3: Cleaning up our Columns
Our GridView above has a lot of columns defined within it, and two of the column values (the SupplierID and the CategoryID) are currently foreign-key numbers -- which certainly isn't the ideal way to represent them to an end-user.
Removing Unnecessary Columns
We can start cleaning up our UI by deleting a few of the columns we don't need. I can do this in source mode (simply nuke the <asp:boundfield> declarations) or in designer mode (just click on the column in the designer and choose the "Remove" task). For example, we could remove the "QuantityPerUnit" column below and re-run our application to get this slightly cleaner UI:
If you have used the <asp:ObjectDataSource> control before and explicitly passed update parameters to update methods (the default when using DataSet based TableAdapters) one of the things you know can be painful is that you have to change the method signatures of your TableAdapter's update methods when the parameters based by your UI are modified. For example: if we deleted a column in our grid (like above), we'd end up having to modify our TableAdapter to support update methods without that parameter.
One of the really nice things about the <asp:LinqDataSource> control is that you do not need to-do these types of changes. Simply delete (or add) a column from your UI and re-run the application - no other changes are required. This makes changing web UI built using the <asp:LinqDataSource> much easier, and enables much faster scenarios iterations within an application.
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:
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:
And now when I run the application I get the human readable Category and Supplier name values instead:
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:
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:
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:
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:
When I run the page I'll now get a filter dropdownlist of all categories at the top of the page:
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:
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):
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:
When we hit finish, the <asp:linqdatasource> control in our page will have been updated to reflect this filter clause like so:
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:
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:
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.
Adding a Data Model Validation Rule
The wrong place to add this type of business validation rule is in the UI layer of our application. Adding it in the UI layer of our application will mean that the rule will be specific to only that one place, and will not be automatically enforced when we add another page to our application that also updates Products. Distributing business rules/logic in our UI layer will also make life extremely painful as our application grows in size - since changes/updates to our business will necessitate making code changes all over the place.
The right place to specify this type of business logic validation is instead in our LINQ to SQL data model classes that we defined earlier. As I discussed in Part 4 of this series, all classes generated by the LINQ to SQL designer are defined as "partial" classes - which means that we can easily add additional methods/events/properties to them. The LINQ to SQL data model classes automatically call validation methods that we can implement to enforce custom validation logic within them.
For example, I could add a partial Product class to my project that implements the OnValidate() partial method that LINQ to SQL calls prior to persisting a Product entity. Within this OnValidate() method I could add the following business rule to enforce that products can't have a Reorder Level if the product is discontinued:
Once I add the above class into my LINQ to SQL project, the above business rule will be enforced anytime anyone uses my data model to try and modify the database. This is true for both updating existing Products, as well as adding new Products into the database.
Because the <asp:LinqDataSource> that we defined in our pages above works against our LINQ to SQL data model classes, all of its update/insert/delete logic will now have to pass the above validation check prior to the change being persisted. We do not need to-do anything to our UI tier in order for this validation to occur - it will automatically be applied anywhere and everywhere our LINQ to SQL data model is used.
Adding Nice Error Handling in our UI Tier
By default if a user now uses our GridView UI to enter a non-valid UnitsOnOrder/Discontinued combination, our LINQ to SQL data model classes will raise an exception. The <asp:LinqDataSource> will in turn catch this error and provides an event that users can use to handle it. If no one handles the event then the GridView (or other) control bound to the <asp:LinqDataSource> will catch the error and provide an event for users to handle it. If no one handles the error there then it will be passed up to the Page to handle, and if not there to the global Application_Error() event handler in the Global.asax file. Developers can choose any place along this path to insert appropriate error handling logic to provide the right end-user experience.
For the application we defined above, probably the best place to handle any update errors is by handling the RowUpdated event on our GridView. This event will get fired every time an update is attempted on our datasource, and we can access the exception error details if the update event fails. We can add the below code to check if an error occurs, and if so display an appropriate error message to the end-user:
Notice above how we have not added any validation specific logic into our UI. Instead, I am retrieving the validation error message string we raised in our business logic and am using it to display an appropriate message to the end-user (I am then displaying a more generic error message in the event of other failures).
Notice how I'm also indicating above that I want the GridView to stay in Edit mode when an error occurs - that way the user can avoid losing their changes, and can modify the values they entered and click "update" again to try and save them. We can then add a <asp:literal> control with the "ErrorMessage" ID anywhere we want on our page to control where where we want the error message to be displayed:
And now when we try and update the Product with an invalid value combination we'll see an error message indicating how to fix it:
The nice thing about using this approach is that I can now add or change my data model's business rules and not have to modify any of my UI tier's code to have them pick up and honor the changes. The validation rules, and corresponding error messages, can be written and centralized in one place in my data model and automatically applied everywhere.
Summary
The <asp:LinqDataSource> control provides an easy way to bind any ASP.NET UI control to a LINQ to SQL data model. It enables UI controls to both retrieve data from a LINQ to SQL data model, as well as cleanly apply updates/inserts/deletes to it.
In our application above we used the LINQ to SQL ORM designer to create a clean, object oriented data model. We then added three ASP.NET UI controls to our page (a GridView, a DropDownList, and a ErrorMessage Literal), and added three <asp:LinqDataSource> controls to bind Product, Category, and Supplier data from it:
We then wrote 5 lines of business validation logic in our data model, and 11 lines of UI error handling logic.
The end result is a simple web application with custom UI that allows users to dynamically filter product data by category, efficiently sort and page over the product results, inline-edit the product data to save updates (providing our business rules pass), and delete products from the system (also providing our business rules allow it).
In future posts in this series I'll cover more LINQ to SQL scenarios including optimistic concurrency, lazy and eager loading, table mapping inheritance, and custom SQL/SPROC usage.
Next week I also plan to start a new series of blog posts that cover the new <asp:ListView> control - which is a new control that will ship with the ASP.NET release in .NET 3.5. It provides total control over the markup generated for data scenarios (no tables, no spans, no inline styles...), while also delivering built-in support for paging, sorting, editing, and insertion scenarios. For example, we could optionally use it to replace the default Grid layout look of our application above with a completely custom look and feel. Best of all, I could replace it within the above page in my application and not have to change my Data Model, the <asp:linqdatasource> declaration, or my code-behind UI error handling logic at all.
Hope this helps,
Scott