1

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.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
Urknecht
  • 415
  • 10
  • 16
  • I dont think u can do this without joining B and C. If you join them into data.Tables["tableB"] then you are there, or am I missing something ? – GuidoG Sep 14 '15 at 15:10
  • I'm using SqlDataAdapter to select the data and when they are matched I wanted to update the table. I don't think this would be possible anymore if I would go with the joining approach. Also I'm not sure if this is a good practice if B and C are huge, producing an even bigger joined table. – Urknecht Sep 15 '15 at 07:24
  • you can set your own updatestatement for your adapter so updating a joined table is no problem. I usually create a view in sql server to join the tables. When selecting i then use "select * from myView" and when updating I use "select * from myTable" for the command. Works perfect for me. – GuidoG Sep 15 '15 at 07:49
  • Didn't know you could use custom statements with the adapter, I'll give that a try. Thank you sir for your time and help! – Urknecht Sep 15 '15 at 08:06
  • @GuidoG could you give me an example please of your update command when selecting from the view and updating the table B? Do I have to create the update command manually for each row I'm editing or can I still work with data.tables["eng"].GetChanges()? – Urknecht Sep 15 '15 at 08:36

1 Answers1

0

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);
            }
        }
    }
}
GuidoG
  • 11,359
  • 6
  • 44
  • 79