C#实现SQL Server数据血缘关系生成程序

要在现有的C#程序中添加功能，输出SQL Server数据血缘关系的三张表到Excel文件，我们需要进行以下几个步骤：

分析存储过程、视图和函数中的引用关系，构建数据血缘关系。
按依赖性从小到大排序表的顺序。
找出对应生成表的数据的存储过程。
将结果输出到Excel文件。

以下是完整的代码实现：

using Microsoft.SqlServer.TransactSql.ScriptDom;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;class Program
{static void Main(){string directoryPath = @"<搜索的目录路径>";var dataRelations = new Dictionary<string, List<string>>();var tableProcedures = new Dictionary<string, List<string>>();var allTablesAndViews = new HashSet<string>();ProcessSqlFiles(directoryPath, dataRelations, tableProcedures, allTablesAndViews);var sortedTables = SortTablesByDependency(dataRelations, allTablesAndViews);WriteToExcel(dataRelations, sortedTables, tableProcedures);}static void ProcessSqlFiles(string directoryPath, Dictionary<string, List<string>> dataRelations, Dictionary<string, List<string>> tableProcedures, HashSet<string> allTablesAndViews){foreach (string filePath in Directory.EnumerateFiles(directoryPath, "*.sql", SearchOption.AllDirectories)){Console.WriteLine($"Processing file: {filePath}");string sqlContent = File.ReadAllText(filePath);ProcessSqlContent(sqlContent, dataRelations, tableProcedures, allTablesAndViews);CheckCreateStatements(sqlContent, tableProcedures);}}static void ProcessSqlContent(string sqlContent, Dictionary<string, List<string>> dataRelations, Dictionary<string, List<string>> tableProcedures, HashSet<string> allTablesAndViews){TSql150Parser parser = new TSql150Parser(false);IList<ParseError> errors;TSqlFragment fragment = parser.Parse(new StringReader(sqlContent), out errors);if (errors.Count == 0){var referenceVisitor = new ReferenceVisitor(dataRelations, allTablesAndViews);fragment.Accept(referenceVisitor);}else{foreach (var error in errors){Console.WriteLine($"Parse error: {error.Message}");}}}static void CheckCreateStatements(string sqlContent, Dictionary<string, List<string>> tableProcedures){// 匹配创建视图的语句MatchCollection viewMatches = Regex.Matches(sqlContent, @"CREATE\s+VIEW\s+([^\s(]+)", RegexOptions.IgnoreCase);foreach (Match match in viewMatches){Console.WriteLine($"View: {match.Groups[1].Value}");}// 匹配创建存储过程的语句MatchCollection sprocMatches = Regex.Matches(sqlContent, @"CREATE\s+PROC(?:EDURE)?\s+([^\s(]+)", RegexOptions.IgnoreCase);foreach (Match match in sprocMatches){Console.WriteLine($"Stored Procedure: {match.Groups[1].Value}");tableProcedures[match.Groups[1].Value] = new List<string>();}// 匹配创建函数的语句MatchCollection functionMatches = Regex.Matches(sqlContent, @"CREATE\s+(?:FUNCTION|AGGREGATE)\s+([^\s(]+)", RegexOptions.IgnoreCase);foreach (Match match in functionMatches){Console.WriteLine($"User Defined Function: {match.Groups[1].Value}");}}static List<string> SortTablesByDependency(Dictionary<string, List<string>> dataRelations, HashSet<string> allTablesAndViews){var sorted = new List<string>();var visited = new HashSet<string>();foreach (var table in allTablesAndViews){TopologicalSort(table, dataRelations, sorted, visited);}return sorted;}static void TopologicalSort(string node, Dictionary<string, List<string>> dataRelations, List<string> sorted, HashSet<string> visited){if (visited.Contains(node)) return;visited.Add(node);if (dataRelations.ContainsKey(node)){foreach (var child in dataRelations[node]){TopologicalSort(child, dataRelations, sorted, visited);}}sorted.Insert(0, node);}static void WriteToExcel(Dictionary<string, List<string>> dataRelations, List<string> sortedTables, Dictionary<string, List<string>> tableProcedures){using (var package = new ExcelPackage()){var worksheet1 = package.Workbook.Worksheets.Add("Data Relations");worksheet1.Cells[1, 1].Value = "Table/View Name";worksheet1.Cells[1, 2].Value = "Generation Path";int row = 2;foreach (var table in sortedTables){worksheet1.Cells[row, 1].Value = table;worksheet1.Cells[row, 2].Value = GetGenerationPath(table, dataRelations);row++;}var worksheet2 = package.Workbook.Worksheets.Add("Sorted Tables");worksheet2.Cells[1, 1].Value = "Table/View Name";for (int i = 0; i < sortedTables.Count; i++){worksheet2.Cells[i + 2, 1].Value = sortedTables[i];}var worksheet3 = package.Workbook.Worksheets.Add("Table - Procedure Mapping");worksheet3.Cells[1, 1].Value = "Table/View Name";worksheet3.Cells[1, 2].Value = "Stored Procedures";row = 2;foreach (var table in sortedTables){worksheet3.Cells[row, 1].Value = table;worksheet3.Cells[row, 2].Value = string.Join(", ", tableProcedures.GetValueOrDefault(table, new List<string>()));row++;}FileInfo file = new FileInfo("DataBloodline.xlsx");package.SaveAs(file);}}static string GetGenerationPath(string table, Dictionary<string, List<string>> dataRelations){if (!dataRelations.ContainsKey(table)) return table;return table + "->" + string.Join("->", dataRelations[table].Select(t => GetGenerationPath(t, dataRelations)));}
}class ReferenceVisitor : TSqlFragmentVisitor
{private readonly Dictionary<string, List<string>> dataRelations;private readonly HashSet<string> allTablesAndViews;public ReferenceVisitor(Dictionary<string, List<string>> dataRelations, HashSet<string> allTablesAndViews){this.dataRelations = dataRelations;this.allTablesAndViews = allTablesAndViews;}public override void Visit(SelectQuerySpecification node){VisitTableReferences(node.FromClause);}public override void Visit(InsertStatement node){AddReference(node.TableReference);}public override void Visit(UpdateStatement node){AddReference(node.TableReference);}public override void Visit(DeleteStatement node){AddReference(node.TableReference);}public override void Visit(ViewDefinition node){AddReference(node.SchemaObjectName);}private void VisitTableReferences(FromClause fromClause){if (fromClause!= null){foreach (var tableReference in fromClause.TableReferences){AddReference(tableReference);}}}private void AddReference(TSqlFragment fragment){if (fragment is TableReference tableReference){var name = tableReference.SchemaObject.BaseIdentifier.Value;AddInfo(name);}else if (fragment is SchemaObjectName schemaObjectName){var name = schemaObjectName.BaseIdentifier.Value;AddInfo(name);}}private void AddInfo(string name){allTablesAndViews.Add(name);if (!dataRelations.ContainsKey(name)){dataRelations[name] = new List<string>();}}
}

代码说明：

Main方法：初始化变量并调用 ProcessSqlFiles 方法，最后调用 WriteToExcel 方法将结果输出到Excel文件。
ProcessSqlFiles方法：遍历指定目录及其子目录下的所有 .sql 文件，并对每个文件的内容执行 ProcessSqlContent 和 CheckCreateStatements 方法。
ProcessSqlContent方法：使用 Microsoft.SqlServer.TransactSql.ScriptDom 库解析SQL内容，获取引用关系。
CheckCreateStatements方法：使用正则表达式匹配SQL内容中的创建视图、存储过程和函数的语句，并更新 tableProcedures 字典。
SortTablesByDependency方法：使用拓扑排序按依赖性从小到大排序表的顺序。
WriteToExcel方法：使用EPPlus库将数据血缘关系的三张表输出到Excel文件。
ReferenceVisitor类：继承自 TSqlFragmentVisitor ，用于收集SQL语句中的表和视图引用关系。

请确保你已经安装了EPPlus库，可以通过NuGet安装：

Install-Package EPPlus