How to apply changes done in my DataGridView back to the DataTable than to the Database? RRS feed

  • Question

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; //using System.Data.SqlClient; using MySql.Data.MySqlClient; namespace GSK.DBLayer { class Database { //Declarations MySqlConnection conn; MySqlCommand cmd; MySqlDataAdapter da; MySqlCommandBuilder cb; DataTable DT; int pCount; string connectionString = "SERVER=localhost; DATABASE=gsk; UID=root;"; //string connectionString = "Data Source=localhost; Initial Catalog=GSK; Integrated Security=True"; //Execute Queries public void AccessDB(string sqlQuery) { try { conn = new MySqlConnection(connectionString); cmd = new MySqlCommand(sqlQuery, conn); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); MessageBox.Show("Your request served successfully!", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (MySqlException ex) { MessageBox.Show("Unable to serve your request!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } //Search Multiple Work public DataTable Search(string sqlQuery) { try { conn = new MySqlConnection(connectionString); conn.Open(); da = new MySqlDataAdapter(sqlQuery, conn); cb = new MySqlCommandBuilder(da); DT = new DataTable(); da.Fill(DT); conn.Close(); da.Dispose(); //Memory Free... } catch (MySqlException ex) { MessageBox.Show("Unable to access data!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return DT; } //--------------------------------------------------------------------------- //----------------------------------------------------------------------- //Get Max Product Count public int GetMaxPdCount(string sqlQuery) { try { conn = new MySqlConnection(connectionString); cmd = new MySqlCommand(sqlQuery, conn); conn.Open(); pCount = Convert.ToInt32(cmd.ExecuteScalar()); conn.Close(); } catch (MySqlException ex) { MessageBox.Show("Cannot Get Max Product Count!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return pCount; } } }
    Let me be specific:

    I have the following components in my project:
    1) Form
    2) Textbox
    3) Button
    4) DataGridView
    5) A class named Database - It has all the code stuff to interact with the database means it can insert,update,delete etc

    I type the desired name in the textbox, press the button and then a query is passed to the database function to retrieve the desired data. My Database class always returns me the "SELECT" query results in a "DATATABLE".

    I receive the "DATATABLE" in a variable called DataTable DT. I have this code in my button click event dgv.datasource=DT then all my data is shown in the DataGridView.

    Now the question is that how will the changes done in my dgv will be reflected in the datatable so than I can pass my updated datatable back to the database using my database class only?

    Hope you get my question!!! I more thing is it necessary to use the adapter fill method to only fill the dataset not the datatable?

    Waiting for the positive response...

    Friday, January 6, 2012 8:38 AM


  • You use MySqlDataAdapter.Update to push updates back to the server.   The datatable will track all inserts/updates and deletes, then you run MySqlDataAdapter.Update and it will perform the appropriate insert/update/delete commands.   Note you have to setup the MySqlDataAdapter with appropriate InsertCommand, UpdateCommand and DeleteCommand as well before calling Update. 


    See this KB for general idea =>

    Friday, January 6, 2012 7:16 PM