SyncFramework

[Thomas Heinze]

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]

Hi,

so i´ve tried Sync with this tutorial.

http://www.devart.com/dotconnect/postgr ... ework.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

[Pinturiccio]

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/postgr ... ework.html

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");

[Thomas Heinze]

@Pinturiccio

Thanks for that.

That's working pretty well.

[Thomas Heinze]

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]

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.

[Pinturiccio]

We are glad to hear that the issue is solved. If you have any questions, feel free to contact us.

[Thomas Heinze]

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]

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.