SyncFramework

SyncFramework

Postby Thomas Heinze » Wed 12 Nov 2014 08:36

Hi,

i had a PostgreSql Database and want to sync the database for local working with an SqL Local DB.

Can i use that SyncFramwork or is that just working from PostgreSql to PostgreSql?
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Thomas Heinze » Wed 12 Nov 2014 13:58

Hi,

so i´ve tried Sync with this tutorial.

http://www.devart.com/dotconnect/postgresql/docs/?SyncFramework.html

I´ve installed Microsoft SyncFramework 2.1 and Microsoft.Sync DataProviders 3.1 via NuGet.

For Compile the app i´ve got this Error
Code: Select all
Fehler   12   Die Assembly 'Devart.Data.PostgreSql.Synchronization, Version=7.3.201.0, Culture=neutral, PublicKeyToken=09af7300eec23701' verwendet 'Microsoft.Synchronization.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' mit einer höheren Versionsnummer als die Assembly 'Microsoft.Synchronization.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91', auf die verwiesen wird.   c:\Program Files (x86)\Devart\dotConnect\PostgreSQL\SyncFramework\Devart.Data.PostgreSql.Synchronization.dll   DB

Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Pinturiccio » Thu 13 Nov 2014 16:59

Thomas Heinze wrote:For Compile the app i?ve got this Error

Probably the reason of this error is that you have added the Microsoft.Synchronization.Data.dll assembly of version 2.0.0.0 instead of the necessary 3.1.0.0. For more information, please refer to http://www.devart.com/dotconnect/postgresql/docs/?SyncFramework.html

Thomas Heinze wrote:Can i use that SyncFramwork or is that just working from PostgreSql to PostgreSql?

You can use Sync Framework for synchronizing PostgreSQL and SQL Server databases. Here is a simple example, demonstrating the synchronization between the PostgreSQL and SQL Server databases.

Code: Select all
PgSqlConnection serverConnection = new PgSqlConnection();
serverConnection.ConnectionString = "PostgreSQL connection string";

SqlConnection clientConnection = new SqlConnection();
clientConnection.ConnectionString = "SQL Server connection string";

DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription("ResultsScope");
DbSyncTableDescription tableDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("RESULTS", serverConnection);

scopeDescription.Tables.Add(tableDescription);
PgSqlSyncScopeProvisioning serverProvision = new PgSqlSyncScopeProvisioning(serverConnection, scopeDescription);
serverProvision.Apply();

DbSyncScopeDescription scopeDescription2 = new DbSyncScopeDescription("ResultsScope2");
DbSyncTableDescription tableDescription2 = SqlSyncDescriptionBuilder.GetDescriptionForTable("RESULTS", clientConnection);

scopeDescription2.Tables.Add(tableDescription2);
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConnection, scopeDescription2);
clientProvision.Apply();

SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
syncOrchestrator.RemoteProvider = new PgSqlSyncProvider("ResultsScope", serverConnection, null, null);
syncOrchestrator.LocalProvider = new SqlSyncProvider("ResultsScope2", clientConnection, null, null);
syncOrchestrator.Synchronize();

PgSqlSyncScopeDeprovisioning deprovision = new PgSqlSyncScopeDeprovisioning(serverConnection);
deprovision.DeprovisionScope("ResultsScope");

SqlSyncScopeDeprovisioning deprovision2 = new SqlSyncScopeDeprovisioning(clientConnection);
deprovision2.DeprovisionScope("ResultsScope2");
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Re: SyncFramework

Postby Thomas Heinze » Fri 14 Nov 2014 16:01

@Pinturiccio

Thanks for that.

That's working pretty well.
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Thomas Heinze » Sun 16 Nov 2014 17:16

So i have tested that with a single table and with more tables.
It works pretty good.

Now i have a Problem with Charset in SqlServer.
The PostgreSql DB has characterset utf8.

The SqlServer don't display the umlauts correct. How can i tell the database to set the correct charset.
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Thomas Heinze » Sun 16 Nov 2014 19:48

Hi,

so i've found the fail. i've forgot the unicode set to true in connection string.

After setting unicode everything works very well and fast.
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Pinturiccio » Mon 17 Nov 2014 13:03

We are glad to hear that the issue is solved. If you have any questions, feel free to contact us.
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Re: SyncFramework

Postby Thomas Heinze » Mon 17 Nov 2014 13:24

Hi Pinturiccio,

i've a problem with the sync with filter.

The first sync is for my global tables and that's working very well.

Now i've some tables where i want to sync only the rows of the specific project.

I've tried that and it seems to work perfect. Now i have to tables where something going wrong.

Here my Code for the sync with filter:
Code: Select all
public void SyncVerfahren(int vNr)
{
PgSqlConnection serverConn = new PgSqlConnection(@"Database=waitzrichter_test;host=********;user id=*****;password=******;unicode=True");
            SqlConnection clientConn = new SqlConnection(@"Data Source=(localdb)\v11.0;Initial Catalog=waitzrichter_test;Integrated Security=True");

            DbSyncScopeDescription serverFilterScope = new DbSyncScopeDescription("VerfahrenScope");
            DbSyncTableDescription tbAussonderung = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Aussonderung", serverConn);
            DbSyncTableDescription tbVerfahren = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Verfahren", serverConn);
            DbSyncTableDescription tbNotizen = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Notizen", serverConn);

            serverFilterScope.Tables.Add(tbAussonderung);
            //serverFilterScope.Tables.Add(tbVerfahrenKunden);
            serverFilterScope.Tables.Add(tbVerfahren);
            serverFilterScope.Tables.Add(tbNotizen);

            SyncScopeProvisioning serverFilterProv = new PgSqlSyncScopeProvisioning(serverConn, serverFilterScope);

            ////Tabelle Aussonderung
            serverFilterProv.Tables["Aussonderung"].AddFilterColumn("VerfahrensNummer");
            serverFilterProv.Tables["Aussonderung"].FilterClause = "t.\"VerfahrensNummer\" = " + vNr + "";
            //

            //Tabelle Verfahren
            serverFilterProv.Tables["Verfahren"].AddFilterColumn("VerfahrensNummer");
            serverFilterProv.Tables["Verfahren"].FilterClause = "t.\"VerfahrensNummer\" = " + vNr + "";

            //Tabelle Notizen
            serverFilterProv.Tables["Notizen"].AddFilterColumn("VerfahrensNummer");
            serverFilterProv.Tables["Notizen"].FilterClause = "t.\"VerfahrensNummer\" = " + vNr + "";

            serverFilterProv.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverFilterProv.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

            serverFilterProv.Apply();

            //SQL Server Client Provisioning
            DbSyncScopeDescription clientFilterScope = new DbSyncScopeDescription("VerfahrenClientScope");
            DbSyncTableDescription tbAussonderungClient = SqlSyncDescriptionBuilder.GetDescriptionForTable("Aussonderung", clientConn);
            DbSyncTableDescription tbVerfahrenClient = SqlSyncDescriptionBuilder.GetDescriptionForTable("Verfahren", clientConn);
            DbSyncTableDescription tbNotizenClient = SqlSyncDescriptionBuilder.GetDescriptionForTable("Notizen", clientConn);

            clientFilterScope.Tables.Add(tbAussonderungClient);
            clientFilterScope.Tables.Add(tbVerfahrenClient);
            clientFilterScope.Tables.Add(tbNotizenClient);


            SqlSyncScopeProvisioning clientFilterProv = new SqlSyncScopeProvisioning(clientConn, clientFilterScope);

            //clientFilterProv.Apply();

            // Erstelle die Filter
            clientFilterProv.Tables["Aussonderung"].AddFilterColumn("VerfahrensNummer");
            clientFilterProv.Tables["Aussonderung"].FilterClause = "[side].[VerfahrensNummer] = " + vNr + "";

            clientFilterProv.Tables["Verfahren"].AddFilterColumn("VerfahrensNummer");
            clientFilterProv.Tables["Verfahren"].FilterClause = "[side].[VerfahrensNummer] = " + vNr + "";

            clientFilterProv.Tables["Notizen"].AddFilterColumn("VerfahrensNummer");
            clientFilterProv.Tables["Notizen"].FilterClause = "[side].[VerfahrensNummer] = " + vNr + "";

            clientFilterProv.SetCreateTableDefault(DbSyncCreationOption.Skip);
            clientFilterProv.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);

            clientFilterProv.Apply();

            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            syncOrchestrator.RemoteProvider = new PgSqlSyncProvider("VerfahrenScope", serverConn, null, null);

            syncOrchestrator.LocalProvider = new SqlSyncProvider("VerfahrenClientScope", clientConn, null, null);

            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            Anzahl = syncStats.DownloadChangesTotal;

            Console.WriteLine("StartZeit: {0}", syncStats.SyncStartTime);
            Console.WriteLine("Fehler: {0}", syncStats.DownloadChangesFailed);
            Console.WriteLine("Applied: {0}", syncStats.DownloadChangesApplied);
            Console.WriteLine("Total: {0}", syncStats.DownloadChangesTotal);
            Console.WriteLine("EndZeit: {0}", syncStats.SyncEndTime);

            DeProvisioning(serverConn, clientConn, "VerfahrenScope", "VerfahrenClientScope");
}


This function is working. Only the rows for for the selected vNr will be syncronized.

Now I have two tables where i get an error on the syncronize call.

If i call the function for the two tables i've got this error:
Function VerfahrenBerechtigte_selectchanges_527d8abf_3d27_47e6_8498_b69966e3e90f not found.


In pgAdmin i've looked for the function and the name is not correct:
VerfahrenBerechtigte_selectchanges_527d8abf_3d27_47e6_8498_b699


That is on both tables. If i syncronize the complete tables everything is working.

I dont't know where the problem should be.
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42

Re: SyncFramework

Postby Thomas Heinze » Tue 18 Nov 2014 19:05

So i've found the error. After looking for the function in pgAdmin and look inside the errormessage of VS, i've looked for postgresql maximum table name and function length.

This is 63 signs. My table names for the two tables are 20 signs and with the append function and id the signs of the function are bigger then 63.

So i've renamed the tables with max 12 signs and now it works.
Thomas Heinze
 
Posts: 10
Joined: Fri 18 Jul 2014 16:42


Return to dotConnect for PostgreSQL