Transfer data from Mysql to SQL CE

[bonnet]

I am developing a mobile VB program that will copy all data from mysql and transfer to SQL CE everytime when the program starts. I currently use the Dataset and DataAdapter functions to do it, but it is extremely slow since the database in MySQL has about 40000 records. Is there other better ways to make the copy go faster? Thanks

[Alexey]

To select data use DataReader. To insert data use DataAdapter or DataTable.

[bonnet]

Thanks for reply. The problem is the insertions take too long when using DataAdapter. Is it possible to bulk copy a table from mysql to sql ce or directly import a txt file into sql ce rather than inserting record one by one?

Here is my code:

Dim sqlconn As New SqlCeConnection("Datasource=\test.sdf")

Dim sqlDA As New SqlCeDataAdapter("select * from table", sqlconn)

Dim builder As SqlCeCommandBuilder = New SqlCeCommandBuilder(sqlDA)
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"

Try
sqlconn.Open()
Dim sqlds As New System.Data.DataSet

Dim myconnection As MySqlConnection = New MySqlConnection("User Id=root;Password=sa;Host=localhost;Database=abc")
myconnection.Open()
Dim msqlDA As New MySqlDataAdapter("select * from table", myconnection)
Dim sqlds2 As New System.Data.DataSet


msqlDA.AcceptChangesDuringFill = False

msqlDA.Fill(sqlds2, "table")


sqlDA.Update(sqlds2, "table")


Catch ex As MySqlException
MessageBox.Show(ex.ToString)
End Try

[Alexey]

Either use UpdateBatchSize property of SqlCeDataAdapter class in your code or take a look at the following passage:

Code: Select all

        Dim sqlconn As New SqlCeConnection("Datasource=\test.sdf")
        Try
            sqlconn.Open()
            Dim myconnection As MySqlConnection = New MySqlConnection("User Id=root;Password=sa;Host=192.168.0.1;Database=abc")
            myconnection.Open()
            Dim mycmd As MySqlCommand = New MySqlCommand("select * from dept", myconnection)
            Dim myreader As MySqlDataReader = mycmd.ExecuteReader
            Dim sqlcmd As SqlCeCommand = New SqlCeCommand("insert into emp values(@deptno, @dname, @loc)", sqlconn)

            While myreader.Read()
                sqlcmd.Parameters(0).Value = myreader.GetValue(0)
                sqlcmd.Parameters(1).Value = myreader.GetValue(1)
                sqlcmd.Parameters(2).Value = myreader.GetValue(2)
                sqlcmd.ExecuteNonQuery()
            End While
        Catch ex As MySqlException
            MessageBox.Show(ex.ToString)
        End Try

[bonnet]

Thanks Alexey! Based on your reference, I have now using DataReader with SqlCeResultSet instead of DataSet & DataAdapter. The performance have improved a lot, the whole copy process cut down to 2 mins with 40000 records

[Alexey]

Well done!

[ccaceres]

hello, please could you give an example of how they solved the problem, I have tried to download data from mysql but the process takes too long line by line, thanks.

[Pinturiccio]

There are two ways to retrieve data from the database.
1. Use MySqlDataAdapter as is demonstrated in the third post of this topic;
2. Use MySqlDataReader as is demonstrated in the fourth post of this topic.

please could you give an example of how they solved the problem

The user solved the issue using the second item (the fourth post). If your data are retrieved too slowly when you use MySqlDataReader, may be, this means that they cannot be retrieved faster because they are too big. Try using MySqlDataAdapter and compare its performance with the one of MySqlDataReader.

[ccaceres]

hello, thanks,the problem is not reading the database, this is fast, my problem is to write the data on the device quickly. I work over 3G networks.

Thanks.

[Pinturiccio]

Writing to a device is performed via a 3G network, and depends on the speed within the network and the way the OS of your device works with this network. dotConnect for MySQL reads data from database and its performance can be limited by the speed of your 3G network.