Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Performing CRUD and Grouping Operations with the LinqDataSource Control  : Page 3

Build fully-editable Create, Read, Update, and Delete (CRUD) database front-ends easily and discover how to group and aggregate data using the new LinqDataSource control in ASP.NET.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Date: 1/31/2018 @ 2 p.m. ET

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#" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Using LinqDataSource Control to group and aggregate data</title> </head> <body> <form id="form1" runat="server"> <div> <asp:ListView DataSourceID="productsSource" ID="productsView" runat="server"> <LayoutTemplate> <table id="tblLayout" border="1" runat="server"> <thead> <tr> <th><b>Product Category</b></th> <th><b>Average List Price</b></th> <th><b>Record Count</b></th> </tr> </thead> <tr runat="server" id="itemPlaceholder" ></tr> </table> </LayoutTemplate> <ItemTemplate> <tr> <td><%# Eval("Key.Name") %></td> <td><%# Eval("AverageListPrice") %></td> <td><%# Eval("RecordCount") %></td> </tr> </ItemTemplate> </asp:ListView> <asp:LinqDataSource ID="productsSource" runat="server" ContextTypeName="AdventureWorksDatabaseDataContext" TableName="Products" Select="new(Key, Average(ListPrice) As AverageListPrice, Count() As RecordCount)" GroupBy="ProductSubcategory"> </asp:LinqDataSource> </div> </form> </body> </html>

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:

<td><%# Eval("Key.Name") %></td> <td><%# Eval("AverageListPrice") %></td> <td><%# Eval("RecordCount") %></td>

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.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date