65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL简单实用工具

Alexandros Pappas
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.68/5 (12投票s)

2013年5月21日

CPOL

15分钟阅读

viewsIcon

36894

downloadIcon

1829

该项目提供了SQL Server的实用工具，例如执行SQL脚本列表、将数据导出为SQL脚本以及显示记录之间的关系。

SqlSimpleUtilities main form

引言

该项目提供了三个SQL Server的实用工具

  1. 执行SQL脚本：用户可以选择一个包含SQL脚本的文件夹，或者一个包含SQL脚本列表的文件。可以指定脚本的执行顺序，并执行SQL脚本。
  2. 导出为SQL脚本：用户可以指定要导出的表列表。基于表关系（外键），该工具会计算导出顺序（父表先于子表）。然后，通过保留关系，将表的数据导出为SQL脚本。
  3. 显示引用：用户可以指定数据库中的一条记录。基于表关系（外键），该工具会在一定距离内查找所有相关记录，并使用svg显示图表。

该项目还演示了如何处理MDI应用程序中的工具栏、如何在WebBrowser控件中显示svg、如何使WebBrowser控件可缩放、如何通过异步调用数据库来填充DataSet、如何使用通用表表达式(CTE)编写递归SQL查询、如何编写深度优先图搜索算法以及如何显示图表。

背景

执行SQL脚本

通常需要一个接一个地执行多个SQL脚本。通常的做法是将SQL脚本逐个加载到SQL Server Management Studio中执行。这很繁琐、耗时且容易出错，因为很容易遗漏某个SQL脚本。下面的图像中所示的工具提供了按顺序执行多个SQL脚本的功能。

Execute scripts form

该窗体提供两个选项卡

  • 执行：显示SQL脚本列表和执行进度。
  • Connection:

    Connection tab

    提供参数以指定到SQL Server数据库的连接。

    通过按“获取”按钮，SQL数据库列表将加载到组合框中。

工具栏按钮 Toolbar buttons
  • 新建：清空列表。
  • 打开：提示用户选择一个包含SQL脚本列表的文件。选择文件后，窗体的列表将填充所选文件中列出的文件名。
  • 导入：提示用户选择一个包含SQL脚本的文件夹。选择文件夹后，窗体的列表将按字母顺序填充所选文件夹及其子文件夹中*.sql文件的文件名。也可以将文件拖放到窗体的列表中。
  • 保存：窗体的列表可以保存到文件。之后可以通过“打开”按钮打开。
  • 运行：开始执行SQL脚本。
  • 暂停：暂停执行。按“运行”时，将从暂停的脚本处恢复执行。
  • 取消：取消执行。按“运行”时，将从头开始执行。
  • 上移：将列表中选定的文件名向上移动一个位置。
  • 下移：将列表中选定的文件名向下移动一个位置。

 

示例

  1. 创建一个文件夹，例如D:\temp\SQLscripts，并在其中放入一些SQL脚本。
  2. 按“导入”按钮，然后选择该文件夹。列表将填充.sql文件。
  3. 使用上移和下移按钮重新排序文件。
  4. 按“运行”按钮开始执行。
  5. 如果出现错误消息，这些消息将写在该窗体底部的文本框中。
  6. 按“保存”按钮，将文件列表保存到例如D:\temp\List1.txt。

导出为SQL脚本

数据库表中的参数化数据经常需要从开发环境复制到UAT和生产数据库。这通常通过编写相应的SQL脚本来完成。该工具将选定数据库表中的数据导出为SQL脚本。难点在于找到导出表的正确顺序，因为表之间可能存在外键。外键代表了一个图。通过遍历图来找到导出表的顺序，因为父表先于子表导出。可以通过一个唯一列来指定与父表的关系。

该窗体提供两个选项卡

  • 执行：显示表列表、外键和导出进度。
  • 连接：如上所述，提供参数以指定到SQL Server数据库的连接。
