Update DataTable to MySql database C#

Question

I have a DataTable MyDT like below:

string MyConString = "SERVER=" + sConfig_hostname + ";" +
                "DATABASE=" + sConfig_dbname + ";" +
                "UID=" + sConfig_dbusername + ";" +
                "PASSWORD=" + sConfig_dbpassword + ";Allow Zero Datetime=true;";
            MySqlConnection connection = new MySqlConnection(MyConString);
            string sQuery="Select * from Table";
            connection.Open();
            MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, connection);
            myDA.SelectCommand.ExecuteNonQuery();
            DataTable MyDT=new DataTable()// <- My DataTable
            myDA.Fill(MyDT);
            connection.Close();
...
...
// do something with MyDT
...
...

After do something with MyDT , I want to update it to the DataBase, how can I do it ?I have searched google for pages but almost update by looping the datatable. is there a way to update the whole datatable without loop?Please help! Thanks for reading

Massimiliano Kraus · Accepted Answer · 2017-03-23 01:56:10Z

4

You need to configure SelectCommand, DeleteCommand, UpdateCommand and InsertCommand properties of DataAdapter. You may use MySqlCommandBuilder to populate three command objects (Select, Delete, Update).

MySqlConnection connection = new MySqlConnection(MyConString);
string sQuery="Select * from Table";

MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, connection);
MySqlCommandBuilder cmb = new MySqlCommandBuilder(myDA);

DataTable MyDT = new DataTable()// <- My DataTable
myDA.Fill(MyDT);

//Add new rows or delete/update existing one
//and update the DataTable using 

myDA.Update(MyDT);

edited Mar 23, 2017 at 1:56

Massimiliano Kraus

3,8925 gold badges32 silver badges51 bronze badges

answered Jul 5, 2012 at 2:21

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NoobAnotherTime Over a year ago

Hi AVD, Can you send me some links for configure those properties, I am newbie, and please forgive for my bad language.Thanks :).

Chenxiao Over a year ago

@AVD What does your ` MySqlCommandBuilder cmb=new MySqlCommandBuilder(myDA);`do? You did not seem to call it else where.

KV Prajapati Over a year ago

@Chenxiao - It automatically generates single-table commands used to reconcile changes made to a DataSet with the associated database. This is an abstract class that can only be inherited. msdn.microsoft.com/en-us/library/…

Collectives™ on Stack Overflow

Update DataTable to MySql database C#

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related