n a previous DevX article, you saw the basic process for using the LinqDataSource control to retrieve and display data. That article discussed functionality such as passing parameters to a LINQ query and executing a stored procedure. This article builds on that foundation by discussing more sophisticated features for creating fully-editable pages, such as adding Create, Read, Update, and Delete (CRUD) capabilities, and handling CRUD events generated by the LinqDataSource control. In addition, this installment also demonstrates how to aggregate and group data using the LinqDataSource control.
CRUD with LinqDataSource
To begin, create a new Visual C# web site named LinqDataSourceExample. After creating the web site, select Website ? Add New Item from the menu. In the Add New Item dialog box, select the “LINQ to SQL Classes” template, and name it AdventureWorksDatabase.dbml. You’ll see the Object Relational Designer view of the newly created class. From that view, select View ? Server Explorer from the menu, and add a new data connection to the AdventureWorks database. Finally, to create the corresponding entity classes, drag and drop the Production.ProductCategory, Production.ProductSubcategory, and Production.Product tables onto the Object Relational Designer from the Tables node in Server Explorer.
The next step is to create an ASP.NET page that uses the LinqDataSource control to create CRUD capabilities on top of the Product table in the AdventureWorks database. Create a new ASP.NET page named CRUD_Example.aspx and modify its code as follows:
<%@ Page Language="C#" %> Using LinqDataSource Control for Create, Read, Update and Delete Operations The LinqDataSource control in the preceding code has three properties: EnableInsert, EnableUpdate, and EnableDelete. All three are set to true to enable automatic insert, update, and delete support. You need to enable the same functionality at the data-bound control, DetailsView in this case. To accomplish this, you set the AutoGenerateInsertButton, AutoGenerateEditButton, and AutoGenerateDeleteButton of the DetailsView control to true. That’s all you need to do to enable CRUD operations with the LinqDataSource control.
Figure 1 shows the output produced by navigating to the page in the browser.
|
|
If you click the Edit button in Figure 1, you’ll see the output shown in Figure 2.
When you click the Update button, the LinqDataSource control automatically retrieves the values from the text boxes and invokes the appropriate methods of the Entity class to update the data in the Product table.
Handling the Updating Event
So, it’s easy to set up a LinqDataSource control to update data automatically, but you probably want to pre-process the data before submitting it to the database. You can accomplish this by handling the LinqDataSource control’s Updating event. The code example shown below checks the user-entered data to ensure its validity:
<%@ Page Language="C#" %> Using LinqDataSource to handle Update event The code sets the LinqDataSource control’s OnUpdating attribute to the name of the event handler. This event handler (named productSource_Updating) gets a LinqDataSourceUpdateEventArgs argument that contains various details about the updating event, as shown in Table 1.
| Property | Description |
| Cancel | Allows you to cancel the update operation |
| Exception | Allows you to get the generated exception before the update operation |
| NewObject | Allows you to retrieve the data that will be saved in the data source |
| OriginalObject | Allows you to retrieve the data that was originally retrieved from the data source |
The preceding code typecasts the values of the OriginalObject and NewObject properties to the Product type. Next, it compares the values of the original and new object ProductID properties; if they differ, it cancels the update operation by setting the Cancel property to true. As the example shows, you have access to both the original and any changed data in this event before committing the changes to the database.
Handling the Deleted Event
In addition to pre-processing events such as Updating, you can also handle post-processing events with the LinqDataSource control. For example, you can display confirmation message to the user after successfully completing a Delete operation. The following page shows an example:
<%@ Page Language="C#" %> <%@ Import Namespace="System.Collections.Generic" %> Using LinqDataSource to handle Deleted event The preceding page code handles the LinqDataSource control’s Deleted event. As the name suggests, the Deleted event fires after the delete operation completes, so you could use this event to check whether the deletion was successful and/or perform cleanup activities. Again, the control receives data about the deleted object in a special argument type, the LinqDataSourceStatusEventArgs object. Table 2 summarizes its key properties.
| Property | Description |
| Exception | Allows you to get any exception generated during the operation |
| ExceptionHandled | Lets you indicate whether the exception was handled so that the exception is not thrown back to the client |
| Result | Allows you to get the object that represents the result of the operation |
The preceding code first checks to see if any exception occurred during the delete operation, and if so, displays the exception message.
if (e.Exception != null) lblMessage.Text = e.Exception.Message; Next, it typecasts the e.Result value to a ProductCategory object, and displays its ProductCategoryID value on the label control (see Figure 3).
ProductCategory category = (ProductCategory)e.Result; lblMessage.Text += "The Deleted Product category id is: " + category.ProductCategoryID;  |
? |
| Figure 3. Handling the Deleted Event: By capturing the Deleted event of the LinqDataSource control, you can provide status information to users or display any exception messages generated during the deletion |
Grouping and Aggregating Data
In addition to its extensible event-driven architecture, the LinqDataSource control also exposes attributes through which you can declaratively group and aggregate data. This section shows a simple example that groups all the products by category, and displays the category, the products’ average price, and the number of products in that category.
<%@ Page Language="C#" %> Using LinqDataSource Control to group and aggregate data The LinqDataSource control defined toward the end of the preceding code has a new attribute, named GroupBy, which is set to the ProductSubcategory object. Having set the GroupBy property, you can then retrieve the value using the Key property, which contains an object that exposes whatever value you use to group the data?in this case, the product subcategory name.
 |
? |
| Figure 4. Grouping and Aggregating Data: The LinqDataSource control lets you group and aggregate data by declaratively setting the GroupBy property. |
Using the ProductSubcategory value as the Key property, you can calculate the average list price of all products that share a ProductSubcategory value. The query also returns a count of the number of records associated with each ProductSubcategory value.
Select="new(Key, Average(ListPrice) As AverageListPrice, Count() As RecordCount)"Note that the aggregate functions have aliases so you can reference them from the data bound control. Here’s the snippet of code that references the previously-defined aggregate functions from the ListView control:
<%# Eval("Key.Name") %> <%# Eval("AverageListPrice") %> <%# Eval("RecordCount") %> Again, note that the Key.Name property contains the same value as the ProductSubcategory.Name property. Figure 4 shows the resulting page.
Using the advanced LinqDataSource control features you’ve seen here, you can easily create fully-functional editable data displays that use CRUD functionality to modify data. You can handle the CRUD events that the LinqDataSource control generates, and you can provide high-level displays such as dashboards by grouping and aggregating data. The LinqDataSource control’s extensible nature lets you customize the runtime behavior as much as you like, simplifying the process of creating rich data-driven web applications.