工具栏按钮 Toolbar buttons
  • 准备：查找选定表的外键。如果源数据库和目标数据库中的ID不同，可以在外键网格中指定一个唯一列，通过该列可以检索父表ID。如果在仅外键图中找到循环链，则会以灰色背景标记，并且必须删除。在上面的例子中，在表HumanResources.Employee上找到了一条循环链，该表通过ManagerID列引用自身。将其删除，以便可以开始导出。如果没有找到循环链，则计算表的顺序。
  • 运行：开始将数据导出为SQL脚本。必须在“SQL脚本”文本框中指定文件名。
  • 暂停：暂停执行。按“运行”时，将从暂停的表处恢复执行。
  • 取消：取消执行。按“运行”时，将从头开始执行。

示例

  1. 在“连接”选项卡中，将连接设置为AdventureWorks数据库。

    Connection tab

  2. 在“执行”选项卡中，选择HumanResources表，然后按“准备”按钮：网格将填充外键。

    After first prepare

    将显示以下消息框。

    Message box cyclic chain

    循环链是网格中的第一行，背景为灰色。表Employees具有对自身的引用。
  3. 该工具不允许循环引用。选择第一行，然后右键单击删除它。这不会从数据库中删除外键。ManagerID值将按原样用于SQL脚本。
  4. 再次按“准备”按钮：网格将填充外键，并且表的顺序将在右侧的列表中显示。

    After second prepare

    将显示以下消息框。

    Message box foreign keys to other tables

    引用未导出的表的两个外键背景为灰色。
  5. 在最后一个外键中，将“唯一”列中选定的字段更改为EmailAddress，如图所示。这意味着ContactId字段将通过搜索唯一字段EmailAddress从Person.Contact表中检索。
  6. 将“SQL脚本”文本框设置为有效的文件名。这是将写入脚本的文件。
  7. 按“运行”按钮。表导出开始。

    During Run

  8. 完成后，可以通过双击“SQL脚本”文本框来打开脚本。

显示引用

查看数据库中的记录时，很难看到它与哪些记录相关。该记录可能引用其他记录，或者其他记录可能引用给定的记录。要查找第一种情况的记录，必须检索以给定记录的表为父表的外键。必须从该记录中检索这些外键的父列。要查找第二种情况的记录，必须检索以给定记录为被引用表的表外键。对于这些外键中的每一个，必须检索引用给定记录的相应外键的被引用表中的记录。所有这些相邻记录都与给定记录的距离为1。这些记录可能还与其他记录相关，这些记录与给定记录的距离为2。相同的过程可以运行到指定的距离。该工具以图表的形式显示给定记录 up to a given distance 的所有相关记录。

该窗体提供两个选项卡

  • 执行：提供控件以指定表名、主键值和距离，并显示记录及其关系的图表。
    • 表名：记录所在的表名。
    • 主键值：记录主键值的逗号分隔列表。
    • 距离：检索相关记录的最大距离。
    • 表过滤器：SQL WHERE子句，用于排除某些表。例如，可以在此处排除许多表引用的某些基本表，如货币、国家、用户。
  • 连接：如上所述，提供参数以指定到SQL Server数据库的连接。
工具栏按钮 Toolbar buttons
  • 运行：检索数据库的所有主键和外键，但排除“表过滤器”指定的表。对于给定的记录，会构建一个图，其中包含 up to a given distance 的所有相关记录。图表将被写入可执行文件夹中的文件Graph1.dot和Graph1.svg。如果文件小于200KB，则将在窗体的WebBrowser控件中显示，否则将在默认Webbrowser中打开。
  • 取消：取消执行。未完成的图将按上述方式写入文件并在上述规定中显示。

 

