Share via


In this topic, you will learn how to synchronize a subscription asynchronously by using the SqlCeReplication class. Asynchronous data synchronization lets your application perform other actions while synchronization is occurring. For more information about using the SqlServerCe namespace, see the SqlServerCe namespace reference documentation.

To start asynchronous data synchronization

  1. Initialize a SqlCeReplication object. You must declare the object outside any method so that it can be accessed.

    private SqlCeReplication repl;
    
  2. Within the method that begins synchronization, create an instance of the SqlCeReplication object, and then set the necessary properties for synchronization with a Publisher.

    this.repl = new SqlCeReplication();
    repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa35.dll";
    repl.InternetLogin = "MyInternetLogin";
    repl.InternetPassword = "<password>";
    repl.Publisher = "MyPublisher";
    repl.PublisherDatabase = "MyPublisherDatabase";
    repl.PublisherLogin = "MyPublisherLogin";
    repl.PublisherPassword = "<password>";
    repl.Publication = "MyPublication";
    repl.Subscriber = "MySubscriber";
    repl.SubscriberConnectionString = "Data Source=MyDatabase.sdf";
    
  3. Call the BeginSynchronize method. This returns an IAsyncResult object. When you call BeginSynchronize, you must pass in an AsyncCallback event handler and the SqlCeReplication object. The AsyncCallback event is fired when synchronization is completed. You can also pass in event handlers for the OnStartTableUpload, OnStartTableDownload, and OnSynchronization events.

    IAsyncResult ar = repl.BeginSynchronize
    (new syncCallback(this.SyncCompletedCallback),
    new OnStartTableUpload(this.OnStartTableUploadCallback),
    new OnStartTableDownload(this.OnStartTableDownloadCallback),
    new OnSynchronization(this.OnSynchronizationCallback), repl);
    

To handle synchronization events:

  1. The only required event is the AsyncCallback, which takes the IAsyncResult as its only parameter.

    public void SyncCompletedCallback(IAsyncResult ar)
    { ... }
    
  2. The OnStartTableUpload and OnStartTableDownload event handlers both take the IAsyncResult and the table name (as a string) as parameters.

    public void OnStartTableUploadCallback(IAsyncResult ar, string tableName)
    { ... }
    public void OnStartTableDownloadCallback(IAsyncResult ar,string tableName)
    { ... }
    
  3. The OnSynchronization event handler takes the IAsyncResult and an integer that represents the percent of synchronization that has completed as parameters.

    public void OnSynchronizationCallback(IAsyncResult ar, int percentComplete)
    { ... }
    

To end asynchronous data synchronization

  • Within the AsyncCallback event handler, use the passed-in SqlCeReplication object and IAsyncResult object to call the EndSynchronize method.

    SqlCeReplication repl = (SqlCeReplication)ar.AsyncState;
    repl.EndSynchronize(ar);
    

Example

