Calculated columns are used when you want to represent a value that’s computed using a combination of existing values. You can create a calculated column, by following the next steps:
1. Create DataColumn object by specifying its name and type.
2. Set the Expression property.
3. Add the DataColumn to the Columns collection of the DataTable by using Add() method.
The next example shows a column that uses string concatenation to combine the first and last name into one field:
Dim fullName As New DataColumn(“FullName”, GetType(String), “TitleOfCourtesy + ‘ ‘ + LastName + ‘, ‘ + FirstName”)
ds.Tables(“Employees”).Columns.Add(fullName)
You can create a calculated column that combines information from related rows. For example, you want to add a column in a Categories table which indicates the number of related product rows. In this case, you need to:
1. Define the relationship with a DataRelation object.
2. Use a SQL aggregate function such as AVG(), MAX(), MIN(), or COUNT().
The next code lines illustrate how you can create three calculated columns, all of which use aggregate functions and table relationships:
Dim connectionString As String = WebConfigurationManager.ConnectionStrings(“SomeCompany”).ConnectionString
Dim con As New SqlConnection(connectionString)
Dim sqlCat As String = “SELECT CategoryID, CategoryName FROM Categories”
Dim sqlProd As String = “SELECT ProductName, CategoryID, UnitPrice FROM Products”
Dim da As New SqlDataAdapter(sqlCat, con)
Dim ds As New DataSet()
Try
con.Open()
da.Fill(ds, “Categories”)
da.SelectCommand.CommandText = sqlProd
da.Fill(ds, “Products”)
Finally
con.Close()
End Try
‘ Define the relationship between Categories and Products.
Dim dtrel As New DataRelation(“CatProds”, ds.Tables(“Categories”).Columns(“CategoryID”), ds.Tables(“Products”).Columns(“CategoryID”))
‘ Add the relationship to the DataSet.
ds.Relations.Add(dtrel)
‘ Create the calculated columns.
Dim count As New DataColumn(“Products (#)”, GetType(Integer), “COUNT(Child(CatProds).CategoryID)”)
Dim max As New DataColumn(“Most Expensive Product”, GetType(Decimal), “MAX(Child(CatProds).UnitPrice)”)
Dim min As New DataColumn(“Least Expensive Product”, GetType(Decimal), “MIN(Child(CatProds).UnitPrice)”)
‘ Add the columns.
ds.Tables(“Categories”).Columns.Add(count)
ds.Tables(“Categories”).Columns.Add(max)
ds.Tables(“Categories”).Columns.Add(min)
‘ Show the data.
grid1.DataSource = ds.Tables(“Categories”)
grid1.DataBind()