Computed / Calculated Columns in an ASP.NET Datagrid

Populating a .NET DataGrid with data retrieved using a DataSet or a DataReader is very simple, but what if we need to display a computed column? That’s where the Expression feature of a DataColumn comes in ADO.NET. An Expression allows you to create virtual in-memory columns where content is obtained as the result of an expression applied to other existing columns.

In this example we’re going to retrieve data from SQL Server's pubs database, and create a column containing the authors advance divided by the price of the book. Not numerically significant, but it is a working example written in C#...

private void Page_Load(object sender, System.EventArgs e)

{
    if(!IsPostBack)
    {
        SqlConnection sqlConnection = 
                          new SqlConnection("your connection string");
        sqlConnection.Open();

        SqlCommand sqlCommand =

                  new SqlCommand("SELECT * FROM Titles",
                                 sqlConnection);
        

        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
        da.Fill(ds);

        DataColumn myColumn =

               new DataColumn("AdvanceDivPrice",
                              Type.GetType("System.Double"));

        myColumn.Expression = "advance / price";

        ds.Tables[0].Columns.Add(myColumn);

        DataGrid1.DataSource = ds;
        DataGrid1.DataBind();
    }   
}

Of course the expression should check for division by zero, which isn't a problem with pub's title prices, but to be safe, the following would generate a null value instead of throwing an exception:

      myColumn.Expression = "advance / IIF(price=0, NULL, price)";