The only catch was it was working only with String column and it was not grouping for numeric column and i fixed that.
Here is the microsoft link.
Implement a DataSet GROUP BY Helper Class in Visual C# .NET
Here is the Dataset Helper class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
namespace DataSetHelper
{
///
/// Summary description for DataSetHelper
///
public class DataSetHelper
{
public DataSet ds;
private System.Collections.ArrayList m_FieldInfo; private string m_FieldList;
private System.Collections.ArrayList GroupByFieldInfo; private string GroupByFieldList;
public DataSetHelper(ref DataSet DataSet)
{
ds = DataSet;
}
public DataSetHelper()
{
ds = null;
}
private void ParseFieldList(string FieldList, bool AllowRelation)
{
/*
* This code parses FieldList into FieldInfo objects and then
* adds them to the m_FieldInfo private member
*
* FieldList systax: [relationname.]fieldname[ alias], ...
*/
if (m_FieldList == FieldList) return;
m_FieldInfo = new System.Collections.ArrayList();
m_FieldList = FieldList;
FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
int i;
for (i = 0; i <= Fields.Length - 1; i++)
{
Field = new FieldInfo();
//parse FieldAlias
FieldParts = Fields[i].Trim().Split(' ');
switch (FieldParts.Length)
{
case 1:
//to be set at the end of the loop
break;
case 2:
Field.FieldAlias = FieldParts[1];
break;
default:
throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");
}
//parse FieldName and RelationName
FieldParts = FieldParts[0].Split('.');
switch (FieldParts.Length)
{
case 1:
Field.FieldName = FieldParts[0];
break;
case 2:
if (AllowRelation == false)
throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'.");
Field.RelationName = FieldParts[0].Trim();
Field.FieldName = FieldParts[1].Trim();
break;
default:
throw new Exception("Invalid field definition: " + Fields[i] + "'.");
}
if (Field.FieldAlias == null)
Field.FieldAlias = Field.FieldName;
m_FieldInfo.Add(Field);
}
}
private void ParseGroupByFieldList(string FieldList)
{
/*
* Parses FieldList into FieldInfo objects and adds them to the GroupByFieldInfo private member
*
* FieldList syntax: fieldname[ alias]|operatorname(fieldname)[ alias],...
*
* Supported Operators: count,sum,max,min,first,last
*/
if (GroupByFieldList == FieldList) return;
GroupByFieldInfo = new System.Collections.ArrayList();
FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
for (int i = 0; i <= Fields.Length - 1; i++)
{
Field = new FieldInfo();
//Parse FieldAlias
FieldParts = Fields[i].Trim().Split(' ');
switch (FieldParts.Length)
{
case 1:
//to be set at the end of the loop
break;
case 2:
Field.FieldAlias = FieldParts[1];
break;
default:
throw new ArgumentException("Too many spaces in field definition: '" + Fields[i] + "'.");
}
//Parse FieldName and Aggregate
FieldParts = FieldParts[0].Split('(');
switch (FieldParts.Length)
{
case 1:
Field.FieldName = FieldParts[0];
break;
case 2:
Field.Aggregate = FieldParts[0].Trim().ToLower(); //we're doing a case-sensitive comparison later
Field.FieldName = FieldParts[1].Trim(' ', ')');
break;
default:
throw new ArgumentException("Invalid field definition: '" + Fields[i] + "'.");
}
if (Field.FieldAlias == null)
{
if (Field.Aggregate == null)
Field.FieldAlias = Field.FieldName;
else
Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;
}
GroupByFieldInfo.Add(Field);
}
GroupByFieldList = FieldList;
}
public DataTable CreateGroupByTable(string TableName, DataTable SourceTable, string FieldList)
{
/*
* Creates a table based on aggregates of fields of another table
*
* RowFilter affects rows before GroupBy operation. No "Having" support
* though this can be emulated by subsequent filtering of the table that results
*
* FieldList syntax: fieldname[ alias]|aggregatefunction(fieldname)[ alias], ...
*/
if (FieldList == null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//return CreateTable(TableName, SourceTable);
}
else
{
DataTable dt = new DataTable(TableName);
ParseGroupByFieldList(FieldList);
foreach (FieldInfo Field in GroupByFieldInfo)
{
DataColumn dc = SourceTable.Columns[Field.FieldName];
if (Field.Aggregate == null)
dt.Columns.Add(Field.FieldAlias, dc.DataType, dc.Expression);
else
dt.Columns.Add(Field.FieldAlias, dc.DataType);
}
if (ds != null)
ds.Tables.Add(dt);
return dt;
}
}
public void InsertGroupByInto(DataTable DestTable, DataTable SourceTable, string FieldList,
string RowFilter, string GroupBy)
{
/*
* Copies the selected rows and columns from SourceTable and inserts them into DestTable
* FieldList has same format as CreateGroupByTable
*/
if (FieldList == null)
throw new ArgumentException("You must specify at least one field in the field list.");
ParseGroupByFieldList(FieldList); //parse field list
ParseFieldList(GroupBy, false); //parse field names to Group By into an arraylist
DataRow[] Rows = SourceTable.Select(RowFilter, GroupBy);
DataRow LastSourceRow = null, DestRow = null; bool SameRow; int RowCount = 0;
foreach (DataRow SourceRow in Rows)
{
SameRow = false;
if (LastSourceRow != null)
{
SameRow = true;
foreach (FieldInfo Field in m_FieldInfo)
{
if (!ColumnEqual(LastSourceRow[Field.FieldName], SourceRow[Field.FieldName]))
{
SameRow = false;
break;
}
}
if (!SameRow)
DestTable.Rows.Add(DestRow);
}
if (!SameRow)
{
DestRow = DestTable.NewRow();
RowCount = 0;
}
RowCount += 1;
foreach (FieldInfo Field in GroupByFieldInfo)
{
switch (Field.Aggregate) //this test is case-sensitive
{
case null: //implicit last
case "": //implicit last
case "last":
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
break;
case "first":
if (RowCount == 1)
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
break;
case "count":
DestRow[Field.FieldAlias] = RowCount;
break;
case "sum":
DestRow[Field.FieldAlias] = Add(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
case "max":
DestRow[Field.FieldAlias] = Max(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
case "min":
if (RowCount == 1)
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
else
DestRow[Field.FieldAlias] = Min(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
}
}
LastSourceRow = SourceRow;
}
if (DestRow != null)
DestTable.Rows.Add(DestRow);
}
private FieldInfo LocateFieldInfoByName(System.Collections.ArrayList FieldList, string Name)
{
//Looks up a FieldInfo record based on FieldName
foreach (FieldInfo Field in FieldList)
{
if (Field.FieldName == Name)
return Field;
}
return null;
}
private bool ColumnEqual(object a, object b)
{
/*
* Compares two values to see if they are equal. Also compares DBNULL.Value.
*
* Note: If your DataTable contains object fields, you must extend this
* function to handle them in a meaningful way if you intend to group on them.
*/
if ((a is DBNull) && (b is DBNull))
return true; //both are null
if ((a is DBNull) || (b is DBNull))
return false; //only one is null
return (a.Equals(b)); //value type standard comparison
}
private object Min(object a, object b)
{
//Returns MIN of two values - DBNull is less than all others
if ((a is DBNull) || (b is DBNull))
return DBNull.Value;
if (((IComparable)a).CompareTo(b) == -1)
return a;
else
return b;
}
private object Max(object a, object b)
{
//Returns Max of two values - DBNull is less than all others
if (a is DBNull)
return b;
if (b is DBNull)
return a;
if (((IComparable)a).CompareTo(b) == 1)
return a;
else
return b;
}
private object Add(object a, object b)
{
//Adds two values - if one is DBNull, then returns the other
if (a is DBNull)
return b;
if (b is DBNull)
return a;
return ((decimal)a + (decimal)b);
}
public DataTable SelectGroupByInto(string TableName, DataTable SourceTable, string FieldList,
string RowFilter, string GroupBy)
{
/*
* Selects data from one DataTable to another and performs various aggregate functions
* along the way. See InsertGroupByInto and ParseGroupByFieldList for supported aggregate functions.
*/
DataTable dt = CreateGroupByTable(TableName, SourceTable, FieldList);
InsertGroupByInto(dt, SourceTable, FieldList, RowFilter, GroupBy);
return dt;
}
private class FieldInfo
{
public string RelationName;
public string FieldName; //source table field name
public string FieldAlias; //destination table field name
public string Aggregate;
}
}
}
Here is the sample page to test the above class,
Create a simple aspx page with 3 grid views and use the following code behind given below,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using PrintDashboard;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataSetHelper dsHelper = new DataSetHelper(ref ds);
//Create the source table
DataTable dt = new DataTable("Orders");
dt.Columns.Add("EmployeeID", Type.GetType("System.String"));
dt.Columns.Add("OrderID", Type.GetType("System.Int32"));
dt.Columns.Add("Amount", Type.GetType("System.Decimal"));
dt.Rows.Add(new object[] {"Sam", 5, 25.00});
dt.Rows.Add(new object[] {"Tom", 7, 50.00});
dt.Rows.Add(new object[] {"Sue", 9, 11.00});
dt.Rows.Add(new object[] {"Tom", 12, 7.00});
dt.Rows.Add(new object[] {"Sam", 14, 512.00});
dt.Rows.Add(new object[] {"Sue", 15, 17.00});
dt.Rows.Add(new object[] {"Sue", 22, 2.50});
dt.Rows.Add(new object[] {"Tom", 24, 3.00});
dt.Rows.Add(new object[] {"Tom", 33, 78.75});
ds.Tables.Add(dt);
this.GridView1.DataSource = ds;
dsHelper.CreateGroupByTable("OrderSummary", ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,Sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder");
dsHelper.InsertGroupByInto(ds.Tables["OrderSummary"], ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder",
"", "EmployeeID");
this.GridView2.DataSource= dt1;
this.GridView2.DataBind();
this.GridView1.DataBind();
DataTable dt2;
dt2 = dsHelper.SelectGroupByInto("OrderSummary2", ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder",
"OrderID>10", "EmployeeID");
this.GridView3.DataSource = dt2;
this.GridView3.DataBind();
}
}
This sounds like a bit of overkill for a simple group-by with aggregates for a data table. I've used approach at http://codecorner.galanter.net/?p=161 and it seems to work pretty good. Or are there any flaws in it?
ReplyDeleteThe blog was absolutely fantastic! Lots of great information and
ReplyDeleteinspiration, both of which we all need!b Keep 'em coming... you all do
such a great job at such Concepts... can't tell you how much I, for
one appreciate all you do!