How to: Create a SQL Server Compact Edition Subscription (Programmatically)

In this topic, you will learn how to create a subscription on Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) by using the SqlCeReplication class, and then perform the initial data synchronization.

To create a SQL Server Compact Edition subscription and synchronize data

1. Initialize a SqlCeReplication object.

   SqlCeReplication repl = new SqlCeReplication();
   

2. Set the connection properties. These properties specify the name and location of the Publication to which you are subscribing, the name and location of the local SQL Server Compact Edition database, and the location of the SQL Server Compact Edition Server Agent.

   repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa30.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 AddSubscription method to create the subscription.

   repl.AddSubscription(AddOption.CreateDatabase);
   

4. Call the Synchronize method to perform data synchronization and populate the local database with the data from the Publication.

   repl.Synchronize();
   

Example

This example shows how to create a subscription and synchronize data with SQL Server. In this example, the local database is named MyDatabase.sdf and the Publication is named MyPublication.

SqlCeReplication repl = null;

try
{
   // Instantiate and configure SqlCeReplication object
   //
   repl = new SqlCeReplication();
   repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa30.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";

   // Create a local database subscription
   //
   repl.AddSubscription(AddOption.CreateDatabase);

   // Synchronize to the SQL Server database
   //
   repl.Synchronize();
}
catch (SqlCeException)
{
   // Handle errors here
   //
}
finally
{
   // Dispose the repl object
   //
   repl.Dispose();
}

Dim repl As SqlCeReplication = Nothing

Try
   ' Instantiate and configure SqlCeReplication object

   repl = New SqlCeReplication()
   repl.InternetUrl = "https://www.adventure-works.com/sqlmobile/sqlcesa30.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"

   ' Create the local SQL Server Database subscription
   
   repl.AddSubscription(AddOption.CreateDatabase)

   ' Synchronize to SQL Server to populate the Subscription 

   repl.Synchronize()
Catch
   ' Handle errors here
   '
Finally
   ' Dispose the repl object
   '
   repl.Dispose()
End Try

   ISSCEMerge      *pISSCEMerge = NULL;
   ISSCEErrors  *pISSCEErrors = NULL;
   HRESULT          hr;
   BSTR            bstr = NULL;
   BOOL            fInitialized = FALSE;
   LONG            lPubChanges;
   LONG            lPubConflicts;
   LONG            lSubChanges;

   /* Create the Replication object. */
   CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER,
      IID_ISSCEMerge, (LPVOID *) &pISSCEMerge);
   
  /* Set Internet properties. */
   bstr = SysAllocString
     (L"https://www.adventure-works.com/sqlce/sqlcesa30.dll");
   pISSCEMerge->put_InternetURL(bstr);
   SysFreeString(bstr);

   bstr = SysAllocString(L"MyInternetLogin");
   pISSCEMerge->put_InternetLogin(bstr);
   SysFreeString(bstr);

   bstr = SysAllocString(L"<MyInternetPassword>");
   pISSCEMerge->put_InternetPassword(bstr);
   SysFreeString(bstr);
   
   /* Set Publisher properties */
   bstr = SysAllocString(L"SamplePublisher");
   pISSCEMerge->put_Publisher(bstr);
   SysFreeString(bstr);

   bstr = SysAllocString(L"AdventureWorks_SQLCE");
   pISSCEMerge->put_PublisherDatabase(bstr);
   SysFreeString(bstr);

   bstr = SysAllocString(L"SQLCEReplDemo");
   pISSCEMerge->put_Publication(bstr);
   SysFreeString(bstr);

   pISSCEMerge->put_PublisherSecurityMode(NT_AUTHENTICATION);

   /* Set Subscriber properties. */
   bstr = SysAllocString(L"Data Source=\\ssce.sdf");
   pISSCEMerge->put_SubscriberConnectionString(bstr);
   SysFreeString(bstr);

   bstr = SysAllocString(L"SQLCE Sub #1");
   pISSCEMerge->put_Subscriber(bstr);
   SysFreeString(bstr);

   /* Create the new anonymous subscription. */
   pISSCEMerge->AddSubscription(CREATE_DATABASE);

   /* Perform the first synchronization to download the initial  
          replica. */
   hr = pISSCEMerge->Initialize();
   if (SUCCEEDED(hr))
      {
      fInitialized = TRUE;
      hr = pISSCEMerge->Run();
      }

   if (SUCCEEDED(hr))
      {
      pISSCEMerge->get_PublisherChanges(&lPubChanges);
      pISSCEMerge->get_PublisherConflicts(&lPubConflicts);
      pISSCEMerge->get_SubscriberChanges(&lSubChanges);
      }
   else
      {
   if(SUCCEEDED(hr = pISSCEMerge->get_ErrorRecords(&pISSCEErrors)))
      {
      ShowErrors(pISSCEErrors);
      pISSCEErrors->Release();
      };
      }

   if (fInitialized)
      {
      (void)pISSCEMerge->Terminate();

See Also

Reference

System.Data.SqlServerCe Namespace Overview

Other Resources

Using Merge Replication
Subscribing to Publications (SQL Server Compact Edition)
Creating a Subscription

Help and Information

Getting SQL Server Compact Edition Assistance