C#

Grouping and sum with Datatable Using IEnumerable

Use for Groupping and sum with datatble

Pass Datatable as dttemp

var query = from r in dttemp.AsEnumerable()
group r by new { StyleDetail_COLOR_NAME = r.Field<string>(“StyleDetail_COLOR_NAME”), StyleDetail_QLY_NAME = r.Field<string>(“StyleDetail_QLY_NAME”), StyleDetail_SHAPENAME = r.Field<string>(“StyleDetail_SHAPENAME”), StyleDetail_SHAPENO = r.Field<int>(“StyleDetail_SHAPENO”), StyleDetail_QLY_ID = r.Field<int>(“StyleDetail_QLY_ID”), StyleDetail_COLOR_ID = r.Field<int>(“StyleDetail_COLOR_ID”) }
into groupedTable
select new
{
StyleDetail_SHAPENO = groupedTable.Key.StyleDetail_SHAPENO,
StyleDetail_QLY_ID = groupedTable.Key.StyleDetail_QLY_ID,
StyleDetail_COLOR_ID = groupedTable.Key.StyleDetail_COLOR_ID,
StyleDetail_SHAPENAME = groupedTable.Key.StyleDetail_SHAPENAME,
StyleDetail_QLY_NAME = groupedTable.Key.StyleDetail_QLY_NAME,
StyleDetail_COLOR_NAME = groupedTable.Key.StyleDetail_COLOR_NAME,
StyleDetail_WT = groupedTable.Sum(s => s.Field<double>(“StyleDetail_WT”)),
StyleDetail_PSC = groupedTable.Sum(s => s.Field<int>(“StyleDetail_PSC”))
};
dtgroupshape = ConvertToDataTable(query);

dtgroupshape is output of datatable with grouping and sum

public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
{
// Use reflection to get property names, to create table, Only first time, others will follow
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.