Master Detail Relation based on Information from 3rd Table

Question

To start things off I'm trying to learn about DataGridView/BindingSource/DataRelation etc. I have allready read some tutorials and gathered information about the topic. So far I think I understood the basics and now I'm trying to experiment with the stuff I have learned.

So far I'm playing with the code from this tutorial: https://msdn.microsoft.com/en-us/library/c12c1kx4%28v=vs.110%29.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

In my project there are 3 tables :

Table A    
    A_id    eng_word
    0         dog
    1         cat

Table B
    B_id    ger_word
    0        Hund
    1        Katze
    2        Maus

Table C (Relation)
    A_id    B_id
    0       0
    0       1
    1       1
    1       2

My goal is to have to DataGridViews for each Table A and Table B with BindingSource and DataRelations, so that when I click on an entry from DataGridView A all the elements from Table B are displayed which could possibly be a translation according to Table C.

        DataRelation relation = new DataRelation("Relation",
            data.Tables["tableA"].Columns["A_id"],
               data.Tables["tableB"].Columns["B_id"]);
        data.Relations.Add(relation);

        bindingSourceA.DataSource = data;
        bindingSourceA.DataMember = "tableA";

        bindingSourceB.DataSource = bindingSourceA;
        bindingSourceB.DataMember = "Relation";

This obviously isn't working without having to call a join on Table B an Table C, but i thought it might be possible to do with DataRelation and BindingSource. The relation from Table A to Table C is not the problem but ongoing to Table B seems impossible for me.

Is there any approch to achieve my goal or it this way just simply wrong? Any advice or pointers in the right direction would be gladly appreciated.

Answer 1

As requested an example of how to influence the commandbuilder, so it builds an update to myTable in stead of to myView or joined tables.
The variable Table is the actual DataTable that has been filled with "select * from myView" or "select B.field1, C.field2 from B join C on ..."
Make sure that every field in myTable is present in myView

using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
    connection.Open();
    using (SqlDataAdapter adapter = new SqlDataAdapter())
    {
        using (SqlCommand command = new SqlCommand())
        {
            using (SqlCommandBuilder builder = new SqlCommandBuilder())
            {
                adapter.SelectCommand = command;// Command;
                adapter.SelectCommand.Connection = connection;
                builder.DataAdapter = adapter;

                // here I let the command builder think that the table is myTable in stead of myView
                adapter.SelectCommand.CommandText = "select * from myTable";
                adapter.UpdateCommand = builder.GetUpdateCommand(true).Clone();
                adapter.DeleteCommand = builder.GetDeleteCommand(true).Clone();

                adapter.Update(Table);
            }
        }
    }
}