I have MS Access database with related tables. In the studio, using standard tools added a new DataSource and Dataset (one class (read one DataTable) - one table).

Suppose, for example, I have 2 tables: Table1 and Table2. 1 of the Table1 columns is the ID in Table2. Now in DataSet.Table1.Row there are properties ID, other columns , Table2Row. In turn, Table2Row is a class corresponding to 1 row of Table2.

Now I want to bring the Table1 table to the DataGrid, but so that instead of the Table2ID column I display some columns from the corresponding Table2 row.

Now I have copied the DataTable of the first table, added the necessary columns there and in the foreach loop I wrote down the necessary values ​​from the corresponding row of the Table2 table:

var fullTable = _dataSet.Ord; fullTable.Columns.Add("CopyName"); fullTable.Columns.Add("ExecutorName"); fullTable.Columns.Add("OrdererName"); foreach (var row in fullTable.AsEnumerable()) { if (row.CopyRow != null) row["CopyName"] = row.CopyRow.Title; if (row.ExecutorRow != null) row["ExecutorName"] = row.ExecutorRow.Name; if (row.OrdererRow != null) row["OrdererName"] = row.OrdererRow.Name; }

The question is: is it possible to do without unnecessary copying? Or how to do it easier and less expensive.

  • using WinForms - Alexey Sarovsky
  • stackoverflow.com/a/665846/5796587 make a preliminary Join of two DataTable as in the example by reference and give the result in DataGreedView - rdorn

2 answers 2

First, make Inner Join two DataTables.
Original on EnSO https://stackoverflow.com/a/665846/5796587

 DataTable dt1 = new DataTable(); dt1.Columns.Add("CustID", typeof(int)); dt1.Columns.Add("ColX", typeof(int)); dt1.Columns.Add("ColY", typeof(int)); DataTable dt2 = new DataTable(); dt2.Columns.Add("CustID", typeof(int)); dt2.Columns.Add("ColZ", typeof(int)); for (int i = 1; i <= 5; i++) { DataRow row = dt1.NewRow(); row["CustID"] = i; row["ColX"] = 10 + i; row["ColY"] = 20 + i; dt1.Rows.Add(row); row = dt2.NewRow(); row["CustID"] = i; row["ColZ"] = 30 + i; dt2.Rows.Add(row); } var results = from table1 in dt1.AsEnumerable() join table2 in dt2.AsEnumerable() on (int)table1["CustID"] equals (int)table2["CustID"] select new { CustID = (int)table1["CustID"], ColX = (int)table1["ColX"], ColY = (int)table1["ColY"], ColZ = (int)table2["ColZ"] };

Then everything is simple:

 dataGridView1.DataSource = results.ToList();

Conversion to the List mandatory, since DataGridView cannot display IEnumerable

Naturally, editing this data in the grid will not change the state of the data in the tables and can be turned off altogether. For editing, you will have to open the source tables.

MSDN examples for different join on LiNQ

    The simplest and less expensive is to request the merged data by a request from the DBMS.

    • I thought about it. But ... 1) Imagine the situation that the data now need to be stored in XML, for example. 2) I would like to make the most of the auto-generated adapter code when editing (Update in particular) - Alexey Sarovsky
    • Alternatively, you can fill in a DataGridView yourself with handles from 2 DataTables, then write event handlers and update everything in the DataTable itself. Something else does not come to mind. - iluxa1810