Transfer data from Mysql to SQL CE

Transfer data from Mysql to SQL CE

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

Postby Alexey » Thu 23 Aug 2007 07:15

To select data use DataReader. To insert data use DataAdapter or DataTable.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

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

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

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

Postby Alexey » Mon 27 Aug 2007 06:26

Well done!
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Re: Transfer data from Mysql to SQL CE

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

Re: Transfer data from Mysql to SQL CE

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Re: Transfer data from Mysql to SQL CE

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

Re: Transfer data from Mysql to SQL CE

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL