Download WPF_SQL_Replication_Tool.zip - 131.6 KB

1. Introduction

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Although SQL Server Replica Server does a very good job, we do need a customized tool, especially for large database.

SQL Server Management Objects (SMO) is objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications.

Database replication includes database objects and data. We can copy database objects and insert data directly between two SQL servers. But it’s not flexible. The best way is generating SQL creating scripts for database objects and insert scripts for data.

2. Scripting Database Schema Objects

2.1 Reference SMO Assemblies

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll

2.2 Connect to Source Database and Create Scripter Instance

string connStr;

SqlConnection connection = new SqlConnection(connStr);ServerConnection sc = new ServerConnection(connection);
Server s = new Server(sc);

s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(Table), "IsSystemObject");
s.SetDefaultInitFields(typeof(View), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "IsEncrypted");
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
Scripter scripter = new Scripter(s);

2.3 Generate Database Creation script

ScriptingOptions options = new ScriptingOptions();

options.DriAll = true;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.AppendToFile = false;

options.ToFileOnly = true;
options.WithDependencies = false;
options.ContinueScriptingOnError = true;
scripter.Options = options;

options.IncludeIfNotExists = true;
options.ScriptDrops = true;
options.FileName = dbCreateFile;
scripter.Script(new Database[] { db });

options.IncludeIfNotExists = false;
options.ScriptDrops = false;
options.AppendToFile = true;
scripter.Script(new Database[] { db });

2.4 Script Objects

     // Script schemas
            if (db.Schemas.Count > 0)
            {
                List<Schema> schemas = new List<Schema>();
                foreach (Schema schema in db.Schemas)
                {
                    if (schema.IsSystemObject)
                        continue;
                    schemas.Add(schema);
                }
                options.IncludeIfNotExists = true;
                options.ScriptDrops = true;
                scripter.Script(schemas.ToArray());
                options.IncludeIfNotExists = false;
                options.ScriptDrops = false;
                scripter.Script(schemas.ToArray());
            }
            //Script tables
            if (db.Tables.Count > 0)
            {
                List<Table> tbls = new List<Table>();
                foreach (Table t in db.Tables)
                {
                    if (t.IsSystemObject)
                        continue;
                    tbls.Add(t);
                }
                DependencyTree tree = scripter.DiscoverDependencies(tbls.ToArray(), true);
                DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
                DependencyCollection depcoll = depwalker.WalkDependencies(tree);
                tbls.Clear();
                foreach (DependencyCollectionNode dep in depcoll)
                {
                    if (dep.Urn.Type != "Table")
                        continue;
                    string tName = dep.Urn.GetAttribute("Name");
                    string schema = dep.Urn.GetAttribute("Schema");
                    var tbl = db.Tables[tName, schema];
                    if (tbl == null)
                        continue;
                    tbls.Add(tbl);
                }
                options.IncludeIfNotExists = true;
                options.SchemaQualifyForeignKeysReferences = true;
                options.ScriptDrops = true;
                scripter.Script(tbls.ToArray());

                options.AppendToFile = true;
                options.IncludeIfNotExists = false;
                options.ScriptDrops = false;
                scripter.Script(tbls.ToArray());
            }            options.AppendToFile = true;

          
            //Script user defined table types
            if (db.UserDefinedTableTypes.Count > 0)
            {
                UserDefinedTableType[] utts = new UserDefinedTableType[db.UserDefinedTableTypes.Count];
                db.UserDefinedTableTypes.CopyTo(utts, 0);
                options.IncludeIfNotExists = true;
                options.ScriptDrops = true;
                scripter.Script(utts);
                options.IncludeIfNotExists = false;
                options.ScriptDrops = false;
                scripter.Script(utts);
            }

           
            //Script views
            if (db.Views.Count > 0)
            {
                List<View> views = new List<View>();
                foreach (View v in db.Views)
                {
                    if (v.IsSystemObject)
                        continue;
                    if (_ignoredViews.Contains(v.Name))
                        continue;
                    views.Add(v);
                }
                options.IncludeIfNotExists = true;
                options.ScriptDrops = true;
                scripter.Script(views.ToArray());

                options.IncludeIfNotExists = false;
                options.ScriptDrops = false;
                scripter.Script(views.ToArray());
            }

            //Script store procedures
            if (db.StoredProcedures.Count > 0)
            {
                List<StoredProcedure> procedures = new List<StoredProcedure>();
                foreach (StoredProcedure p in db.StoredProcedures)
                {
                    if (p.IsSystemObject || p.IsEncrypted)
                        continue;
                    procedures.Add(p);
                }
                options.IncludeIfNotExists = true;
                options.ScriptDrops = true;
                scripter.Script(procedures.ToArray());
                options.IncludeIfNotExists = false;
                options.ScriptDrops = false;
                scripter.Script(procedures.ToArray());
            }

            if (db.Version >= 9 &&
              db.CompatibilityLevel >= CompatibilityLevel.Version90)
            {
                 ////Script DDL triggers
                if (db.Triggers.Count > 0)
                {
                    DatabaseDdlTrigger[] triggers = new DatabaseDdlTrigger[db.Triggers.Count];
                    db.Triggers.CopyTo(triggers, 0);
                    options.IncludeIfNotExists = true;
                    options.ScriptDrops = true;
                    scripter.Script(triggers);
                    options.IncludeIfNotExists = false;
                    options.ScriptDrops = false;
                    scripter.Script(triggers);
                }
            }

