C# Replication

I have a c# application that runs off a local database. This application is
used by marketers to keep track of their leads and appointments. Currently
two marketers are using this application on laptops. Obvisouly the two
marketers will have different data on their laptops. They need to share the
same data and the only way to do this is through merging the databases.
I thought about merging the database on the laptop with a database on our
local server, and then the server and laptop will be updated with the latest
information.
I need to do this through c# and I'm having trouble setting up sql to make
use of merging.
Please help me on ideas or how to go about this.
ThanxI take it you are talking about merge replication. Use the Replication
Wizard to create a merge publication. You can then use the merge replication
ActiveX control to pull this
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Xml;
using System.Net;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using SQLINITXLib;
using SQLMERGXLib;
namespace MergeDemo1
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
public System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.DataGrid dataGrid2;
private SqlDataAdapter SQLDataAdapterPub;
private SqlDataAdapter SQLDataAdapterSub;
private DataSet DataSetPub=new DataSet();
private DataSet DataSetSub=new DataSet();
public event _SQLMergeEvents_StatusEventHandler StatusEventMerge;
public event _SQLSnapshotEvents_StatusEventHandler StatusEventSnapshot;
private System.Windows.Forms.Button UpdatePublisher;
private System.Windows.Forms.Button UpdateSubscriber;
private System.Windows.Forms.Button CloseButton;
private System.Windows.Forms.Label SubscriberConflicts;
private System.Windows.Forms.Label SubscriberChanges;
private System.Windows.Forms.Label PublisherChanges;
private System.Windows.Forms.Label PublisherConflicts;
private System.Windows.Forms.Button Sync;
private System.Windows.Forms.ProgressBar progressBar1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.UpdatePublisher = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.CloseButton = new System.Windows.Forms.Button();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.dataGrid2 = new System.Windows.Forms.DataGrid();
this.UpdateSubscriber = new System.Windows.Forms.Button();
this.Sync = new System.Windows.Forms.Button();
this.SubscriberConflicts = new System.Windows.Forms.Label();
this.SubscriberChanges = new System.Windows.Forms.Label();
this.PublisherChanges = new System.Windows.Forms.Label();
this.PublisherConflicts = new System.Windows.Forms.Label();
this.progressBar1 = new System.Windows.Forms.ProgressBar();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(24, 16);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(144, 48);
this.button1.TabIndex = 0;
this.button1.Text = "Click to generate and distribute Snapshot";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(48, 168);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(832, 272);
this.dataGrid1.TabIndex = 2;
//
// UpdatePublisher
//
this.UpdatePublisher.Location = new System.Drawing.Point(208, 16);
this.UpdatePublisher.Name = "UpdatePublisher";
this.UpdatePublisher.Size = new System.Drawing.Size(152, 48);
this.UpdatePublisher.TabIndex = 3;
this.UpdatePublisher.Text = "Click to Update Publisher";
this.UpdatePublisher.Visible = false;
this.UpdatePublisher.Click += new
System.EventHandler(this.UpdatePublisher_Click);
//
// label1
//
this.label1.Location = new System.Drawing.Point(112, 128);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(760, 32);
this.label1.TabIndex = 4;
this.label1.Text = "label1";
this.label1.Visible = false;
//
// CloseButton
//
this.CloseButton.Location = new System.Drawing.Point(592, 16);
this.CloseButton.Name = "CloseButton";
this.CloseButton.Size = new System.Drawing.Size(152, 48);
this.CloseButton.TabIndex = 5;
this.CloseButton.Text = "Close";
this.CloseButton.Click += new System.EventHandler(this.Close_Click);
//
// label2
//
this.label2.Location = new System.Drawing.Point(48, 128);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(56, 32);
this.label2.TabIndex = 6;
this.label2.Text = "Publisher";
//
// label3
//
this.label3.Location = new System.Drawing.Point(48, 472);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(64, 32);
this.label3.TabIndex = 8;
this.label3.Text = "Subscriber";
//
// dataGrid2
//
this.dataGrid2.DataMember = "";
this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid2.Location = new System.Drawing.Point(48, 512);
this.dataGrid2.Name = "dataGrid2";
this.dataGrid2.Size = new System.Drawing.Size(832, 272);
this.dataGrid2.TabIndex = 7;
//
// UpdateSubscriber
//
this.UpdateSubscriber.Location = new System.Drawing.Point(208, 456);
this.UpdateSubscriber.Name = "UpdateSubscriber";
this.UpdateSubscriber.Size = new System.Drawing.Size(152, 48);
this.UpdateSubscriber.TabIndex = 9;
this.UpdateSubscriber.Text = "Click to Update Subscriber";
this.UpdateSubscriber.Visible = false;
this.UpdateSubscriber.Click += new
System.EventHandler(this.UpdateSubscriber_Click);
//
// Sync
//
this.Sync.Location = new System.Drawing.Point(400, 16);
this.Sync.Name = "Sync";
this.Sync.Size = new System.Drawing.Size(152, 48);
this.Sync.TabIndex = 10;
this.Sync.Text = "Synchronize";
this.Sync.Visible = false;
this.Sync.Click += new System.EventHandler(this.Sync_Click);
//
// SubscriberConflicts
//
this.SubscriberConflicts.Location = new System.Drawing.Point(376, 456);
this.SubscriberConflicts.Name = "SubscriberConflicts";
this.SubscriberConflicts.Size = new System.Drawing.Size(144, 24);
this.SubscriberConflicts.TabIndex = 11;
this.SubscriberConflicts.Text = "Subscriber Conflicts: 0";
//
// SubscriberChanges
//
this.SubscriberChanges.Location = new System.Drawing.Point(512, 456);
this.SubscriberChanges.Name = "SubscriberChanges";
this.SubscriberChanges.Size = new System.Drawing.Size(144, 24);
this.SubscriberChanges.TabIndex = 12;
this.SubscriberChanges.Text = "Subscriber Changes: 0";
//
// PublisherChanges
//
this.PublisherChanges.Location = new System.Drawing.Point(512, 80);
this.PublisherChanges.Name = "PublisherChanges";
this.PublisherChanges.Size = new System.Drawing.Size(144, 24);
this.PublisherChanges.TabIndex = 14;
this.PublisherChanges.Text = "Publisher Changes: 0";
//
// PublisherConflicts
//
this.PublisherConflicts.Location = new System.Drawing.Point(376, 80);
this.PublisherConflicts.Name = "PublisherConflicts";
this.PublisherConflicts.Size = new System.Drawing.Size(144, 24);
this.PublisherConflicts.TabIndex = 13;
this.PublisherConflicts.Text = "Publisher Conflicts: 0";
//
// progressBar1
//
this.progressBar1.Location = new System.Drawing.Point(112, 152);
this.progressBar1.Name = "progressBar1";
this.progressBar1.Size = new System.Drawing.Size(168, 8);
this.progressBar1.TabIndex = 15;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(896, 853);
this.Controls.Add(this.progressBar1);
this.Controls.Add(this.PublisherChanges);
this.Controls.Add(this.PublisherConflicts);
this.Controls.Add(this.SubscriberChanges);
this.Controls.Add(this.SubscriberConflicts);
this.Controls.Add(this.Sync);
this.Controls.Add(this.UpdateSubscriber);
this.Controls.Add(this.label3);
this.Controls.Add(this.dataGrid2);
this.Controls.Add(this.label2);
this.Controls.Add(this.CloseButton);
this.Controls.Add(this.label1);
this.Controls.Add(this.UpdatePublisher);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Merge Demo";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e)
{}
private void Synchronize()
{
//run snapshot
//synchronize mergesub
Snapshot();
Synchronzie_mergesub();
}
private void Snapshot()
{
SQLSnapshot objSnapshot= new SQLSnapshotClass();
objSnapshot.Publisher = System.Environment.MachineName.ToString();
objSnapshot.PublisherSecurityMode =
SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION;
objSnapshot.PublisherDatabase = "Northwind";
objSnapshot.Publication = "Northwind";
objSnapshot.ReplicationType=SQLINITXLib.REPLICATION_TYPE.MERGE;
objSnapshot.Distributor = System.Environment.MachineName.ToString();
objSnapshot.DistributorSecurityMode =
SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION;
objSnapshot.Status +=new
_SQLSnapshotEvents_StatusEventHandler(ob
jSnapshot_Status);
try
{
button1.Visible=false;
label1.Visible=true;
label1.Text = "Generating snapshot...";
label1.Refresh();
objSnapshot.Initialize();
objSnapshot.Run();
objSnapshot.Terminate();
label1.Text = "Snapshot generated...";
label1.Refresh();
}
catch (Exception e)
{
MessageBox.Show(e.StackTrace);
MessageBox.Show(e.Message);
}
}
private void Synchronzie_mergesub()
{
SQLMergeClass objMerge = new SQLMergeClass();
objMerge.Publisher=System.Environment.MachineName.ToString();
objMerge.Publisher=Dns.GetHostName();
objMerge.PublisherDatabase="Northwind";
objMerge.Publication="Northwind";
objMerge.PublisherSecurityMode=SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
objMerge.Subscriber=System.Environment.MachineName.ToString();
objMerge.SubscriberDatabase="mergesub";
objMerge.SubscriberSecurityMode=SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
objMerge.Status +=new
_SQLMergeEvents_StatusEventHandler(objMe
rge_Status);
try
{
label1.Visible=true;
label1.Text="Initializing Merge Sub";
label1.Refresh();
objMerge.Initialize();
objMerge.Run();
label1.Text="Running Merge Agent";
label1.Refresh();
objMerge.Terminate();
label1.Text="Merge Agent complete";
label1.Refresh();
}
catch(Exception e)
{
MessageBox.Show(e.StackTrace);
MessageBox.Show(e.Message);
}
PublisherConflicts.Text="Publisher Conflicts: " +
objMerge.PublisherConflicts.ToString();
PublisherConflicts.Update();
PublisherChanges.Text="Publisher Changes: " +
objMerge.PublisherChanges.ToString();
PublisherChanges.Update();
SubscriberConflicts.Text="Subscriber Conflicts: " +
objMerge.SubscriberConflicts.ToString();
SubscriberConflicts.Update();
SubscriberChanges.Text="Subscriber Changes: " +
objMerge.SubscriberChanges.ToString();
SubscriberChanges.Update();
}
public void button1_Click(object sender, System.EventArgs e)
{
Synchronize();
PopulateDataGrids();
}
public void PopulateDataGrids()
{
SqlConnection objConnectionPub = null;
SqlConnection objConnectionSub = null;
try
{
objConnectionPub = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=Northwind");
objConnectionSub = new
SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial
Catalog=mergesub");
//SQLDataAdapaterPub(
this.SQLDataAdapterPub= new SqlDataAdapter("Select * from
Customers",objConnectionPub);
this.SQLDataAdapterSub= new SqlDataAdapter("Select * from
Customers",objConnectionSub);
DataSetPub.Clear();
DataSetSub.Clear();
SQLDataAdapterPub.Fill(DataSetPub, "Customers");
SQLDataAdapterSub.Fill(DataSetSub, "Customers");
dataGrid1.DataSource = DataSetPub.DefaultViewManager;
dataGrid1.DataMember="Customers";
dataGrid2.DataSource = DataSetSub.DefaultViewManager;
dataGrid2.DataMember="Customers";
UpdatePublisher.Visible=true;
UpdateSubscriber.Visible=true;
SqlCommandBuilder commandBuilderPub = new
SqlCommandBuilder(SQLDataAdapterPub);
SqlCommandBuilder commandBuilderSub = new
SqlCommandBuilder(SQLDataAdapterSub);
}
catch (Exception oE)
{
MessageBox.Show("Problem Populating DataGrid: [" + oE.ToString() + "]");
}
finally
{
if (objConnectionPub!= null )
{
if (objConnectionPub.State == ConnectionState.Open)
objConnectionPub.Close();
}
if (objConnectionSub!= null )
{
if (objConnectionSub.State == ConnectionState.Open)
objConnectionSub.Close();
}
}
}
private void Close_Click(object sender, System.EventArgs e)
{
this.Close();
}
public void UpdatePublisher_Click(object sender, System.EventArgs e)
{
UpdateSubscriber.Visible=true;
if(DataSetPub!= null && DataSetPub.HasChanges())
{
SQLDataAdapterPub.Update(DataSetPub, "Customers");
}
Sync.Visible=true;
}
public SQLINITXLib.STATUS_RETURN_CODE objSnapshot_Status(string Message,
int Percent)
{
label1.Text=Message.ToString() +" " +Percent.ToString() + "% complete.";
label1.Update();
progressBar1.Value=Percent;
progressBar1.Update();
StatusEventSnapshot(Message, Percent);
return new SQLINITXLib.STATUS_RETURN_CODE ();
}
public SQLMERGXLib.STATUS_RETURN_CODE objMerge_Status(string Message, int
Percent)
{
label1.Text=Message.ToString() +" " +Percent.ToString() + "% complete.";
label1.Update();
progressBar1.Value=Percent;
progressBar1.Update();
StatusEventMerge(Message, Percent);
return new SQLMERGXLib.STATUS_RETURN_CODE ();
}
private void UpdateSubscriber_Click(object sender, System.EventArgs e)
{
UpdateSubscriber.Visible=true;
if(DataSetSub!= null && DataSetSub.HasChanges())
{
SQLDataAdapterSub.Update(DataSetSub, "Customers");
}
Sync.Visible=true;
}
private void Sync_Click(object sender, System.EventArgs e)
{
Synchronzie_mergesub();
PopulateDataGrids();
dataGrid1.Update();
}
}
}
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Developer" <Developer@.discussions.microsoft.com> wrote in message
news:3E371B74-9DC6-458D-9169-419D741CD547@.microsoft.com...
> I have a c# application that runs off a local database. This application
is
> used by marketers to keep track of their leads and appointments. Currently
> two marketers are using this application on laptops. Obvisouly the two
> marketers will have different data on their laptops. They need to share
the
> same data and the only way to do this is through merging the databases.
> I thought about merging the database on the laptop with a database on our
> local server, and then the server and laptop will be updated with the
latest
> information.
> I need to do this through c# and I'm having trouble setting up sql to make
> use of merging.
> Please help me on ideas or how to go about this.
> Thanx|||The following article describes how to use offline MSDE installations as
merge replication subscribers to an online SQL Server acting as a publisher.
http://support.microsoft.com/defaul...kb;en-us;324992
"Developer" <Developer@.discussions.microsoft.com> wrote in message
news:3E371B74-9DC6-458D-9169-419D741CD547@.microsoft.com...
>I have a c# application that runs off a local database. This application is
> used by marketers to keep track of their leads and appointments. Currently
> two marketers are using this application on laptops. Obvisouly the two
> marketers will have different data on their laptops. They need to share
> the
> same data and the only way to do this is through merging the databases.
> I thought about merging the database on the laptop with a database on our
> local server, and then the server and laptop will be updated with the
> latest
> information.
> I need to do this through c# and I'm having trouble setting up sql to make
> use of merging.
> Please help me on ideas or how to go about this.
> Thanx