Thursday, September 18, 2008

Run aggregate on datatable column

I had one table with columns "Name" and "Department" which i need to display in a multiline textbox. like below
Name~~~~~~~~ Dept
Tarun Ghosh~~~SWD
Diptangshu Das~SSWD
So that the names and Dept are aligned properly. So i needed the Max of the string length of the column "Name" and give a right-padding to name to make all of same length with the maximum lengthed name.
Firstly i went with the primitive looping through each record and calculate the max of the row, if it is greater than the global max, set global max to current max algo...
But i was not satisfied with the approach even though no problem.
Then i got the
public Object Compute(
string expression,
string filter
)
function of DataTable. Expression par needs to be some aggregate function lik Sum, Avg,Min,Max,Count,Var. with filter expression is same as where clause in dt.Select() function.
But one problemmmmmmmm.... The expression parameter can't work on computed column. Like say you have two column qty and unit price. You need to get the max(qty*unitPrice), then first you have to add a new column to hold the value of the multiplied result and then use the compute function on that column.
So in my case i wanted to get the max of length. So i added a column as

DataColumn col = new DataColumn("Length");
col.Expression = "Len(Name)";
col.DataType = typeof(int);
dt.Columns.Add(col);
object obj= dt.Compute("Max(Length)","");
Response.Write(obj.ToString());

obj is storing the max length value
Some useful link
Link1
Link2

No comments: