Sunday, 12 May 2013

datatable group by



private DataTable GetGroupedBy(DataTable dt, string columnNamesInDt, string groupByColumnNames, string typeOfCalculation)

{

//Return its own if the column names are empty

if (columnNamesInDt == string.Empty || groupByColumnNames == string.Empty)

{

return dt;

}




//Once the columns are added find the distinct rows and group it bu the numbet

DataTable _dt = dt.DefaultView.ToTable(true, groupByColumnNames);




//The column names in data table

string[] _columnNamesInDt = columnNamesInDt.Split(',');




for (int i = 0; i < _columnNamesInDt.Length; i = i + 1)

{

if (_columnNamesInDt[i] != groupByColumnNames)

{

_dt.Columns.Add(_columnNamesInDt[i]);

}

}







//Gets the collection and send it back

for (int i = 0; i < _dt.Rows.Count; i = i + 1)

{

for (int j = 0; j < _columnNamesInDt.Length; j = j + 1)

{

if (_columnNamesInDt[j] != groupByColumnNames)

{

_dt.Rows[i][j] = dt.Compute(typeOfCalculation + "(" + _columnNamesInDt[j] + ")", groupByColumnNames + " = '" + _dt.Rows[i][groupByColumnNames].ToString() + "'");

}

}

}




return _dt;

}



-----------------------------------



how to use

//The way to call the Group By Function //Argument 1 = Data table in IMAGE 1 //Argument 2 = The fields you want for the data table returned //Argument 3 = The field you want to group by //Argument 4 = The function you want to do It can be either SUM, COUNT, AVG etc. DataTable dtGroupedBy = GetGroupedBy(dt, "CodeName,Quantity,Current", "CodeName", "Sum");


No comments:

Post a Comment

Note: only a member of this blog may post a comment.