Search code examples
c#sqldatabasewinformsdatagridview

Save Changes in DataGridView C#

After calling the table from the database into the dataGridView and entering data into the empty cells of this DataGridView, I want to save this data to my database by clicking on the button, but I don’t know how to do this

This is how I access db:

public MySqlConnection mycon;
 public MySqlCommand mycom;
 public string connect = "Server=localhost;Database=base;Uid=root;password=pas;charset=utf8";
 public SD.DataSet ds;

 mycon = new MySqlConnection(connect);
 mycon.Open();

 MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
 SD.DataTable table = new SD.DataTable();
 ms_data.Fill(table);
 dataGridView1.DataSource = table;
 mycon.Close();
Solution

  • Pains me to write it, but this should be the minimum set of calls necessary:

    MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
new MySqlCommandBuilder(ms_data);
ms_data.Update(dataGridView1.DataSource as DataTable);

    You need an adapter, and then passing it to a command builder should make the CB read the select command loaded into the adapter and use it to populate the adapter's other commands (insert/update/delete), then you can call Update and pass a modified table and the rows in the table will be examined to see if they're new, modified or deleted and the appropriate command will be called to persist the changes

    script needs to be an SQL that selects the primary key columns from the DB

    Why "pains to say it" ? Because it's such a long winded, hard work way of doing database access compared to even the technology that replaced it, let alone something modern like EF. If you're liking working with datatables, consider:

    • Add a new DataSet type of file to the project
    • Open it, right click the surface, add a tableadapter
    • Configure the connection string
    • Configure a "select that returns rows" query like SELECT * FROM sometable WHERE id = @someId
    • A tableadapter and datatable pair appear, that mimick the table in the database
    • Switch to a form, open the data sources window (View Menu, Other Windows), drag the table to the form - a datagridview, dataset, tableadapter, navigator etc appear.
    • Everything is wired up to go.

    Your tableadapter is a dataadapter on steroids, and will Fill/Update the datatable from/to database. The datatable has named columns and is generally much nicer to work with. All the code for loading and saving is already written into the FormX.cs so you can take a look, but it basically amounts to:

    var x = new BlahBlahTableAdapter();
x.FillBy(this.BlahBlahDataSet, someIdTextBox.Text);

    Filling the datatable that is binded to the grid will cause the data to appear in the grid automatically.

    Saving is similarly simple:

    var x = new BlahBlahTableAdapter();
x.Update(this.BlahBlahDataSet);

    Footnote: I believe you need MySQL Tools For Visual Studio installed for this to play nice. Also, you might encounter issues if you made a NetCore/Net5+ WinForms project because some elements of new .NET don't support winforms properly

    Footnote2: See https://bugs.mysql.com/bug.php?id=99199 if you're wondering why the tableadapter wizard option "Refresh the dataset" is not working

    If you want to try something more modern, there are lots of good EF tutorials out there. EF is a tool that uses a database to create objects in your code. The data for the objects lives in the database and is automatically transported back and forth from/to objects/database by EF. If you get into it, EF Core Power Tools is a very useful VS addin to have