Transfer data from Mysql to SQL CE

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
bonnet
Posts: 3
Joined: Thu 23 Aug 2007 02:40

Transfer data from Mysql to SQL CE

Post by bonnet » Thu 23 Aug 2007 02:55

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
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 23 Aug 2007 07:15

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

bonnet
Posts: 3
Joined: Thu 23 Aug 2007 02:40

Post by bonnet » Thu 23 Aug 2007 07:44

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
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 23 Aug 2007 08:45

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
Posts: 3
Joined: Thu 23 Aug 2007 02:40

Post by bonnet » Fri 24 Aug 2007 06:36

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
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 27 Aug 2007 06:26

Well done!

ccaceres
Posts: 2
Joined: Thu 07 Feb 2013 18:11

Re: Transfer data from Mysql to SQL CE

Post by ccaceres » Thu 07 Feb 2013 18:18

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Transfer data from Mysql to SQL CE

Post by Pinturiccio » Mon 11 Feb 2013 13:47

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.
ccaceres wrote: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
Posts: 2
Joined: Thu 07 Feb 2013 18:11

Re: Transfer data from Mysql to SQL CE

Post by ccaceres » Fri 15 Feb 2013 18:07

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Transfer data from Mysql to SQL CE

Post by Pinturiccio » Tue 19 Feb 2013 13:35

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.

Post Reply