GROUP BY DataTable Method - A SQLOps method

The Expressions that can be formulated for a calculated column in a DataTable are a very handy thing. The problem is the aggregation operators only work for a parent-child relationship.
This feature really stood out when I started working with the MSDE DBMS.

The MSDE engine is a wonderful little animal, except for the built in work governor.
Throttling back all queries when the governor is activated is the price you pay for a free DBMS.
There are illegal ways to get around this, but why bother when you can do it on the client.

Thanks to the SQLOps class I blogged about earlier, this processing is surprisingly simple.
I really needed a single table aggregation method to shift the burden from MSDE
All that was missing from this class was a decent GROUPBY method.

Limitations & possible improvements:

  • The initial PROJECT method is a relational project. That is, no duplicates are returned. If you haven't updated the method I original gave ( and I warned you here) then you must substitute it with a DISTINCT call.
  • The Grouping columns are returned as part of the table heading.
  • The length of the AggreationExpressions, ExpressionNames and Type arrays must match.
  • The Type array must match the Aggregation result type returned.
  • Only valid aggregation expressions as outlined in the ADO.NET help file are allowed. IE COUNT(*) is invalid...
  • I am only posting the final overloaded method. All other signatures are left to you..
  • Resolving the data type of the aggregation expression could be automated.
  • I don't know what happens when a NULL appears..
How it works:
  • Get a projection table of the grouping columns (distinct)
  • Add columns for the aggregation results.
  • Start looping through the projected table.
  • For each row build the filter (by determining the data type) for the grouping columns.
  • For each aggregation expression call the COMPUTE method of the Initial table and update the projected tables column.
Here is an example of it in action using the Northwind database. I hope this clarifies the arguments for you
The orders table is every row from the Orders table in northwind
On my box, in debug mode, this expression took about 40ms to evaluate

English: Calculate the number of orders for each customer
SQL: Select CustomerID, COUNT(*) AS Total from Orders group by CustomerID
SQLOps: SQLOps.GROUPBY(orders,new DataColumn[]{orders["CustomerID"]},new string[]{"COUNT(CustomerID)"},new string[]{"Total"}, new Type[]{typeof(int)})

The following code is an extension of the SQLOps class to handle single table aggregations.

public static DataTable GROUPBY(DataTable Table, DataColumn[] Grouping, string[] AggregateExpressions, string[] ExpressionNames, Type[] Types)

{

      if(Table.Rows.Count == 0)

            return Table;

      DataTable table = SQLOps.PROJECT(Table,Grouping);

      table.TableName = "GROUPBY";

 

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

      {

            table.Columns.Add(ExpressionNames[i],Types[i]);

      }

 

      foreach(DataRow row in table.Rows)

      {

            string filter = string.Empty;

 

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

            {

                  //Determine Data Type        

                  string columnname = Grouping[i].ColumnName;

                  object o = row[columnname];

                  if(o is string || DBNull.Value == o)

                  {

                        filter += columnname + "='" + o.ToString() + "' AND ";

                  }

                  else if(o is DateTime)

                  {

                        filter += columnname + "=#" + ((DateTime)o).ToLongDateString()

                              + " " + ((DateTime)o).ToLongTimeString() + "# AND ";

                  }

                  else

                        filter += columnname + "=" + o.ToString() + " AND ";

            }                

            filter = filter.Substring(0,filter.Length - 5);

 

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

            {

                  object computed = Table.Compute(AggregateExpressions[i],filter);

                  row[ExpressionNames[i]] = computed;

            }          

      }

      return table;

}

 

EDIT: I give up trying to format this thing properly!
Print | posted on Thursday, May 20, 2004 1:29 PM

Feedback

# Take Outs for 19 May 2004

left by Enjoy Every Sandwich at 5/20/2004 1:44 AM Gravatar
Take Outs for 19 May 2004

# re: GROUP BY DataTable Method - A SQLOps method

left by Ramesh at 9/13/2007 8:17 PM Gravatar
SQLOps Class not found in .net.. Which namespace i have to include....?

# re: GROUP BY DataTable Method - A SQLOps method

left by JulienV at 2/14/2008 3:46 AM Gravatar
Thank you for this function, it's exactly what I need. Good work !

# re: GROUP BY DataTable Method - A SQLOps method

left by Shrini at 5/26/2008 10:15 PM Gravatar
SQLOps Class not found in .net.. Could u say the namespace of SQLOps class?
Comments have been closed on this topic.