This example shows how to implement asynchronous data synchronization. In this example, the application uses the SyncStatus to update the user interface during synchronization so that the user knows about the progress. The user interface is updated whenever a synchronization event is fired and every time that a table is uploaded and downloaded. When synchronization is complete, the application obtains the time of the last successful synchronization from the __sysMergeSubscriptions table and displays the results.

 public class MyForm : Form
    {
        private string tableName;
        private int percentage;
        private SyncStatus eventStatus;
        private SqlCeReplication repl;
        private EventHandler myUserInterfaceUpdateEvent;

        internal enum SyncStatus
        {
            PercentComplete,
            BeginUpload,
            BeginDownload,
            SyncComplete
        }

        public MyForm()
        {
            // InitializeComponent();
            this.myUserInterfaceUpdateEvent = new EventHandler(MyUserInterfaceUpdateEvent);
        }

        public void MyUserInterfaceUpdateEvent(object sender, System.EventArgs e)
        {
            switch (this.eventStatus)
            {
                case SyncStatus.BeginUpload:
                    //this.labelStatusValue.Text = "Began uploading table : " + tableName;
                    break;

                case SyncStatus.PercentComplete:
                    //this.labelStatusValue.Text = "Sync with SQL Server is " + percentage.ToString() + "% complete.";
                    break;

                case SyncStatus.BeginDownload:
                    //this.labelStatusValue.Text = "Began downloading table : " + tableName;
                    break;

                case SyncStatus.SyncComplete:
                    //this.labelStatusValue.Text = "Synchronization has completed successfully";
                    //this.labelLastSyncValue.Text = GetLastSuccessfulSyncTime().ToString();
                    break;
            }
        }

        public void SyncCompletedCallback(IAsyncResult ar)
        {
            try
            {
                SqlCeReplication repl = (SqlCeReplication)ar.AsyncState;

                repl.EndSynchronize(ar);
                repl.SaveProperties();

                this.eventStatus = SyncStatus.SyncComplete;
            }
            catch (SqlCeException e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                // NOTE: If you want to set Control properties from within this 
                // method, you must use Control.Invoke method to marshal
                // the call to the UI thread; otherwise you might deadlock your 
                // application; See Control.Invoke documentation for more information
                //
                this.Invoke(this.myUserInterfaceUpdateEvent);
            }
        }

        public void OnStartTableUploadCallback(IAsyncResult ar, string tableName)
        {
            this.tableName = tableName;
            this.eventStatus = SyncStatus.BeginUpload;

            // NOTE: If you want to set Control properties from within this 
            // method, you must use Control.Invoke method to marshal
            // the call to the UI thread; otherwise you might deadlock your 
            // application; See Control.Invoke documentation for more information
            //
            this.Invoke(this.myUserInterfaceUpdateEvent);
        }

        public void OnSynchronizationCallback(IAsyncResult ar, int percentComplete)
        {
            this.percentage = percentComplete;
            this.eventStatus = SyncStatus.PercentComplete;

            // NOTE: If you want to set Control properties from within this 
            // method, you must use Control.Invoke method to marshal
            // the call to the UI thread; otherwise you might deadlock your 
            // application; See Control.Invoke documentation for more information
            //
            this.Invoke(this.myUserInterfaceUpdateEvent);
        }

        public void OnStartTableDownloadCallback(IAsyncResult ar, string tableName)
        {
            this.tableName = tableName;
            this.eventStatus = SyncStatus.BeginDownload;

            // NOTE: If you want to set Control properties from within this 
            // method, you must use Control.Invoke method to marshal
            // the call to the UI thread; otherwise you might deadlock your 
            // application; See Control.Invoke documentation for more information
            //
            this.Invoke(this.myUserInterfaceUpdateEvent);
        }

        private void ButtonSynchronize_Click(object sender, System.EventArgs e)
        {
            try
            {
                this.repl = new SqlCeReplication();
                repl.SubscriberConnectionString = "Data Source=Test.sdf";

                if (false == File.Exists("Test.sdf"))
                {
                    repl.AddSubscription(AddOption.CreateDatabase);
                    repl.PublisherSecurityMode = SecurityType.DBAuthentication;
                    repl.Publisher = "MyPublisher";
                    repl.PublisherLogin = "PublisherLogin";
                    repl.PublisherPassword = "<Password>";
                    repl.PublisherDatabase = "AdventureWorksDW";
                    repl.Publication = "AdventureWorksDW";
                    repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa35.dll";
                    repl.InternetLogin = "MyInternetLogin";
                    repl.InternetPassword = "<Password";
                    repl.Subscriber = "MySubscriber";
                }
                else
                {
                    repl.LoadProperties();
                }

                IAsyncResult ar = repl.BeginSynchronize(
                    new AsyncCallback(this.SyncCompletedCallback),
                    new OnStartTableUpload(this.OnStartTableUploadCallback),
                    new OnStartTableDownload(this.OnStartTableDownloadCallback),
                    new OnSynchronization(this.OnSynchronizationCallback), 
                    repl);
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public DateTime GetLastSuccessfulSyncTime()
        {
            DateTime localDateTime;
            SqlCeConnection conn = null;
            SqlCeCommand cmd = null;

            try
            {
                conn = new SqlCeConnection("Data Source = Test.sdf");
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT LastSuccessfulSync FROM __sysMergeSubscriptions " +
                    "WHERE Publication=@publication";

                cmd.Parameters.Add("@publication", SqlDbType.NVarChar, 4000);
                cmd.Parameters["@publication"].Value = "AdventureWorksDW";

//Note: LastSuccessfulSync is stored in local time, not UTC time
                localDateTime = (DateTime)cmd.ExecuteScalar();
                return localDateTime;
            }
            finally
            {
                conn.Close();
            }
        }
    }
Public Class MyForm
        Inherits Form

        Private myUserInterfaceUpdateEvent As EventHandler
        Private tableName As String
        Private percentage As Integer
        Private eventStatus As SyncStatus
        Private repl As SqlCeReplication

        Friend Enum SyncStatus
            PercentComplete
            BeginUpload
            BeginDownload
            SyncComplete
        End Enum 'SyncStatus

        Public Sub New()
            ' InitializeComponent();
            Me.myUserInterfaceUpdateEvent = New EventHandler(AddressOf UserInterfaceUpdateEvent)

        End Sub 'New

        Public Sub UserInterfaceUpdateEvent(ByVal sender As Object, ByVal e As System.EventArgs)
            Select Case Me.eventStatus
                Case SyncStatus.BeginUpload
                    'this.labelStatusValue.Text = "Began uploading table : " & tableName;

                Case SyncStatus.PercentComplete
                    'this.labelStatusValue.Text = "Sync with SQL Server is " & percentage.ToString() & "% complete.";

                Case SyncStatus.BeginDownload
                    'this.labelStatusValue.Text = "Began downloading table : " & tableName;

                Case SyncStatus.SyncComplete
                    'this.labelStatusValue.Text = "Synchronization has completed successfully";
                    'this.labelLastSyncValue.Text = GetLastSuccessfulSyncTime().ToString();
            End Select
        End Sub 'UserInterfaceUpdateEvent

        Public Sub SyncCompletedCallback(ByVal ar As IAsyncResult)
            Try
                Dim repl As SqlCeReplication = CType(ar.AsyncState, SqlCeReplication)

                repl.EndSynchronize(ar)
                repl.SaveProperties()

                Me.eventStatus = SyncStatus.SyncComplete
            Catch e As SqlCeException
                MessageBox.Show(e.Message)
            Finally
                ' NOTE: If you want to set Control properties from within this 
                ' method, you must use Control.Invoke method to marshal
                ' the call to the UI thread; otherwise you might deadlock your 
                ' application; See Control.Invoke documentation for more information
                '
                Me.Invoke(Me.myUserInterfaceUpdateEvent)
            End Try

        End Sub 'SyncCompletedCallback

        Public Sub OnStartTableUploadCallback(ByVal ar As IAsyncResult, ByVal tableName As String)
            Me.tableName = tableName
            Me.eventStatus = SyncStatus.BeginUpload

            ' NOTE: If you want to set Control properties from within this 
            ' method, you must use Control.Invoke method to marshal
            ' the call to the UI thread; otherwise you might deadlock your 
            ' application; See Control.Invoke documentation for more information
            '
            Me.Invoke(Me.myUserInterfaceUpdateEvent)

        End Sub 'OnStartTableUploadCallback

        Public Sub OnSynchronizationCallback(ByVal ar As IAsyncResult, ByVal percentComplete As Integer)
            Me.percentage = percentComplete
            Me.eventStatus = SyncStatus.PercentComplete

            ' NOTE: If you want to set Control properties from within this 
            ' method, you must use Control.Invoke method to marshal
            ' the call to the UI thread; otherwise you might deadlock your 
            ' application; See Control.Invoke documentation for more information
            '
            Me.Invoke(Me.myUserInterfaceUpdateEvent)

        End Sub 'OnSynchronizationCallback

        Public Sub OnStartTableDownloadCallback(ByVal ar As IAsyncResult, ByVal tableName As String)
            Me.tableName = tableName
            Me.eventStatus = SyncStatus.BeginDownload

            ' NOTE: If you want to set Control properties from within this 
            ' method, you must use Control.Invoke method to marshal
            ' the call to the UI thread; otherwise you might deadlock your 
            ' application; See Control.Invoke documentation for more information
            '
            Me.Invoke(Me.myUserInterfaceUpdateEvent)

        End Sub 'OnStartTableDownloadCallback

        Private Sub ButtonSynchronize_Click(ByVal sender As Object, ByVal e As System.EventArgs)
            Try
                Me.repl = New SqlCeReplication()
                repl.SubscriberConnectionString = "Data Source=Test.sdf"

                If False = File.Exists("Test.sdf") Then
                    repl.AddSubscription(AddOption.CreateDatabase)
                    repl.PublisherSecurityMode = SecurityType.DBAuthentication
                    repl.Publisher = "MyPublisher"
                    repl.PublisherLogin = "PublisherLogin"
                    repl.PublisherPassword = "<Password>"
                    repl.PublisherDatabase = "AdventureWorksDW"
                    repl.Publication = "AdventureWorksDW"
                    repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa35.dll"
                    repl.InternetLogin = "InternetLogin"
                    repl.InternetPassword = "<Password>"
                    repl.Subscriber = "MySubscriber"
                Else
                    repl.LoadProperties()
                End If

                Dim ar As IAsyncResult = repl.BeginSynchronize( _
                    New AsyncCallback(AddressOf Me.SyncCompletedCallback), _
                    New OnStartTableUpload(AddressOf Me.OnStartTableUploadCallback), _
                    New OnStartTableDownload(AddressOf Me.OnStartTableDownloadCallback), _
                    New OnSynchronization(AddressOf Me.OnSynchronizationCallback), repl)

            Catch ex As SqlCeException
                MessageBox.Show(ex.Message)
            End Try

        End Sub 'ButtonSynchronize_Click

        Public Function GetLastSuccessfulSyncTime() As DateTime
            Dim localDateTime As DateTime

            Dim conn As SqlCeConnection = Nothing
            Dim cmd As SqlCeCommand = Nothing

            Try
                conn = New SqlCeConnection("Data Source = Test.sdf")
                conn.Open()

                cmd = conn.CreateCommand()
                cmd.CommandText = "SELECT LastSuccessfulSync FROM __sysMergeSubscriptions " & _
                    "WHERE Publication=@publication"

                cmd.Parameters.Add("@publication", SqlDbType.NVarChar, 4000)
                cmd.Parameters("@publication").Value = "AdventureWorksDW"
'Note: LastSuccessfulSync is stored in local time, not UTC time
                localDateTime = CType(cmd.ExecuteScalar(), DateTime)

                Return localDateTime
            Finally
                conn.Close()
            End Try

        End Function 'GetLastSuccessfulSyncTime
    End Class 'MyForm

Concepts

Asynchronous Data Synchronization

Using Merge Replication