Ken Cox
Posts 170
|
Using LINQ to Add a Total in a GridView Footer
It's a common requirement to display a column's total in an ASP.NET GridView control's footer. There are many ways to accomplish this, but the simplest way (and the least code!) is to use LINQ and the Aggregate keyword. You need a simple database, a LINQ to SQL class, and a small Extension method.
Creating the Fruits database
1. In Solution Explorer, add a SQL Express database named Fruits.mdf to your project. (File > New File > SQL Server Database).
2. In Database/Server Explorer, add a table named SalesStats to the database. (Refer to the JulieDVD project starting at Page 38 for help with this.)
3. In Database/Server Explorer, add the following columns (and types) to the SalesStats table:
id as an int data type with (Is Identity) set to Yes.
Product as a varchar(50)
Sales as an int
Price as money.
4. In Database/Server Explorer, add some data to the table (Data > Show Table Data) such as you see in the following table:
1 Apple 100 200.0000
2 Orange 200 400.0000
3 Grapes 50 75.0000
You now have a database table and data but nowhere to put it. That's next.
Creating the Data Context with LINQ to SQL
1. In Solution Explorer, add a LINQ to SQL Classes file named FruitDataClasses.dbml to your project. (File > New File > LINQ to SQL Classes). The O/R designer page appears.
2. In Database/Server Explorer, drag the SaleStats table and drop it on the left-hand pane. This creates a SaleStat object.
3. Save your work. (File > Save All).
This turns the database table and columns into objects that the LINQ DataSource can use.
Hooking up with the LINQDataSource control
1. In Solution Explorer, add a new Web Form names fruitsales.aspx to your project.
2. In Design view, from the Toolbox, drag a LinqDataSource control and drop it on the fruitsales.aspx page.
3. From the LinqDataSource1 Tasks button, click Configure Data Source.
4. Accept the default settings and then click Next.
5. Click Finish to exit the wizard.
The LinqDataSource control is now pointing to the data context you created previously.
Adding and Configuring the Gridview Control
1. In Design view, drag a GridView control from the Toolbox and drop it onto the fruitsales.aspx page.
2. From the GridView1 Tasks menu, select LinqDataSource1 as the data source.
3. From the GridView1 Tasks menu, click Edit Columns.
4. In the Fields dialogue box, in the Selected fields area (lower left) select Sales.
5. Just above the OK button, click the hyperlink for Convert This Field into a TemplateField and then click OK.
6. From the GridView1 Tasks menu, click Edit Templates.
7. From the dropdown list, select Column [2] Sales.
8. From the Toolbox, drag a Label control and drop it in the FooterTemplate area of the column.
9. Using the Properties page (F4) set the Label control's id to lblTotal.
Using an Extension Method to Calculate the Total Sales
1. In Solution Explorer, add a Text file to the project and name it util.vb.
2. Add the following code as the complete content of util.vb
Imports Microsoft.VisualBasic
Imports System.Runtime.CompilerServices
Public Module Extns
<Extension()> _
Function CalculateTotal(ByVal s As String) As String
Dim dc As New FruitDataClassesDataContext()
Return (Aggregate d In dc.SaleStats Into Sum(d.Sales)).ToString()
End Function
End Module
Telling the GridView to Display the Total
1. In fruitsales.aspx, select GridView1 and, using its Properties page (F4), set the ShowFooter property to True.
2. Switch to Source view.
3. Locate the Label control named lblTotal and set its Text value to '<%# CalculateTotal("") %>' as shown in the following code:
<FooterTemplate>
<asp:Label ID="lblTotal" runat="server" Text='<%# CalculateTotal("") %>'></asp:Label>
</FooterTemplate>
When you run the page, the GridView binds automatically to LinqDataSource1 to fill the columns. When it's time to display the footer, the CalculateTotal() extension method goes into action.
For more about extension methods, see Page 138 of ASP.NET 3.5 For Dummies
For examples of the LINQ Aggregate functions, see the back side of the Cheat Sheet inside the front cover.
|