示例

  1. 在“连接”选项卡中，将连接设置为AdventureWorks数据库。

    Connection tab

  2. 在“执行”选项卡中，选择Sales.Customer表，将“主键值”设置为30040，将“距离”设置为1，然后按“运行”按钮：窗体将显示与Sales.Customer表中CustomerID=30040的记录直接相关的所有记录。

    Customer 30040, Distance 1

    在每个顶点中，显示四个元素
    • 级别：一个“L”后面跟着顶点的级别。父节点的级别高于其所有子节点的级别。级别代表创建顺序。较低级别的记录比高级别的记录先创建。因此，高级别记录必须先于低级别记录删除。相同级别的记录可以同时删除。
    • 距离：一个“D”后面跟着与起始记录的距离。与起始记录直接相关的所有记录（直接父节点或直接子节点）的距离为1。与距离为1的记录直接相关的所有记录的距离为2，依此类推。
    • 表名：顶点所代表的记录所属的表名。
    • 主键值：记录主键值的逗号分隔列表。
    在状态栏中，显示以下信息。
    • 最大距离：起始记录与相关记录之间的最大距离。
    • 顶点数：找到的顶点数。
    • 边数：找到的边数。
    • 文件：生成的svg文件的路径。在同一位置也会创建一个相应的.dot文件。
    • 长度：svg文件的长度（以字节为单位）。
  3. 将“距离”设置为2。这将需要更长的时间（通常为4分钟），并产生约10000个顶点。这是因为Sales.Customer表引用了Sales.Territory表。一个Sales.Territory记录被许多Sales.Customer记录引用，如图所示。

    Customer 30040, Distance 2

  4. 将“表过滤器”设置为WHERE a.name != 'SalesTerritory'。图将变得小得多，只有19个顶点。

    Customer 30040, Distance 2, without SalesTerritory

  5. 将“距离”设置为10，并将“表过滤器”设置为WHERE a.name NOT IN ('AddressType','ContactType','CountryRegion','CurrencyRate','PhoneNumberType','Product','SalesPerson','SalesStore','SalesTerritory','ShipMethod','SpecialOffer','SpecialOfferProduct','StateProvince')，以排除所有基本表，然后按“运行”。完成后，状态栏显示Max distance = 9。因为最大距离设置为10，这意味着给定的记录直接或间接相关的所有记录都已检索。与给定记录相关的距离大于9的记录（除了“表过滤器”中指定的表）没有。
    Customer 30040, Distance 10, without base tables

Using the Code

执行SQL脚本

FormExecScripts.vb实现了SQL脚本的执行。SQL脚本可能包含GO语句，因此无法使用SqlCommand.ExecuteNonQuery执行这些脚本。方法ModSql.ExecuteSql在GO语句处分割SQL脚本。GO语句位于行的开头，同一行没有其他语句。然后，使用SqlCommand.ExecuteNonQuery逐个执行生成的SQL脚本。

导出为SQL脚本

FormExportToScript.vb实现了导出到SQL脚本的功能。导出逻辑在ExportByTable类中。方法FKsComputeLevel计算外键的级别（即顺序）。算法在方法的注释中进行了描述。SQL代码由方法DataTableExport生成。

显示引用

FormShowReferences.vb实现了“显示引用”功能。所有处理都在BackgroundWorker1_DoWork方法中完成。执行以下语句：

  • FillSqlStatements：此方法检索所有表的主键和外键（不包括“表过滤器”中指定的表），并填充DataTables mDataTableIXs和mDataTableFKs。
    • mDataTableIXs包含主键，并具有以下列。
      • TableName：此索引所属的表名。
      • PKColumn：属于此索引的列名的逗号分隔列表。
    • mDataTableFKs包含外键，并具有以下列。
      • FKName：外键名称。
      • ParentTable：外键的被引用表。
      • ChildTable：外键的父表。
      • ParentColumn：外键被引用列的逗号分隔列表。
      • ChildColumn：外键父列的逗号分隔列表。
      在映射外键定义的父子关系时，sys.foreign_keys表中的列命名有些令人困惑。在此项目中，父表将是外键的被引用表，子表将是外键的父表。
    用于填充上述DataTables的两个查询都使用了CTE（通用表表达式）的递归查询。
    对于每个表，此方法构造要执行的SQL语句，用于检索记录的邻居。例如，表Sales.Customer有3个父表和1个子表。

    Sales.Customer relations

    要获取父记录，必须执行以下SQL脚本。
    SELECT PersonID,TerritoryID,StoreID FROM Sales.Customer _
