WPF SQL 复制工具（第 1 部分）
5.00/5 (9投票s)
通过复制，您可以将数据分发到不同位置，并通过本地和广域网、拨号连接、无线连接以及 Internet 分发给远程或移动用户。
1. 引言
复制是一组技术，用于将数据和数据库对象从一个数据库复制并分发到另一个数据库，然后同步数据库以保持一致性。通过复制，您可以将数据分发到不同位置，并通过本地和广域网、拨号连接、无线连接以及 Internet 分发给远程或移动用户。
尽管 SQL Server 复制服务器做得很好，但我们仍然需要一个定制的工具，特别是对于大型数据库。
SQL Server Management Objects (SMO) 是用于以编程方式管理 Microsoft SQL Server 的对象。您可以使用 SMO 构建定制的 SQL Server 管理应用程序。
数据库复制包括数据库对象和数据。我们可以直接在两个 SQL Server 之间复制数据库对象并插入数据。但这不够灵活。最好的方法是为数据库对象生成 SQL 创建脚本，为数据生成插入脚本。
2. 脚本化数据库架构对象
2.1 引用 SMO 程序集
- 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 连接到源数据库并创建 Scripter 实例
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 生成数据库创建脚本
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 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);
}
}
在脚本化表时，需要使用依赖项查找器来查找表之间的依赖关系。然后使用依赖关系按正确的顺序脚本化表。
DependencyWalker 对象是一个用于执行涉及依赖项的脚本化操作的工具，例如识别依赖关系。该工具将此输出转换为一个列表。
3. WPF SQL 复制工具的技术亮点
WPF SQL 复制工具是一个向导工具，可帮助您将 SQL Server 数据库复制到不同的数据库服务器。该工具首先生成脚本（数据库架构脚本、数据脚本），然后运行这些脚本来创建数据库、创建所有数据库对象和插入数据。
3.1 扩展 WPF 工具包向导
扩展 WPF 工具包提供了一系列丰富的 WPF 控件、组件和实用程序，用于创建 WPF 应用程序。工具包的向导控件使得构建向导变得极其容易。
<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 委托命令
一个 ICommand，可以为其 Execute(T) 和 CanExecute(T) 附加委托。它还实现了 IActiveAwareinterface，这在将此命令注册到监视命令活动的 CompositeCommand 中时很有用。委托命令类在 Prism 中实现。
我不想引用 Prism 来使这个工具变得复杂。因此，提供了一个易于实现的委托命令。
/// <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 交互式命令
您应该知道事件触发器可以调用命令。但是如何将事件参数传递给命令？没有内置的方法。我们必须继承其父类（抽象类）：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 响应式扩展
响应式扩展 (Rx) 是一个用于使用可观察序列和 LINQ 查询操作符来组合异步和基于事件的程序的库。使用 Rx，开发人员可以使用 Observables 表示异步数据流，使用 LINQ 操作符查询异步数据流，并使用 Schedulers 参数化异步数据流中的并发。简单地说，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. WPF SQL 复制工具的工作流程
4.1 欢迎
4.2 选择源数据库
选择要导出的数据库。请勾选“从源数据库导入数据”选项。
我们选择“AdventueWorks”作为示例。
4.3 生成数据库架构脚本
首先选择一个输出文件夹，这是脚本的输出位置。如果您想生成数据库架构脚本，请勾选“创建架构”选项。
好的。现在开始工作。单击“生成脚本”按钮。
完成后，您可以在输出文件夹中看到生成的 SQL 文件。
4.4 选择目标服务器
选择要导入的数据库。指定服务器名称和访问身份验证。例如，如果要导出到本地 SQL Express 的“test”，则下图是截图。请注意，您必须单击“获取默认路径”按钮来获取数据文件路径。此工具不会删除现有数据库。如果您想创建数据库，请确保新数据库名称在服务器上不存在。
4.5 运行脚本
勾选“创建数据库”选项可以从头开始创建数据库，并创建所有对象。
单击“立即运行”开始导入作业。
完成后，“AdventureWorks2”已成功创建并包含所有对象。
4.6 任务摘要
所有工作完成后，您最终会看到摘要页面。摘要页面为您提供了已完成的所有任务的概述。您还可以打开日志文件进行检查。
5. 结论
在本文中，我们回顾了如何使用 SMO 将数据库架构复制到其他服务器，以及如何与 WPF 和 MVVM 模式集成来实现一个轻量级的复制工具。我将在下一篇文章中介绍如何导出和导入数据。