Transfer data from Mysql to SQL CE
Transfer data from Mysql to SQL CE
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
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
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
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
Re: Transfer data from Mysql to SQL CE
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Transfer data from Mysql to SQL CE
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.
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.
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 wrote:please could you give an example of how they solved the problem
Re: Transfer data from Mysql to SQL CE
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.
Thanks.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Transfer data from Mysql to SQL CE
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.