CODEWITHSUNDEEP
c#mysqlcopydataset
Yosh
Yosh

Reputation: 1

How to copy a populated MYSQL-Table to another Database in C#

I am using C#, .net 3.5 and a MySQL-Database. I have a populated table on Server 1 which I want to copy to Server 2. On Server 2 I have the same table structure, but the table is empty. Now I want to copy the data from Server 1 to Server 2. I connect to Server 1 and fill the information into a DataSet - no problem. Then I open a second connection to the other server. My problem is, how can I store this DataSet on the second Server? The Update()-command has no effect, even if I set the same UpdateCommand und InsertCommand-CommandText as for Server 1. I get no error when I use Update(DataSet,"TableName"), but the table is still empty. For MSSQL-Databases BulkCopy would be an option, but it seems that there is no equivalent for MySQL DBs!?

I do not want to use mysqldump, I want to do it programmaticaly in C# on a client.

Any idea?

EDIT:

        MySqlConnection conn_DB1 = new MySqlConnection(connString_DB1);
        MySqlDataAdapter adp_DB1 = new MySqlDataAdapter("select * from myDB", conn_DB1);

        DataSet theDataSet_DB1 = new DataSet();
        adp_DB1.Fill(theDataSet_DB1, "myDB"); //everything is fine, the Data is there


        MySqlConnection conn_DB2 = new MySqlConnection(connString_DB2);
        MySqlDataAdapter adp_DB2 = new MySqlDataAdapter("select * from myDB", conn_DB2);

        DataSet theDataSet_DB2 = new DataSet();
        adp_DB1.Fill(theDataSet_DB2, "myDB"); //this DataSet is empty, of course

        theDataSet_DB2 = theDataSet_DB2.Copy(); //the data is updated, the second DataSet has all the rows as expected

        adp_DB2.Update(theDataSet_DB2, "myDB"); //no error on execution, but the table is still empty on the second server

Upvotes: 0

Views: 3838

Answers (2)

Luis
Luis

Reputation: 11

The rows in theDataSet_DB2.Tables are "unchanged", so any occurs. You must mark rows as "added" before to update them.

foreach (DataTable table in theDataSet_DB2.Tables)
  foreach (DataRow row in table.Rows)
    row.SetAdded ();

It's necessary to build a MySqlCommandBuilder associated to the dataAdapter:

new MySqlCommandBuilder (dataAdapter);

Upvotes: 1

Yahia
Yahia

Reputation: 70369

I am not sure which library you use to access MySQL DB...

For a "pure ADO.NET" with the original ADO.NET provider from MySQL see this artcile http://www.codeproject.com/KB/database/GenericCopyTableDataFcn.aspx

IF you use the MySQL .NET connector there is a class called MySqlBulkLoader see http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-bulk-loader.html . this class takes AFAIK a file - so would have to create a file from the source table first to use it...

IF you are using the Devart MySQL components you could:

MySqlLoader ML = new MySqlLoader("myDB", conn_DB2);
ML.CreateColumns();
ML.LoadTable(theDataSet_DB1.Tables[0]);

Upvotes: 0

Related Questions