DataTable: Query a programmatically generated DataTable

[Sumit Kathuria]

I have programmatically generated a DataTable in ASP.NET 3.5 and now I want to use group by and do some calculations using SQL on this DataTable. My question is: Is it possible to write a new SQL query against this DataTable and generate a new updated DataTable? For eg: select ID,sum(Rate) from dataTable group by ID

Sumit Kathuria

[David Mujica]

I've never seen it done. You could push the datatable up to the SQL server in a #TEMP table, then do your SQL from there. Not necessarily the best way to do things, but it would work.

[mr priyank]

You can use Linq Or Lamda Expression to form such query

DataTable dt = new DataTable();

        dt.Columns.Add("ID");
        dt.Columns.Add("Rate");
        dt.Rows.Add("1", "10");
        dt.Rows.Add("1", "20");
        dt.Rows.Add("2", "30");
        dt.Rows.Add("2", "40");

        var result = dt.AsEnumerable().GroupBy(row => row\["ID"\]).Select(GroupedRows => new { ID = GroupedRows.Key, SumOfRate = GroupedRows.Sum(row => Convert.ToDecimal(row\["Rate"\])) }).ToList();