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:
DataColumn fullName = new DataColumn(“FullName”, typeof(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:
string connectionString =WebConfigurationManager.ConnectionStrings[“SomeCompany”].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = “SELECT CategoryID, CategoryName FROM Categories”;
string sqlProd = “SELECT ProductName, CategoryID, UnitPrice FROM Products”;
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();
try
{
con.Open();
da.Fill(ds, “Categories”);
da.SelectCommand.CommandText = sqlProd;
da.Fill(ds, “Products”);
}
finally
{
con.Close();
}
// Define the relationship between Categories and Products.
DataRelation dtrel = 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.
DataColumn count = new DataColumn(“Products (#)”, typeof(int), “COUNT(Child(CatProds).CategoryID)”);
DataColumn max = new DataColumn(“Most Expensive Product”, typeof(decimal), “MAX(Child(CatProds).UnitPrice)”);
DataColumn min = new DataColumn(“Least Expensive Product”, typeof(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();