When scripting tables, dependency walker to be used to find the dependency between table. Then use dependency to script tables with the right order.

The DependencyWalker object is a tool to perform scripting operations that involve dependencies such as identifying dependent relationships. The tool and converts this output to a list.

3. Technical Highlights of WPF SQL Replication Tool

WPF SQL Replication Tool is a wizard tool to help you replicate SQL Server database to different database server. This tool first generates scripts (database schema script, data scripts), then run these scripts to create database, create all database objects and insert data.

3.1 Extended WPF Toolkit Wizard

Extended WPF Toolkit provided an extensive collection of WPF controls, components and utilities for creating WPF applications. The wizard control of Toolkit makes extremely easy to build a wizard.

<xctk:Wizard FinishButtonClosesWindow="True">
            <i:Interaction.Triggers>
                <i:EventTrigger EventName="Finish">
                    <i:InvokeCommandAction Command="{Binding CloseCommand}">
                        <i:InvokeCommandAction.CommandParameter>
                            <System:Boolean>True</System:Boolean>
                        </i:InvokeCommandAction.CommandParameter>
                    </i:InvokeCommandAction>
                </i:EventTrigger>
                <i:EventTrigger EventName="Cancel">
                    <i:InvokeCommandAction Command="{Binding CloseCommand}">
                        <i:InvokeCommandAction.CommandParameter>
                            <System:Boolean>False</System:Boolean>
                        </i:InvokeCommandAction.CommandParameter>
                    </i:InvokeCommandAction>
                </i:EventTrigger>
                <i:EventTrigger EventName="Next">
                    <!--<i:InvokeCommandAction Command="{Binding NextCommand}" CommandParameter="{Binding RelativeSource={RelativeSource Mode=FindAncestor, AncestorType={x:Type xctk:Wizard}}, Path=CurrentPage.Name}" />-->
                    <cm:InteractiveCommand Command="{Binding NextCommand}" />
                </i:EventTrigger>
               
                <i:EventTrigger EventName="PageChanged">
                    <i:InvokeCommandAction Command="{Binding PageChangedCommand}" CommandParameter="{Binding RelativeSource={RelativeSource Mode=FindAncestor, AncestorType={x:Type xctk:Wizard}}, Path=CurrentPage.Name}" />
                </i:EventTrigger>
            </i:Interaction.Triggers>
            <xctk:WizardPage x:Name="introPage" 
                                   Title="Welcome to WPF SQL Replication Wizard Tool"
                                    Description="This Wizard will walk you though to import and export database."> 
                <xctk:WizardPage.ExteriorPanelContent>
                    <Image Source="Resources/DBImportExport.png" Width="256" Height="256"/>
                </xctk:WizardPage.ExteriorPanelContent>
            </xctk:WizardPage>

            <v:SelectSourceDB x:Name="selectSourcePage" PageType="Interior"
                                   Title="Choose a Data Source"
                                   Description="Select the source from which to copy data."
                                   NextPage="{Binding ElementName=dbScriptPage}"
                                   PreviousPage="{Binding ElementName=IntroPage}"/>
            <v:CreateDBScriptPage x:Name="dbScriptPage" PageType="Interior"
                                   Title="Generate Script"
                                   Description="Genereate DB Schema script, global data script and selected user script."
                                   NextPage="{Binding ElementName=selectDestinationPage}"
                                   PreviousPage="{Binding ElementName=selectSourcePage}"/>
            <v:SelectDestinationDB x:Name="selectDestinationPage" PageType="Interior"
                                   Title="Choose a Destination"
                                   Description="Specify where to copy data to."
                                   NextPage="{Binding ElementName=runPage}"
                                   PreviousPage="{Binding ElementName=dbScriptPage}"/>
            <v:RunDBScriptPage x:Name="runPage" PageType="Interior"
                                   Title="Run Script"
                                   Description="Run script on destination server"
                                   NextPage="{Binding ElementName=summaryPage}"
                                   PreviousPage="{Binding ElementName=createDBScriptPage}"/>
            
            <v:SummaryPage x:Name="summaryPage" PageType="Interior"
                                   Title="Summary"
                                   Description="List execution result of all tasks"
                                   CanFinish="True"/>
        </xctk:Wizard>

