I have 2 datagridviews on a winform (C#) that show about the same data, where the left one is grouped by customer and the right one shows (all) the rows. I use a datatable as the datasource and filling that one is done by selecting data from MSSQL.

Because the data is the same, I would like to re-use the datatable and try to do the grouping via LINQ on the left datagridview. It all works as expected, except for one extra column I like to add on the left datagridview, that has a sum of the rows per customer. Normally I would do this in the SQL statement, but since I need all the rows in the datatable, I have to find another way.

This is what I have so far.

private void load_dgv_grouped()
{
    DataTable dtRecord_head = new DataTable();
    dtRecord_head.Clear();
    conn.ConnectionString = GlobalVar.fCurrentSQLCon;

    conn.Open();
    string qry = "SELECT * FROM xxx.._f_so_select(@nm)";
    SqlCommand command = new SqlCommand(qry, conn);
    command.Parameters.AddWithValue("@nm", tb_so_search_cust.Text);
    sqlDataAdap = new SqlDataAdapter(command);
    commandBuilder = new SqlCommandBuilder(sqlDataAdap);
    conn.Close();

    sqlDataAdap.Fill(dtRecord_head);
    //this is the grouped datatable
    DataTable dt = new DataTable();

    dt = dtRecord_head.AsEnumerable()
           .GroupBy(r => new { Col1 = r["CustNr"], Col2 = r["CustNm"] })

           // needed for the order by
           .Select(g => g.OrderBy(r => r["CustNm"]).First())
           .OrderBy(d => d["CustNm"])
           .CopyToDataTable()

           // select only certain columns for this dt
           .DefaultView.ToTable(false, "CustNr", "CustNm"); 

    // here the actual datasource is set for the datagridview,
    // as well as some other stuff that I do on all datagridviews
    m.set_datagridview(dgv_so_head, dt, 1, this);

    // load the data into the second datagridview
    load_dgv_lines(dtRecord_head);
}

The original datatable has a column named NrOfRows and I would like to add a field showing the sum per CustNr. I have searched a lot on how to add a calculated (sum) field for the column, but could not find a solution online that I could use in my case. So any help would be appreciated.

0 Answers