Page 1 of 1
Synchronizing data between Mysql and SQL CE
Posted: Mon 26 Mar 2007 11:12
by IamMacro
Hi,
Im developping a VB.Net program to let employee's update workorders into a SQL CE database on their Pocket Pc ..
Now when returning to the company they should be able to synchronize their data on the pocket pc from SQL CE to a (linuxserver) mysql database , what's the fastest way to do this ?
What are my options using mysqlDirect.net ?
Are there any Synchronization options in MysqlDirect.net mobile ?
Thx.
Posted: Mon 26 Mar 2007 14:43
by Alexey
Synchronization can be performed using datasets and datatables. Please take a look at our demo projects in the installation folder.
We cannot give you an advice on the fastest way, because we do not know your architecture.
Posted: Tue 27 Mar 2007 07:16
by IamMacro
Is there a specific sample i should look at , most ive seen were just for making connections not for synchronizing , but i could be wrong.
by Architecture you mean our Datamodel or Server - Pc setup ?
thx
Posted: Tue 27 Mar 2007 07:28
by Alexey
Take a look at Table and DataSet demo projects.
By architecture I meant your datamodel.
Posted: Wed 28 Mar 2007 09:22
by IamMacro
Code: Select all
Dim sqlconn As New SqlCeConnection("Datasource=\PDADATA3.sdf")
Dim sqlDA As New SqlCeDataAdapter("select * from client", sqlconn)
sqlconn.Open()
Dim sqlds As New DataSet
sqlDA.Fill(sqlds, "klant")
DataGrid1.DataSource = sqlds.Tables("klant")
Try
myconnection.Open()
Dim msqlDA As New MySqlDataAdapter("select * from Klant", myconnection)
Dim sqlds2 As New DataSet
msqlDA.AcceptChangesDuringFill = False
msqlDA.Fill(sqlds2, "Klant")
sqlDA.Update(sqlds2, "klant")
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
End Sub
this code connects both databases and it fills up the SQL CE database with new and MYsql data , except it has data that was already in the SQL Ce database and MYSQL data that was recently added , this is one method and i've read it is rather slow.using data adapters..
My datamodel is rather large but the amount of data that needs to be transported is low , its possible with only 20 rows per visit at the company.
for this example with clients
SQL CE database(Client Table)
ClientID , Clientname , ClientStreet
mysql database (Client Table)
ClientId,clientname,client,street,client..,client..., more info
I hope you can help me with another technique of transporting and synchronizing the data.
Ive looked at the examples and they just help me fill up datasets and data-adapters and this of course works well.
thx
Posted: Thu 29 Mar 2007 06:56
by Alexey
This scenario is acceptable for this task, but can cause data conlicts. If it doesn't satisfy your speed requirements, etc. you have to design a custom architecture for synchronization purposes.
Posted: Thu 29 Mar 2007 13:38
by IamMacro
Code: Select all
Dim msqlDA As New MySqlDataAdapter("select KlantID,Klantnaam,Straat from Klant", myconnection)
Dim sqlds2 As New DataSet
sqlDA.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("klant", "klant", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Klantnaam", "Klantnaam"), New System.Data.Common.DataColumnMapping("Straat", "Straat")})})
msqlDA.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Klant", "Klant", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Klantnaam", "Klantnaam"), New System.Data.Common.DataColumnMapping("Straat", "Straat")})})
Dim sqlcomm As New SqlCeCommandBuilder
sqlcomm.DataAdapter = sqlDA
msqlDA.AcceptChangesDuringFill = False
Dim msqlds As New DataSet
msqlDA.Fill(msqlds, "Klant")
'sqlds.Merge(msqlds)
Dim SQlceklanten As DataTable = sqlds.Tables("klant")
Dim MysqlKlanten As DataTable = msqlds.Tables("klant")
SQlceklanten.Merge(MysqlKlanten, False, MissingSchemaAction.AddWithKey)
'******** MissingSchemaAction.Ignore works ***
sqlds.AcceptChanges()
Dim changes As DataSet = sqlds.GetChanges
DataGrid1.DataSource = changes.Tables("Klant")
sqlDA.Update(changes, "Klant")
This is a bit further into synchronizing both databases with datasets , but im getting an error
".KlantID and .KlantID have conflicting properties: DataType property mismatch."
Which is strange cause both Mysql database and SQlce database are using Integers (Bigints)
The missingschemaaction.ignore works , in adding the data into the SQLCE database but this is double what it is supposed to do.
For example.
SQLCE database has 2 clients.
Mysql database has 10 , what i end up with on the SQL CE database is
12 clients .
Im running out of ideas to make the datasets or dataadapters update only the changes.
thx
Posted: Fri 30 Mar 2007 13:57
by Alexey
IamMacro wrote:Which is strange cause both Mysql database and SQlce database are using Integers (Bigints)
MySQLDirect .NET determines colunm type on the basis of information from the database.
Posted: Mon 02 Apr 2007 07:08
by IamMacro
so how can i resolve this , all the data in the database are numbers.. and all the data coming from the other database are numbers too.
Or am i looking at this problem wrong ?
Posted: Mon 02 Apr 2007 08:07
by Alexey
Please send me a small test project to reproduce the problem.
Include definitions of your own database objects.
Use e-mail address provided in the Readme file.
Did you find any convinient way to merge the two?
Posted: Tue 24 Feb 2009 15:53
by Qwerty1979
Did you find any convinient way to merge the two Sqlce and mysql?
Posted: Wed 25 Feb 2009 14:59
by AndreyR
Entity Framework seems to be the technolology you are interested in.
It allows using one conceptual model and several storage models for different data sources.
Take a look on our Entity Framework samples here:
http://devart.com/dotconnect/efquerysamples.zip
In these samples we implemented the scenario similar to the one you described - samples use the only one CSDL model and several SSDL models - for Oracle, MS SQL Server, PostgreSQL, MySQL and SQLite.