Dev Direct Solution Center

How to automatically synchronize two database schemas from within an application

Download the complete source code for this solution.

 Note: This solution requires the component to be installed first.
To download the installer, click here

Introduction

The following sample shows how the Database Restyle - Library can be used to automate several changes to a database schema from within an appication. In this simple example we synchoronize two databases by adding two new tables from one database to another.

Detail

Our demonstration code illustrates how we start with an initial database schema and then make several schema changes. The changes take the form of the addition of two new database tables.

Set up

Download and unzip the sample code from the above link. There is an example project for C# and an SQL script that can be used to create the two initial tables in the source database.

In this example MS SQL Server 2005 is used to store the data. Start by creating the two new databases - firmwareDevelop and firmware. Now run the SQL script supplied (firmwareDevelop_setup.sql) against the firmwareDevelop database to create two new tables for the example as shown below.

The sample code includes a simple WinForms application called FirmwareApp.

The example includes all the code necessary to generate the SQL scripts required to synchronize the target and source database. It also shows how to perform the synchronisation.

Generating the SQL Script

Clicking the "Show Scripts" button will call the following method to generate and show the SQL script. The method creates the connection strings for the source and target databases from the values entered into the application. We can then call the database Compare method and compare the schema for our source and target databases using the ReverseDatabase method. The output from the compare is then read and displayed in our application text box.

C#
    private void showScriptsButton_Click(object sender, EventArgs e)
    {
      //Create source and target database connection strings
      string sourceConnectionString =
        GetConnectionString(sourceServerTextBox.Text, sourceDbTextBox.Text);
      string targetConnectionString =
        GetConnectionString(targetServerTextBox.Text, targetDbTextBox.Text);
      logTextBox.Clear();
      try
      {
        StringBuilder stringBuilder = new StringBuilder();
        //Call Database compare method to compare schema
        DatabaseSynchronizer dbSync = databaseSync.Compare(
          databaseSync.ReverseDatabase(sourceConnectionString),
          databaseSync.ReverseDatabase(targetConnectionString));
        //loop around the generated script and add to the application text box
        foreach (Script script in dbSync.Scripts)
        {
          stringBuilder.AppendLine(script.GetText());
          stringBuilder.AppendLine();
        }
        logTextBox.Text = stringBuilder.ToString();
      }
      catch (Exception ex)
      {
        logTextBox.Text = ex.Message;
      }
    }

The application shows what scripts need to be executed in order to synchronize firmwareDevelop with firmware. As firmware is initially empty, synchronization results in the creation of two tables plus their primary and external keys.

Synchronizing the Database Schema

Clicking the "Synchronize" button will call the following routine to synchronize the source and target database schema. This time we setup the target database connection string, open a connection to the target database and begin a transaction. We then create a new object, using the ReverseDatabase method, which contains the source database schema and then use the UpdateDatabase method to compare the source database schema and make appropriate schema changes to the target database. The transaction is then committed and the changes made.

C#
    private void synchronizeButton_Click(object sender, EventArgs e)
    {
      log = new StringBuilder();
      try
      {
        //Create database target connection string
        targetConnection = new SqlConnection(
          GetConnectionString(targetServerTextBox.Text, targetDbTextBox.Text));
        //Create a connection to the target database
        targetConnection.Open();
        //Start a transaction on the target database
        targetTransaction = targetConnection.BeginTransaction();

        try
        {
          //using RevereseDatabase obtain source database schema
          Database database = databaseSync.ReverseDatabase(
            GetConnectionString(sourceServerTextBox.Text, sourceDbTextBox.Text));
          //update the target database schema
          databaseSync.UpdateDatabase(database, targetConnection,
            targetTransaction);

          //Commit changes to the target database
          targetTransaction.Commit();
          logTextBox.Text = log.ToString();
        }
        catch (Exception ex)
        {
          targetTransaction.Rollback();
          logTextBox.Text = log.ToString();
          logTextBox.Text += ex.Message;
        }
        finally
        {
          targetConnection.Close();
        }
      }
      catch (Exception ex)
      {
        logTextBox.Text = log.ToString();
        logTextBox.Text += ex.Message;
      }
    }

Conclusion

This solution demonstrates how we can synchronize two database schema very easily using Perpetuum's Database Restyle - Library. Database Restyle is a component which can be integrated directly into your application. No additional software is required on the client side as synchronization is performed by the component. You can distribute the component within your application without any additional fees regardless of the application type (WinForms, ASP.NET, Console, WPF, etc.). Database structure is saved as an XML file and can be distributed in any form (file, resource, etc.).

This example shows just the most basic capabilities of Database Restyle and there are many more functions available for performing more advanced operations.