WHERE CustomerID=@Sales.Customer_CustomerID@
    要获取子记录，必须检索子表中引用给定记录的记录，如以下SQL脚本所示。
    SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE CustomerID=@Sales.Customer_CustomerID@
    每个表的SQL语句保存在DataTable DataTableSQLStatements中。
  • 创建一个新图：mGraph = New AdjacencyGraph(New DbVertexProvider(), New DbEdgeProvider(), False)。AdjacencyGraph位于命名空间QuickGraph.Representations中，该命名空间定义在QuickGraph.Algorithms.dll中。AdjacencyGraph表示一个带有顶点和边的图。
  • 创建初始顶点：Dim vertex = GetStartVertex(mGraph)。检索UI中指定的记录，创建顶点并将其添加到图中。数据库记录的对应顶点由表名和主键值定义。如果两个顶点的表名和主键值相等，则它们代表相同的记录。
  • 通过调用方法FillNeighbours检索起始顶点的邻居。此方法采用深度优先搜索（DFS）算法来构建图。
    • 如果距离小于最大距离且顶点尚未被访问。
      • 通过将顶点的主键值填充到先前生成的SQL语句中来构建SQL语句。通过调用GetDataset方法检索相关记录。GetDataSet方法使用异步方法SqlAdapter.BeginExecuteReader来填充DataSet。
      • 对于子表是顶点表的每一个外键。
        • 通过调用FillParent方法填充父记录。FillParent方法获取子顶点并创建或检索父顶点。
          • 创建父顶点。
          • 如果这样的顶点已经存在。
            • 子顶点的级别设置为子顶点级别与现有父顶点级别加1的最大值。
            • 在现有父顶点和子顶点之间添加一条边。
          • Else
            • 父节点的级别设置为子节点级别减1。
            • 将父顶点添加到图中。
            • 在新创建的父顶点和子顶点之间添加一条边。
            • 通过递归调用方法FillNeighbours来填充新创建的父顶点及其所有邻居。
      • 对于父表是顶点表的每一个外键。
        • 对于子表的每一行。
          • 通过调用FillChild方法填充子记录。FillChild方法获取父顶点并创建或检索子顶点。
            • 创建子顶点。
            • 如果这样的顶点已经存在。
              • 父顶点的级别设置为父顶点级别与现有子顶点级别减1的最小值。
              • 在父顶点和现有子顶点之间添加一条边。
            • Else
              • 子节点的级别设置为父节点级别加1。
              • 将子顶点添加到图中。
              • 在父顶点和新创建的子顶点之间添加一条边。
              • 通过递归调用方法FillNeighbours来检索新创建的子顶点及其所有邻居。
  • 在FillGraphViz方法中，将生成的图写入两个文件。
    • 创建一个GraphvizAlgorithm对象：Dim svg = New GraphvizAlgorithm(graph, ".\", GraphvizImageType.Svg)。
    • 创建Graph1.svg文件：mFileName = svg.Write("Graph1")。
    • 创建Graph1.dot文件：File.WriteAllText("Graph1.dot", svg.Output.ToString())。

方法BackgroundWorker1_RunWorkerCompleted检索文件Graph1.svg，如果它小于200kB，则在WebBrowser控件中显示它，否则，它在默认的Webbrowser中打开该文件。

此项目使用了在 Dependency Visualizer项目中提供的QuickGraph库。不幸的是，我找不到这些库的文档。我尝试使用官方QuickGraph库，但文档非常简陋，也没有提供示例。

历史

  • 2013年5月21日：首次发布
  • 2016年9月22日：添加了FormReferences.vb以显示数据库记录的关系。
© . All rights reserved.