Synchronizing data between Mysql and SQL CE

Synchronizing data between Mysql and SQL CE

Postby IamMacro » Mon 26 Mar 2007 11:12

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.
IamMacro
 
Posts: 5
Joined: Mon 26 Mar 2007 10:42

Postby Alexey » Mon 26 Mar 2007 14:43

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

Postby IamMacro » Tue 27 Mar 2007 07:16

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
IamMacro
 
Posts: 5
Joined: Mon 26 Mar 2007 10:42

Postby Alexey » Tue 27 Mar 2007 07:28

Take a look at Table and DataSet demo projects.
By architecture I meant your datamodel.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby IamMacro » Wed 28 Mar 2007 09:22

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
IamMacro
 
Posts: 5
Joined: Mon 26 Mar 2007 10:42

Postby Alexey » Thu 29 Mar 2007 06:56

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

Postby IamMacro » Thu 29 Mar 2007 13:38

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
IamMacro
 
Posts: 5
Joined: Mon 26 Mar 2007 10:42

Postby Alexey » Fri 30 Mar 2007 13:57

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

Postby IamMacro » Mon 02 Apr 2007 07:08

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 ?
IamMacro
 
Posts: 5
Joined: Mon 26 Mar 2007 10:42

Postby Alexey » Mon 02 Apr 2007 08:07

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

Did you find any convinient way to merge the two?

Postby Qwerty1979 » Tue 24 Feb 2009 15:53

Did you find any convinient way to merge the two Sqlce and mysql?
Qwerty1979
 
Posts: 2
Joined: Sun 09 Nov 2008 20:13

Postby AndreyR » Wed 25 Feb 2009 14:59

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for MySQL