Maulik Dhorajia: Apply group by clause on Datatable in C#

Wednesday, August 18, 2010

Apply group by clause on Datatable in C#

The blog will explain how to retrieve a data table using C# using a group by clause.

//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");
/// <summary>
/// The group by clause for Data Table
/// </summary>
/// <param name="dt">The main data table to be filtered with the Group By.</param>
/// <param name="columnNamesInDt">The Fields sent seperated by commas. EG "Field1,Field2,Field3"</param>
/// <param name="groupByColumnNames">The column name which should be used for group by.</param>
/// <param name="typeOfCalculation">The calculation type like Sum, Avg Or Count.</param>
/// <returns></returns>
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;
}