CODEWITHSUNDEEP
c#.netlinqdatatable
Jimmy
Jimmy

Reputation: 2106

Creating datatables

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);

            }

Upvotes: 2

Views: 561

Answers (2)

Slawomir Pasko
Slawomir Pasko

Reputation: 907

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.

Upvotes: 2

Jeremy Thompson
Jeremy Thompson

Reputation: 65732

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;

        }

Upvotes: 4

Related Questions