Why do I have to duplicate my code to handle two synced MSSQL databases

Question

I'm writing an application, with SQL server connection. This connection is not very stable, so I decided to use Microsoft Sync Framework, which is pretty good. My problem is, that I have to use twice as much dataset&tableadapter, plus I have to change all winform datasource (eg.: a combobox), when I switch between the local and server database.

What I want to achieve is to switch between the two database easily. I have a fully working code, but I think it could be more better.

Initialization code:

int timeout = 5;
public Form1()
{
    Program.ChangeConnectionString("TESTDBConnectionString", "Data Source=ip\\engine;Initial Catalog=TESTDB;Persist Security Info=True;User ID=usr;Password=psw; Connect Timeout = " + timeout);
    Program.ChangeConnectionString("localConnectionString", "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = \\local.mdf; Integrated Security = True; Connect Timeout = " + timeout);
    InitializeComponent();
}

Update (and connection check) code:

private void UpdateDB()
{
    CheckServer();
    this.tbl_SyncTestTableAdapter1.Fill(this.localDataSet1.tbl_SyncTest);
    if (Program.IsOnline)
    {
        this.tbl_SyncTestTableAdapter.Fill(this.tESTDBDataSet.tbl_SyncTest);
        tblSyncTestBindingSource.DataSource = tESTDBDataSet.tbl_SyncTest;
        tblSyncTestBindingSource1.DataSource = tESTDBDataSet.tbl_SyncTest;
        tblSyncTestBindingSource2.DataSource = tESTDBDataSet.tbl_SyncTest;
        tblSyncTestBindingSource4.DataSource = tESTDBDataSet.tbl_SyncTest;
        dataGridView2.Visible = true;
        Sync();
    }
    else
    {
        tblSyncTestBindingSource.DataSource = localDataSet1.tbl_SyncTest;
        tblSyncTestBindingSource1.DataSource = localDataSet1.tbl_SyncTest;
        tblSyncTestBindingSource2.DataSource = localDataSet1.tbl_SyncTest;
        tblSyncTestBindingSource4.DataSource = localDataSet1.tbl_SyncTest;
        dataGridView2.Visible = false;
        label8.Text = "OFFLINE";
    }
}

Sample database action:

private void button3_Click(object sender, EventArgs e)
{
    string[] val = new string[] { comboBox2.Text, textBox5.Text, textBox4.Text };
    int ival = (int)comboBox2.SelectedValue;
    CheckServer();
    if (Program.IsOnline) tbl_SyncTestTableAdapter.Update(val[0], val[1], val[2], ival);
    else tbl_SyncTestTableAdapter1.Update(val[0], val[1], val[2], ival);
    UpdateDB();
}

If you need more code, let me know, but I think the others are irrevelant.

EDIT, Sync code:

private void Sync()
{
    label8.Text = "SYNCING...";
    new Task(() =>
    {
        ProvisionServer();
        ProvisionLocal();
        SqlConnection serverConn = new SqlConnection(Properties.Settings.Default["TESTDBConnectionString"].ToString());
        SqlConnection localConn = new SqlConnection(Properties.Settings.Default["localConnectionString"].ToString());
        SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
        syncOrchestrator.LocalProvider = new SqlSyncProvider("TestScope", localConn);
        syncOrchestrator.RemoteProvider = new SqlSyncProvider("TestScope", serverConn);
        syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
        SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
        Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
        Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
        Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
        Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
        Console.WriteLine(String.Empty);
        this.BeginInvoke((Action)(() =>
        {
            label8.Text = "SYNCED";
            this.tbl_SyncTestTableAdapter.Fill(this.tESTDBDataSet.tbl_SyncTest);
            this.tbl_SyncTestTableAdapter1.Fill(this.localDataSet1.tbl_SyncTest);
        }));
    }).Start();
}

Answer 1

The DataAdapter pattern exist so you can Adapt for different scenario's without having to implement/duplicate the same logic throughout your app. In your case you're only interested in choosing the correct Adapter implementation.

If that is your only Form you could introduce a property that gets the apropiate DataAdapter and use that throughout.

// Adapter will give you an interface
// to either your local store
// or your online store
private IDataAdapter Adapter 
{
   get 
   {
       IDataAdapter _adapter = null;
       CheckServer();
       if (Program.IsOnline) 
       {
            _adapter = new SqlDataAdapter(selectcommand, onlineconnection);
       }
       else
       {
            _adapter = new  SqlCeDataAdapter(selectCommand, offlineConnection);
       }
       return _adapter
   }   
} 

Your other methods can deal with just a single dataset:

private void UpdateDB()
{
    this.Adapter.Fill(this.localDataSet1);
    // you don't need to change any bindings ...
}

And your db action:

private void button3_Click(object sender, EventArgs e)
{
    string[] val = new string[] { comboBox2.Text, textBox5.Text, textBox4.Text };
    // if the Adapter has changed here
    // the dataset might hold different states
    // so it might hold on Update where an Insert is needed
    // in that case use some logic to Fill a new Dataset and merge that
    // with the current one
    this.Adapter.Update(this.localDataSet1); // the values from the dataset will be stored.
}

But now that you have revealed your synchronization code, why don't you simply always connect to your local database and then let the sync framework deal with the updates/inserts needed both locally and in the central db. Bringing the logic for online and offline scenario there where you choose the DataAdapter kind of defeats the purpose of the sync framework in the first place.