Include a Calculated Column in a DataGrid...

Date: January 28, 2003

In this article you will learn how to include a calculated column in a DataGrid. A calculated column generally contains some value calculated from one or more columns of data that are included in the data store. It could be based on multiplying or dividing a column value by some other value, or it could be based on two or more columns of data in the data store. In this article we will take the latter approach.

We will use the Order Details table from the Northwind database. The columns included are OrderID, ProductID, UnitPrice, and Quantity. Our calculated column will be the result of multiplying UnitPrice times Quantity to arrive at a "Total Cost" of each order.

First, the aspx page. Here we create and set properties for our DataGrid. The only thing to really note is that we have added a BoundColumn for our TotalCost column. We can't actually create a calculated column at design time. As you will see in the code-behind file, we add the calculated column dynamically at run time. But here in the aspx page we must at least "make room" for the calculated column.
<%@ Page Language="vb" Src="CalculatedColumn.aspx.vb" Inherits="CalculatedColumn" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>CalculatedColumn</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="dtgOrderDetails" runat="server"
              AutoGenerateColumns="False"
              BackColor="White" ForeColor="Black"
              CellSpacing="2" CellPadding="4"
              GridLines="Both">
  <Columns>
    <asp:BoundColumn DataField="OrderId" HeaderText="Order ID">
    </asp:BoundColumn>
    <asp:BoundColumn DataField="ProductID" HeaderText="Product ID">
    </asp:BoundColumn>
    <asp:BoundColumn DataField="UnitPrice"
                     HeaderText="Unit Price"
                     DataFormatString="{0:c}">
    </asp:BoundColumn>
    <asp:BoundColumn DataField="Quantity" HeaderText="Quantity">
    </asp:BoundColumn>
    <asp:BoundColumn DataField="TotalCost"
                     HeaderText="Total Cost"
                     ItemStyle-HorizontalAlign="Right"
                     DataFormatString="{0:c}">
    </asp:BoundColumn>
  </Columns>
  <HeaderStyle font-bold="True"
               forecolor="White"
               backcolor="#4A3C8C">
  </HeaderStyle>
  <ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:DataGrid>
</form>
</body>
</html>
Now for the code-behind file. Most of this file is just the usual database access code to get the data and fill a dataset. Near the bottom of the file, however, you will see where we compute and add our calculated column. First we create a new column with the line calcCol = New DataColumn(...). We then set the Expression property of the calculated column with the line: calcCol.Expression = "UnitPrice * Quantity". Lastly we add the column to the DataSet with the line: ds.Tables("OrderDetails").Columns.Add(calcCol). We then bind the DataGrid and we are done!
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Configuration

Public Class CalculatedColumn : Inherits Page

  Protected dtgOrderDetails As DataGrid

  Private Sub Page_Load(sender As Object, e As EventArgs)
    Dim ds As DataSet
    Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim calcCol As DataColumn
    Dim objCmd As New SqlCommand("SELECT * " _
      & "FROM [Order Details] " _
      & "WHERE ProductID=5", objConn)
    objCmd.CommandType = CommandType.Text
    Dim dataAdapter As SqlDataAdapter
    ds = New DataSet()

    dataAdapter = New SqlDataAdapter(objCmd)
    dataAdapter.Fill(ds, "OrderDetails")

    'Add the calculated column
    calcCol = New DataColumn("TotalCost", GetType(Double))
    calcCol.Expression = "UnitPrice * Quantity"
    ds.Tables("OrderDetails").Columns.Add(calcCol)

    dtgOrderDetails.DataSource = ds
    dtgOrderDetails.DataBind()
  End Sub

End Class
I hope it is now clear how easy it is to add a calculated column to a DataGrid control.
You may Download the code here.