Question Copy Table from 1 DB to another using DataSet

[alsatan]

Hello to all, sorry if my english is not so good, I will try to explain the problem.

I need to copy all data from a Table of a Acces mdb (connected with OleDB) to a Table of a MySql DB (connected with ODBC)

I made a working solution, but it's very slow, and so I want to try other solutions to check if they can give me more performance.

The solution connecting to the mdb, using a DataReader, then for each row in Datareader I make an INSERT into the Mysql Table ( before copy I truncate the table to get it empty)

The records are more than 10K and this operation is very slow, and do be onest I need to do the same thing on other 2 tables also very big as this one.

I cannot make a direct sql insert ( as "INSERT INTO A in ..... SELECT * FROM B) becouse the 1 DB has a OleDB conn and the other has a ODBC conn

So I thought to try to make this operation using TableAdapters and DataSet, but I'm not able to make it working.

The problem is that the Dataset for VB HAS.CHANGES is false

If you need some code I can post but what I do is this.

- Connection to MDb
- Create OleDbTableAdapter
- Create DataSet
- Fill DataSet with TableAdapter
- Connection to MySqlDB
- Create ODBCTableAdapter
- Using Update command of ODBCTableAdapter with the first Dataset.

But DS has no changes commited so he don't write anything to DB, so I thought to use another Dataset and copy data from DS1 to DS2 to add rows, see if has.changes was true and the making Update command of ODBCTableadapter using DS2.
I tryed to copy data between datasets doing:

ds2 = ds1.copy

o I tryed also to use dataset import function, looping DS1 datarows and Importing all rows from DS1 to DS2.
In both cases the rows are added to DS2, but still HAS.CHANGES is false, what can I do?
Just to clarify possible questions I didn't use DS.Acceptchanges, PrimaryKey is defined , TA Update command is defined, DS has data (I populate 2 DataGrid to check it).
No errors given, just no data written on DB.

Any suggestion? Thanks in advice

 

[cjard]

All the tableadapter will do, essentially, is the same as what youre doing with your "for each row in datatable.. insert values"

If you wanna persist with it, and the two tables have the same column names and types etc, then easiest just to do:

For Each ro as DataRow in db1Datatable.Rows
  ro.SetAdded()
Next

All the stuff i've read on mysql indicated you'd be faster off taking your access datatable and going through it row by row, putting all the data into a csv file, then using LOAD DATA command of mysql, or make use of the bulk insert feature, where a insert command can take more than one set of values:

Dim msc as MySqlCommand = New MySqlCommand("insert into person(firstname,lastname) values")
For i = 0 to 9 step 1
  msc.CommandText += "(@first" & i  & " ,@last " & i & "),"
  msc.Parameters.AddWithValue("@first" & i, "")
  msc.Parameters.AddWithValue("@last" & i, "")
Next i
'now we have a nice command that can insert 10 names in one go. it looks like:
'insert into person (firstname,lastname) values (@first1,@last1),(@first2,@last2),(@first3,@last3) ... (@first9,@last9),
'we need to trim the comma off the end
msc.CommandText = msc.CommandText.TrimEnd(","c)

'the command also has a ready populated Parameters collection so we just change the values:
Dim i as integer = 0
For Each ro As DataRow in db1Datatable.Rows
  msc.Parameters("@first" & i).Value = ro("FirstName").ToString()
  msc.Parameters("@last" & i).Value = ro("LastName").ToString()
  i+=1
  If i < 9 Then
    msc.ExecuteNonQuery()
    i = 0
  End If
Next

'note at this point if for example we have 97 rows in our table, the insert will have run 9 times but there will be 7 unsaved rows because we never did an executenonquery() for them so far
'what to do with them.. i'll leave as an exercise for you.. you could chop the query text down to finish after @last6.. or populate the remaining parameters with null and insert nulls then delet them
'or you could just run 7 inserts.. many ways to skin this cat

[

This webpage says that your inserts could be tens or hundreds of times faster with this method

MySQL LOAD DATA vs. Bulk Insert which is faster | MySQL Insert Benchmarking | MediaBandit Ltd Blog


note all my code in this post is written from memory, as a pseudocode aid to showing the concept. it is not intended to compile and work as is