3.2 Delegate Command

An ICommand whose delegates can be attached for Execute(T) and CanExecute(T). It also implements the IActiveAwareinterface, which is useful when registering this command in a CompositeCommand that monitors command's activity. Delegate command class is implemented in Prism.

I don’t want to reference Prism to make this tool complicated. So provides an easy implementation of Delegate Command.

/// <summary>
    /// An <see cref="ICommand"/> whose delegates can be attached for <see cref="Execute"/> and <see cref="CanExecute"/>.
    /// It also implements the <see cref="IActiveAware"/> interface, which is
    /// useful when registering this command in a <see cref="CompositeCommand"/>
    /// that monitors command's activity.
    /// </summary>
    /// <typeparam name="T">Parameter type.</typeparam>
    public partial class DelegateCommand<T> : ICommand
    {
        private readonly Action<T> executeMethod = null;
        private readonly Func<T, bool> canExecuteMethod = null;
        private List<WeakReference> _canExecuteChangedHandlers;
       
        /// <summary>
        /// Initializes a new instance of <see cref="DelegateCommand{T}"/>.
        /// </summary>
        /// <param name="executeMethod">Delegate to execute when Execute is called on the command.  This can be null to just hook up a CanExecute delegate.</param>
        /// <remarks><seealso cref="CanExecute"/> will always return true.</remarks>
        public DelegateCommand(Action<T> executeMethod)
            : this(executeMethod, null)
        {
        }

        /// <summary>
        /// Initializes a new instance of <see cref="DelegateCommand{T}"/>.
        /// </summary>
        /// <param name="executeMethod">Delegate to execute when Execute is called on the command.  This can be null to just hook up a CanExecute delegate.</param>
        /// <param name="canExecuteMethod">Delegate to execute when CanExecute is called on the command.  This can be null.</param>
        /// <exception cref="ArgumentNullException">When both <paramref name="executeMethod"/> and <paramref name="canExecuteMethod"/> ar <see langword="null" />.</exception>
        public DelegateCommand(Action<T> executeMethod, Func<T, bool> canExecuteMethod)
        {
            if (executeMethod == null && canExecuteMethod == null)
                throw new ArgumentNullException("executeMethod", Resources.DelegateCommandDelegatesCannotBeNull);

            this.executeMethod = executeMethod;
            this.canExecuteMethod = canExecuteMethod;
        }

        ///<summary>
        ///Defines the method that determines whether the command can execute in its current state.
        ///</summary>
        ///<param name="parameter">Data used by the command. If the command does not require data to be passed, this object can be set to <see langword="null" />.</param>
        ///<returns>
        ///<see langword="true" /> if this command can be executed; otherwise, <see langword="false" />.
        ///</returns>
        public bool CanExecute(T parameter)
        {
            if (canExecuteMethod == null) return true;
            return canExecuteMethod(parameter);
        }

        ///<summary>
        ///Defines the method to be called when the command is invoked.
        ///</summary>
        ///<param name="parameter">Data used by the command. If the command does not require data to be passed, this object can be set to <see langword="null" />.</param>
        public void Execute(T parameter)
        {
            if (executeMethod == null) return;
            executeMethod(parameter);
        }

        ///<summary>
        ///Defines the method that determines whether the command can execute in its current state.
        ///</summary>
        ///<param name="parameter">Data used by the command.  If the command does not require data to be passed, this object can be set to null.</param>
        ///<returns>
        ///true if this command can be executed; otherwise, false.
        ///</returns>
        bool ICommand.CanExecute(object parameter)
        {
            return CanExecute((T)parameter);
        }

        ///<summary>
        ///Occurs when changes occur that affect whether or not the command should execute.
        ///</summary>        
        public event EventHandler CanExecuteChanged
        {
            add
            {
                WeakEventHandlerManager.AddWeakReferenceHandler(ref _canExecuteChangedHandlers, value, 2);
            }
            remove
            {
                WeakEventHandlerManager.RemoveWeakReferenceHandler(_canExecuteChangedHandlers, value);
            }
        }

        ///<summary>
        ///Defines the method to be called when the command is invoked.
        ///</summary>
        ///<param name="parameter">Data used by the command.  If the command does not require data to be passed, this object can be set to null.</param>
        void ICommand.Execute(object parameter)
        {
            Execute((T)parameter);
        }

        /// <summary>
        /// Raises <see cref="ICommand.CanExecuteChanged"/> on the UI thread so every 
        /// command invoker can requery <see cref="ICommand.CanExecute"/> to check if the
        /// <see cref="CompositeCommand"/> can execute.
        /// </summary>
        protected virtual void OnCanExecuteChanged()
        {
            WeakEventHandlerManager.CallWeakReferenceHandlers(this, _canExecuteChangedHandlers);
        }


        /// <summary>
        /// Raises <see cref="CanExecuteChanged"/> on the UI thread so every command invoker
        /// can requery to check if the command can execute.
        /// <remarks>Note that this will trigger the execution of <see cref="CanExecute"/> once for each invoker.</remarks>
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1030:UseEventsWhereAppropriate")]
        public void RaiseCanExecuteChanged()
        {
            OnCanExecuteChanged();
        }

3.3 Interactive Command

You should know event trigger can invoke a command. But how pass event arguments to the command? There is no built-in way. We have to subclass its parent (abstract) class: TriggerAction<DependencyObject>.

public class InteractiveCommand : TriggerAction<DependencyObject>
    {
        protected override void Invoke(object parameter)
        {
            if (base.AssociatedObject != null)
            {
                ICommand command = this.ResolveCommand();
                if ((command != null) && command.CanExecute(parameter))
                {
                    command.Execute(parameter);
                }
            }
        }

        private ICommand ResolveCommand()
        {
            ICommand command = null;
            if (this.Command != null)
            {
                return this.Command;
            }
            if (base.AssociatedObject != null)
            {
                foreach (PropertyInfo info in base.AssociatedObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
                {
                    if (typeof(ICommand).IsAssignableFrom(info.PropertyType) && string.Equals(info.Name, this.CommandName, StringComparison.Ordinal))
                    {
                        command = (ICommand)info.GetValue(base.AssociatedObject, null);
                    }
                }
            }
            return command;
        }

        private string commandName;
        public string CommandName
        {
            get
            {
                base.ReadPreamble();
                return this.commandName;
            }
            set
            {
                if (this.CommandName != value)
                {
                    base.WritePreamble();
                    this.commandName = value;
                    base.WritePostscript();
                }
            }
        }

        #region Command
        public ICommand Command
        {
            get { return (ICommand)GetValue(CommandProperty); }
            set { SetValue(CommandProperty, value); }
        }

        // Using a DependencyProperty as the backing store for Command.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty CommandProperty =
            DependencyProperty.Register("Command", typeof(ICommand), typeof(InteractiveCommand), new UIPropertyMetadata(null));
        #endregion
}

3.4 Reactive extension

The Reactive Extensions (Rx) is a library for composing asynchronous and event-based programs using observable sequences and LINQ-style query operators. Using Rx, developers represent asynchronous data streams with Observables, query asynchronous data streams using LINQ operators, and parameterize the concurrency in the asynchronous data streams using Schedulers. Simply put, Rx = Observables + LINQ + Schedulers.

     GetSourceDatabasesCommand = new DelegateCommand<ConnectionSetting>(x =>
            {
                LastError = string.Empty;
                IsBusy = true;
                var t = new Task<List<string>>(() =>
                {
                    return GetDatabases(x);
                });

                t.ToObservable().ObserveOnDispatcher().Subscribe(list =>
                {
                    SourceDatabases = list;
                    IsBusy = false;
                    ReportError();
                });

                t.Start();
            });

4. Workflow of WPF SQL Replication Tool

4.1 Welcome

4.2 Choose Source Database

Choose a database which you want to export. Please tick "Import data from source database" option.

We choose "AdventueWorks" as an example.

4.3 Generate DB Schema Script

First select an output folder which is the place scripts output to. Tick "Create Schema" option if you want to generate database schema scripts.

Ok. Now do the work. Click "Generate script" button.

When it completes, you can see the SQL files generated in output folder.

4.4 Choose Destination Server

Choose a database which you want to import. Specify the server name and access authentication. For example, if you want to export to "test" of local SQL Express, the blow is the screenshot. Please note you have to click "Get Default Path" button to get the data file path. This tool doesn’t delete the existing database. Make sure the new database name doesn’t exist on the server if you want to create database.

4.5 Run Script

Tick "Create Database" option to create the database from scratch, and create all objects.

Click "Run immediately" to start import job.

When it completes, "AdventureWorks2" is created successfully with all objects.

4.6 Summary of Tasks

After all works done, you finally get Summary page. The summary page gives you an overview about all tasks you’ve done. Also you can open log file to check.

5. Conclusion

In this article we go through how to use SMO to copy database schema to other server, and how to integrate with WPF and MVVM pattern to implement a light replication tool. I’ll introduce how to export and import data in next article.

WPF SQL Replication Tool (Part 1)