2

I've a DataTable[having 5 columns] from db which has combined data. I need to invoke a group by on this combined table and create 2 tables...one with groupedBy rows and other having items. What would be fastest way to do this in C# code? Also, I've written code below for adding columns for these 2 tables.Is that correct?

Heres my code:

string colName = "ACCOUNT_ID"; 
var allRows = combinedTable.AsEnumerable();      

var accountRowGroups = allRows.GroupBy(row => row[colName]);

DataTable masterDataTable = new DataTable(); 
DataTable childPricesDataTable = new DataTable();

// Create the columns 
            DataColumnCollection pdCols = combinedTable.Columns; 
            for (int ndx = 0; ndx < pdCols.Count; ndx++) 
            { 
                string columnName = pdCols[ndx].ColumnName; 
                Type type = Type.GetType(pdCols[ndx].DataType.ToString());

                masterDataTable.Columns.Add(columnName, type); 
                childPricesDataTable.Columns.Add(columnName, type);

            }
Improve this question

2 Answers 2

Reset to default
4
+25

See a similar question here: How to merge multiple DataTable objects with group by with C# and concatinating duplicate rows?

I agree with duffymo, do it in SQL rather than data in-memory.

However if that isn't an option:

You can add a relationship between the two DataTables: http://msdn.microsoft.com/en-us/library/ay82azad(v=vs.71).aspx

Then you can run group by's on the combined datatables: http://codecorner.galanter.net/2009/04/20/group-by-and-aggregates-in-net-datatable/

Here is a proper example from the SQL Team: http://weblogs.sqlteam.com/davidm/archive/2004/05/20/1351.aspx

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;

        }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks jeremy for ur inputs.Would like to mention that am already using a DataRelation object to bind the 2 tables[ie. masterDataTable and childPricesDataTable] to an infragistics grid, but the whole thing is very slow.Hence am looking for an alternate faster way in C#.Also, when u say I can sql instead, do u mean i shud return 2 tables from my sql rather than 1 combined table and then show them in the grid using DataRelation object?
Also,@duffymo talks about combining 2 tables in sql and returning what is required...If I need to use sql, then as u can see in my comment above, I will have to send 2 tables[master & child] instead of 1 and then show them in the grid using DataRelation object...Would u still suggest me to use sql then?
Hi @Jimmy, since you have to bind two grids "one with groupedBy rows and other having items" - I would create a stored procedure and return two SELECT's, one Group By and the other without group by. Then I would use the DataReader.NextResult() method described here "Retrieving Multiple Result Sets using NextResult" msdn.microsoft.com/en-us/library/haa3afyz.aspx to bind both grids with only a single hit to the database. The whole thing will be much faster when the JOIN operation is performed in SQL especially when your database grows. HTH
2

It's not direct answer to your question but maybe alternative (I believe better) way to solve your problem.

If I understand correctly you have DataTable, some defined manipulations/filters and want return modfied DataTable as result. But in practice the result DataTable hasn't known shape (it may be different depending columns in filter).

Consider using linq instead of manipulating DataTables and DataRelations. Linq gives support for all mentioned by you operations: grouping, filtering, trimming and many more.

As the result you may return object which is bindable also, so you may use it in your grid, but the method definition will be much cleaner.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.