bind more than one dataGridView to MySQL database

I have MySQL database with four tables, and I've written form an example binding method. But this solution works well only with one table. If I bind more than one, dataGridViews will be filled with info, but Update and Delete commands work badly.

 public void Bind(DataGridView dataGridView, string tableName)
    {
        string query = "SELECT * FROM " + tableName;

        mySqlDataAdapter = new MySqlDataAdapter(query, conn);
        mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter);

        mySqlDataAdapter.UpdateCommand = mySqlCommandBuilder.GetUpdateCommand();
        mySqlDataAdapter.DeleteCommand = mySqlCommandBuilder.GetDeleteCommand();
        mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand();

        dataTable = new DataTable();
        mySqlDataAdapter.Fill(dataTable);

        bindingSource = new BindingSource();
        bindingSource.DataSource = dataTable;

        dataGridView.DataSource = bindingSource;
    }

Should I use different mySqlDataAdapter or mySqlCommandBuilder for each new table? I've used different DataTable and BindingSource objects, but when I inserted new row in one table, I had an exception that I left empty field in other table. Any solutions or tips for this problem?

Thanks in advance!开发者_JAVA技巧

Better late than never I guess...

I have an application that loads different tables into the same DataGridView using Visual Studio 2013. So far it is working!

1. DataTable

You certainly need to create a new one for each different table you want to load, otherwise you can not clear out the old data. You might think that

dataTable.Clear()

would do the trick but no, it leaves the old column headers behind so your new table is loaded to the right of all the old columns :-(. Although interestingly if your new table has a column with the same name as the old it merges them!

2. MySqlAdapter

I currently create a new one for each table, but at the very least your sql query is changing so you need to create a new SelectCommand:

MySqlCommand cmd = new MySqlCommand("SELECT * FROM `" + tableName + "`", conn);
sqlAdapter.SelectCommand = cmd;

I've tried this and it seems to work, but actually it is simpler to just create a new MySqlAdapter and performance really isn't an issue at this point!

3. SqlCommandBuilder

Yes you should create a new one because the update and delete commands will be different. I don't use a class variable but create one dynamically (i.e. as a local variable) when I need it.

4. BindingSource

I don't believe you need a new BindingSource, but I haven't used them very much so can't be certain.