Recently I had a requirement to add Calculated Columns to a DataTable. For 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 DataTable
to a GridView if you need.
dataGridView1.DataSource = workTable;
dataGridView1.Update();
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());
dataGridView1.Invalidate();