Synchronizing data between Mysql and SQL CE

[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.

[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.

[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

[Alexey]

Take a look at Table and DataSet demo projects.
By architecture I meant your datamodel.

[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

[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.

[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

[Alexey]

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.

[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 ?

[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.

[Qwerty1979]

Did you find any convinient way to merge the two Sqlce and mysql?

[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.