WPF SQL 复制工具（第二部分）
5.00/5 (13投票s)
在本文中，我们从生成插入存储过程开始，然后介绍如何使用此过程生成插入 SQL 文件并运行这些插入 SQL 文件导入数据。
1. 引言
在上篇文章中，我们介绍了如何使用 SQL Server Management Objects 复制数据库对象。在这里，我们将讨论如何导出和导入数据。
在开发环境中工作时，几乎总有将生产数据库中的静态 SQL 脚本并插入到开发数据库环境的需求。这通常是一项繁琐的任务，一个名为 sp_generate_inserts 的便捷的 Microsoft SQL 存储过程可以解决这个问题。
该存储过程 (proc) 由 Narayana Vyas Kondreddi 创建，脚本副本可在 http://vyaskn.tripod.com/code/generate_inserts.txt 找到。
2. 生成插入
在 [master] 数据库中运行脚本。然后，您可以在要导出的数据库上执行该过程。
基本上，这个过程会创建一个字面查询并运行它。结果将是
Insert [schema].[Table] (Column1, …, Column N) Values ( Value1, …, Value N)
原始版本的生成插入过程存在一些缺陷。
如果您直接在 SQL Express 2008 R2 上运行脚本，它总是在这里失败：
EXEC master.dbo.sp_MS_upd_sysobj_category 1
我删除了开头的 "EXEC master.dbo.sp_MS_upd_sysobj_category 1"，然后将
底部的 "EXEC master.dbo.sp_MS_upd_sysobj_category 2" 替换为 "EXEC sys.sp_MS_marksystemobject sp_generate_inserts"。请注意，您必须将 sp_generate_inserts 标记为系统对象。否则，当您运行该过程时，您将收到错误 "User table or view not found"（用户表或视图未找到）。
进行这些更改后，我们现在进行测试，
转到 [AdventureWorks]，
exec sp_generate_inserts @table_name='Culture', @owner='Production'
您会发现结果是单列。对于一些较长的值，它会被截断。
所以我做了一个更改，将结果分成多列。
然后不幸的是，原始版本的 "生成插入" 将 "xml" 数据转换为 "char"。如果 xml 中存在非 ANSI 字符，这将导致另一个错误 "Conversion of one or more characters from XML to target collation impossible"（一个或多个字符从 XML 转换为目标排序规则的转换不可能）。我通过将 "xml" 转换为 "nvarchar(max)" 来修复此问题。
3. WPF SQL 复制工具
3.1 Visual Studio 解决方案结构
此解决方案包含 3 个项目："WPF SQL Replication Tool"、"SQLSMO" 和 "LogUtil"。
"SQLSMO" 项目用于使用 SQL Server Management Objects 脚本化数据库和数据。
"LogUtil" 是一个使用 log4net 的日志组件。
"WPF SQL Replication Tool" 使用 MVVM 模式进行 SQL 数据库和数据复制。
3.2 MVVM 设计模式
在 MVVM 中，代码是 ViewModel。因此，它基本上侧重于关注点分离，从而使应用程序结构的实现更容易创建和维护。
如果 ViewModel 中的属性值发生更改，这些新值将通过数据绑定和通知自动传播到视图。当用户在视图中执行某个操作（例如单击保存按钮）时，ViewModel 上的命令将执行以执行请求的操作。在此过程中，是 ViewModel 修改模型数据，视图绝不修改它。视图类不知道模型类的存在，而 ViewModel 和模型也不知道视图的存在。事实上，模型根本不知道 ViewModel 和视图的存在。
MVVM 模式包含三个关键部分：
模型 (业务规则、数据访问、模型类)
视图 (用户界面 (XAML))
ViewModel (视图和模型之间的代理或中间人)
ViewModel 作为模型和视图之间的接口。它提供视图和模型数据之间的数据绑定，并通过命令处理所有 UI 操作。
视图将其控件值绑定到 ViewModel 上的属性，ViewModel 又公开模型对象中包含的数据。
3.3 将生成插入集成到复制工具中
了解如何使用 sp_generate_inserts 后，现在我们需要将其集成到我们的代码中。
private List<string> GetInserts(StringBuilder sb, Database db, string outputFolder, string tName, string schema, int maxRows, bool identityOn, int lastSeq = 0)
{
List<string> outputFiles = new List<string>();
var identityOnSql = string.Format("SET IDENTITY_INSERT {0}.{1} ON", schema, tName);
var identityOffSql = string.Format("SET IDENTITY_INSERT {0}.{1} OFF", schema, tName);
try
{
using (DataSet ds = db.ExecuteWithResults(sb.ToString()))
{
int rows = 0;
int seq = lastSeq;
string outputfile = Path.Combine(outputFolder, tName + seq.ToString("D5") + ".sql");
StreamWriter sw = new StreamWriter(outputfile, false, Encoding.Unicode);
if (identityOn)
{
sw.WriteLine(identityOnSql);
}
foreach (DataTable dt in ds.Tables)
{
int cols = dt.Columns.Count;
foreach (DataRow dr in dt.Rows)
{
if (rows > maxRows)
{
if (identityOn)
{
sw.WriteLine(identityOffSql);
}
sw.Close();
sw.Dispose();
outputFiles.Add(outputfile);
seq++;
rows = 0;
outputfile = Path.Combine(outputFolder, tName + seq.ToString("D5") + ".sql");
sw = new StreamWriter(outputfile, false, Encoding.Unicode);
if (identityOn)
{
sw.WriteLine(identityOnSql);
}
}
string insertSql = "";
for (int i = 0; i < cols; i++)
insertSql += dr[i].ToString();
sw.WriteLine(insertSql);
rows++;
}
}
if (identityOn)
{
sw.WriteLine(identityOffSql);
}
sw.Close();
sw.Dispose();
outputFiles.Add(outputfile);
}
return outputFiles;
}
catch (Exception ex)
{
throw;
}
}
上面的代码调用 sp_generate_inserts 过程，并将结果保存到物理文件中。考虑到一个大表，结果文件可能会变得非常大。这不是我想要的。所以我引入了 maxRows 来限制每个文件中的行数。
您可以看到 "Set Identity On" 和 "Set Identity Off"。这是什么意思？当我们设计一个表时，经常会将主键 (int) 设置为标识列，这样在插入数据时，id 的值会自动增加。因为在结果 SQL 文件中，我们已经为标识列提供了值。当我们在导入期间运行结果 SQL 文件时，我们必须设置 "Set identity insert on"，并在会话结束时设置 "Set identity insert off"。
现在我们来看看如何从数据库生成数据脚本。
public List<string> GenerateDataScript(string connStr, string outputDirectory)
{
List<string> outputFiles = new List<string>();
SqlConnection connection = new SqlConnection(connStr);
ServerConnection sc = new ServerConnection(connection);
Server s = new Server(sc);
Scripter scripter = new Scripter(s);
Database db = new Database();
db = s.Databases[connection.Database];
string outputFolder = Path.Combine(outputDirectory, db.Name + "\\Data");
if (!Directory.Exists(outputFolder))
Directory.CreateDirectory(outputFolder);
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 = true;
options.ContinueScriptingOnError = true;
scripter.Options = options;
//Script tables
if (db.Tables.Count > 0)
{
Table[] tbls = new Table[db.Tables.Count];
db.Tables.CopyTo(tbls, 0);
DependencyTree tree = scripter.DiscoverDependencies(tbls, true);
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
List<string> excludeCols = new List<string>();
StringBuilder sb = new StringBuilder();
int maxRows = MaxRows;
foreach (DependencyCollectionNode dep in depcoll)
{
sb.Clear();
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;
if (tbl.IsSystemObject)
continue;
if (ignoreTables.ToList().Contains(tName))
continue;
bool identityOn = false;
foreach (Column col in tbl.Columns)
{
if (col.Identity)
{
identityOn = true;
}
if (col.Computed)
{
excludeCols.Add(col.Name);
}
}
string colsExclude = excludeCols.Count > 0 ? ",@cols_to_exclude = \"" : "";
int i = 0;
foreach (var c in excludeCols)
{
colsExclude += i==0 ? string.Format("'{0}'", c) : string.Format(",'{0}'", c);
i++;
}
if (!string.IsNullOrEmpty(colsExclude))
colsExclude += "\"";
sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='{1}'{2}{3}", tName, schema, colsExclude, Environment.NewLine);
outputFiles.AddRange(GetInserts(sb, db, outputFolder, tName, schema, maxRows, identityOn));
}
}
return outputFiles;
}
我必须提到的一点是，我们需要排除计算列。如果计算列的值是由确定性表达式定义的，则它不能作为 INSERT 或 UPDATE 语句的目标。
3.4 导入前检查生成插入是否存在
您可以看到数据导入完全依赖于 "生成插入" 过程。如果 "生成插入" 不存在，数据导入将立即失败。
public static void EnsureGenerateInsertsProcExists(string connectionString, bool forceToUpdate)
{
string query = "select * from sysobjects where type='P' and name='sp_generate_inserts'";
bool spExists = false;
if (!forceToUpdate)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
spExists = true;
LogUtil.Logger.Instance.Log("sp_generate_inserts procedure exists in master of source server already.");
break;
}
}
}
}
}
if (!spExists)
{
var file = Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "Scripts\\sp_generate_inserts.sql");
if (File.Exists(file))
{
LogUtil.Logger.Instance.Log("Create sp_generate_inserts procedure in master of source server already.");
string script = File.ReadAllText(file);
ExecuteSql(script, connectionString, false);
}
}
}
3.4 取消选中约束并检查约束
在将数据导入目标数据库期间还会发生另一个问题。那就是依赖性。很难按照依赖顺序插入数据，有时嵌套依赖性使这个问题更加困难。
我们可以通过一个简单的方法来解决这个问题：在导入前禁用所有约束，在导入后启用所有约束。
public static void UncheckConstraint(string connectionString, string tableName = null)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "EXEC sp_MSforeachtable @command1='ALTER TABLE " + (string.IsNullOrEmpty(tableName) ? "?" : tableName) + " NOCHECK CONSTRAINT ALL'";
using (var cmd = new SqlCommand(sql, connection))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public static void CheckConstraint(string connectionString, string tableName = null)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string sql = "EXEC sp_MSforeachtable @command1='ALTER TABLE " + (string.IsNullOrEmpty(tableName) ? "?" : tableName) +" CHECK CONSTRAINT ALL'";
using (var cmd = new SqlCommand(sql, connection))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
4. 复制数据库和数据
4.1 欢迎
4.2 选择源数据库
选择要导出的数据库。请勾选 "从源数据库导入数据" 选项。
我们选择 "AdventueWorks" 作为示例。
4.3 生成数据库架构脚本和数据脚本
首先选择一个输出文件夹，这是脚本输出的位置。
有一些选项。
- 如果希望更新源数据库上的 "生成插入" 过程，才需要勾选 "Update generate inserts sql procedure if it exists"（如果存在则更新生成插入 SQL 过程）选项。
此选项为您提供了更新生成插入过程的机会。正如我之前所说的，原始版本存在一些问题。即使在我稍作修改之后，对于某些特殊数据，仍然可能出现一些问题。如果发生这种情况，您需要添加一些自定义更改。
- "Create Schema Script"（创建架构脚本）生成数据库架构脚本。
- "Create Data Script"（创建数据脚本）生成数据脚本。
根据需要勾选选项。此工具非常灵活，如果您不需要，不必勾选所有选项。
如果想生成数据库架构脚本，请勾选 "Create Schema"（创建架构）选项。
好的。现在开始工作。单击 "Generate script"（生成脚本）按钮。
完成后，您可以在输出文件夹中看到生成的 SQL 文件。
4.4 选择目标服务器
选择要导入的数据库。指定服务器名称和访问身份验证。例如，如果您想导出到本地 SQL Express 的 "test" 数据库，则下图是截图。请注意，您必须单击 "Get Default Path"（获取默认路径）按钮来获取数据文件路径。此工具不会删除现有数据库。如果您想创建数据库，请确保新数据库名称在服务器上不存在。
4.5 运行脚本
"Create Database"（创建数据库）从头开始创建数据库，并创建所有对象。
"Import Data"（导入数据）将所有数据导入目标数据库。
您不必选择所有选项。对于现有数据库，您无需勾选 "Create Database"（创建数据库）选项。
单击 "Run immediately"（立即运行）以启动导入作业。
完成后，"AdventureWorks2" 成功创建，并包含所有对象。
4.6 任务摘要
所有工作完成后，您将看到摘要页面。摘要页面为您提供已完成所有任务的概览。您还可以打开日志文件进行检查。
5. 结论
在本文中，我们从生成插入存储过程开始，然后介绍如何使用此过程生成插入 SQL 文件并运行这些插入 SQL 文件导入数据。关键点是，此工具生成的所有 SQL 文件都可以重用。如果您在源选择页面上勾选 "Import Data from existing SQL files"（从现有 SQL 文件导入数据），则向导将跳过脚本生成页面直接进入选择目标页面。