CODEWITHSUNDEEP
c#mysqlwinforms
PandaNL
PandaNL

Reputation: 848

Updating datatable back to mysql

In my application I have a button that will use a select command to fill my datatable, this datatable is sourcebind to a datagridview.

What I want to achieve is the following, I want a second "Update" button to write back the changes made in the datagrid view (row edit, row deletion), I found some info on MySqlCommandBuilder but couldn't make it work MySqlCommandBuilder

How can I make this work, so users can update the datatable and write it back to mysql.

Upvotes: 0

Views: 1530

Answers (1)

Steve
Steve

Reputation: 216273

When you have populated initially your datatable you have used an instance of an object called MySqlDataAdapter with code that could be like this 

string query = "SELECT * FROM yourTableName";
MySqlDataAdapter adapter = new MySqlDataAdapter(query, connString);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
DataTable dt = new DataTable();
adapter.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgview.DataSource = bs;

now, you change the datagrid modifying, adding or deleting rows, and every change you make to the datagrid is reflected on the underlying data source (the datatable), If you want to update your records on the database you need to keep that MySqlDataAdapter around (as a global variable inside your form) and just call the Update method of the adapter instance when you need it

BindingSource bs = dgview.DataSource As BindingSource;
adapter.Update(bs.DataSource as DataTable);

This call will use the instances of the MySqlCommand class created by the MySqlCommandBuilder to update the database (Keep in mind that MySqlCommandBuilder works only on select query that involves only a single table - no join allowed)

Upvotes: 2

Related Questions