Friday, August 24, 2012

Calculated Columns in .NET Data Tables (C#)

Recently I had a requirement to add Calculated Columns to Data Table. For an example I’m Only providing Amount and Quantity. Then discount will be calculated automatically.

String Discount = ".1";
DataTable workTable = new DataTable("Customers");

DataColumn workCol = workTable.Columns.Add("ID", typeof(Int32));
workTable.Columns.Add("UnitPrice", typeof(Double));
workTable.Columns.Add("Quantity", typeof(Int16));
workTable.Columns.Add("Total", typeof(Double));
workTable.Columns.Add("Discount", typeof(Double));

workTable.Columns["Total"].Expression = "UnitPrice*Quantity";
workTable.Columns["Discount"].Expression = String.Format("Total*{0}",Discount);
Later you can bind the Data Table to a Grid View if you need.
dataGridView1.DataSource = workTable;

Special Note:

But my requirement was to update the Column Expression dynamically in the runtime. For an example Discount will be change according to the total amount. Therefore this method can not cater that requirement since  expression is fixed with the discount.

Thus I update the Column Expression in   dataGridView1_DataBindingComplete and invalidate the control for force data panting.

Double RunningSum = 0;
String ValDiscount = "50";
workTable.Columns["ValueDis"].Expression = String.Format("{0}/{1}*Total", ValDiscount, RunningSum.ToString());

No comments: