HOW TO: Program the SQL Snapshot and SQL Distribution Control with Visual C# .NET (319649)

SUMMARY

This step-by-step article demonstrates how to program the SQL Snapshot and SQL Distribution controls by using Microsoft Visual C# .NET.

You can use the SQL Server 2000 Replication ActiveX controls to embed replication functionality in custom applications.

back to the top

Prerequisites

Before you insert the sample code in a Microsoft Visual C# .NET project, follow these steps:

1. Make sure the publisher, distributor, and subscriber are configured properly and that they are all running SQL Server 2000.
2. Create a snapshot publication and name it SampleSnapshotPublication. The publishing database is Northwind.
3. After you create the publication, look at the publication properties of the publication.
4. Click the Subscription Options tab.
5. Click to select the Use a Distribution Agent that is independent of other publications from this database and Snapshot files are always available to initialize new subscriptions immediately check boxes.
6. Click OK to close the Publication Properties dialog box.

back to the top

Steps to Use Visual C# .NET to Program the SQL Snapshot and SQL Distribution Control

The sample code provided in this section generates a snapshot by using the SQL Snapshot Control. The SQL Distribution Control then creates a subscription database named Northwind_replica. Make sure that the Northwind_replica database does not exist before you run the sample code. The SQL Distribution Control creates a pull subscription in the Northwind_replica database and applies the snapshot at the subscriber.

Inside the Visual C# .NET project, add references to both:

• Microsoft SQL Snapshot Control 8.0.
• Microsoft SQL Distribution Control 8.0 COM objects.

Next, insert this code in the C# class file for your project:

using System;
using System.Runtime.InteropServices;
using SQLINITXLib;
using SQLDISTXLib;

namespace SqlRepl
{
	//This class demonstrates use of the SQL Server Snapshot and Distribution Agent replication controls. 
	class SnapshotApp
	{
		/*	Prior to running this code, you must set up replication as follows:
		// 	
		//		Create a snapshot publication named "SampleSnapshotPublication" and configure it to allow pull
		//		subscriptions. 
		//		
		//		You must also set the following subscription options for the publication:
		//			-Use a Distribution Agent that is independent of other publications from this database. 
		//			-Snapshot files are always available to initialize new subscriptions. 
		//	
		//	This code first generates the snapshot by using the SQLSnapshotClass object. The subscription database
		//	and pull subscription are then created through code. Next, the snapshot is applied at the subscriber by using
		//	the SQLDistributionClass object. 
		// 
		//	Set references to the following COM DLL files:
		//		-Microsoft SQL Snapshot Control 8.0
		//		-Microsoft SQL Distribution Control 8.0		 
		*/ 

		[STAThread]
		static void Main(string[] args)
		{
			string strPublisher, strDistributor, strSubscriber, strPublisherDatabase, strSubscriberDatabase, strPublication;
			strPublisher = "PUBLISHER";		//change to the name of your publisher
			strDistributor = "DISTRIBUTOR";	//change to the name of your distributor
			strSubscriber = "SUBSCRIBER";		//change to the name of your subscriber
			strPublication = "SampleSnapshotPublication";
			strPublisherDatabase = "Northwind";
			strSubscriberDatabase = "Northwind_replica";

			SQLSnapshotClass oSnap = new SQLSnapshotClass();
			
			//Set up the publisher.
			oSnap.Publisher = strPublisher;
			oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			oSnap.PublisherDatabase = strPublisherDatabase;
			oSnap.Publication = strPublication;

			//Set up the distributor.
			oSnap.Distributor = strDistributor;
			oSnap.DistributorSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			
			//Generate the snapshot.
			try
			{
				Console.WriteLine("Generating snapshot...");
				oSnap.Initialize();
				oSnap.Run();
				oSnap.Terminate();
				Console.WriteLine("Snapshot generated.");
			}
			catch (Exception e)
			{
				Console.WriteLine(e.StackTrace);
				Console.WriteLine(e.Message);
			}
			
			//Now that the snapshot is created, use the Distribution Agent control to apply the snapshot at the subscriber. 

			SQLDistributionClass oDist = new SQLDistributionClass();
			
			//Set up the publisher.
			oDist.Publisher = strPublisher;
			oDist.PublisherSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			oDist.PublisherDatabase = strPublisherDatabase;
			oDist.Publication = strPublication;

			//Set up the distributor.
			oDist.Distributor = strDistributor;
			oDist.DistributorSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION;
			
			//Set up the subscriber.
			oDist.Subscriber = strSubscriber;
			oDist.SubscriberDatabase = strSubscriberDatabase;
			oDist.SubscriberSecurityMode = SQLDISTXLib.SECURITY_TYPE.NT_AUTHENTICATION;

			//Set up the subscription.
			oDist.SubscriptionType = SQLDISTXLib.SUBSCRIPTION_TYPE.PULL;
			oDist.SynchronizationType = SQLDISTXLib.SYNCHRONIZATION_TYPE.AUTOMATIC;
			oDist.SubscriptionName = "PullSnapshotSubscription";
			
			//Create the database and subscription.
			oDist.AddSubscription(SQLDISTXLib.DBADDOPTION.CREATE_DATABASE, SQLDISTXLib.SUBSCRIPTION_HOST.NONE);
			
			//Synchronize the subscription.
			try
			{
				Console.WriteLine("Running distribution agent...");
				oDist.Initialize();
				oDist.Run();
				oDist.Terminate();
				Console.WriteLine("Distribution agent completed.");
			}
			catch (Exception e)
			{
				Console.WriteLine(e.StackTrace);
				Console.WriteLine(e.Message);
			}

			
		
		}
	}
}
				

back to the top