1. 项目背景与核心价值在数据库应用开发过程中表结构信息是最基础的元数据。无论是生成文档、进行数据迁移还是开发ORM工具准确获取表结构都是关键的第一步。传统方式需要开发人员手动查询系统表或编写复杂SQL这个过程既耗时又容易出错。我最近接手的一个数据中台项目需要对接7种不同类型的数据库频繁的表结构变更让团队苦不堪言。每次变更后开发、测试、文档三个团队都要各自执行查询不仅效率低下还经常出现版本不一致的情况。这促使我开发了这个C#数据库表结构读取工具。这个工具的核心价值在于统一访问接口封装不同数据库的系统表查询差异标准化输出将表结构信息转化为统一的对象模型自动化处理支持批量导出和变更对比功能2. 技术方案设计2.1 整体架构设计采用三层架构设计[数据访问层] → [业务逻辑层] → [表示层] ↑ ↑ [数据库驱动] [表结构模型]数据访问层使用ADO.NET基础类库通过DbProviderFactory实现多数据库支持。业务逻辑层定义统一的表结构模型Table/Column/Key等实体类表示层提供控制台和WinForms两种界面。2.2 关键技术选型ADO.NET核心组件DbConnection数据库连接基础DbCommand执行查询命令DataReader高效读取查询结果Parameter防止SQL注入反射技术 动态创建实体类简化不同数据库的适配工作。例如var providerType Assembly.Load(System.Data).GetType($System.Data.{providerName}Client.{providerName}ClientFactory);设计模式应用工厂模式创建不同数据库的访问对象策略模式处理不同数据库的元数据查询逻辑装饰器模式添加缓存等扩展功能3. 核心实现细节3.1 数据库连接管理实现通用的连接字符串构建器public class DbConnector { public static DbConnection CreateConnection(string providerName, string connectionString) { var factory DbProviderFactories.GetFactory(providerName); var conn factory.CreateConnection(); conn.ConnectionString connectionString; return conn; } // 支持自动识别连接字符串中的关键参数 public static string BuildConnectionString(DbType dbType, string server, string database, string userId, string password) { return dbType switch { DbType.SqlServer $Server{server};Database{database};User Id{userId};Password{password};, DbType.MySQL $Server{server};Database{database};Uid{userId};Pwd{password};, _ throw new NotSupportedException() }; } }3.2 表结构信息查询针对SQL Server的元数据查询示例public ListTableSchema GetTables(DbConnection connection) { var tables new ListTableSchema(); using var cmd connection.CreateCommand(); cmd.CommandText SELECT t.name AS TableName, s.name AS SchemaName, ep.value AS Description FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id LEFT JOIN sys.extended_properties ep ON ep.major_id t.object_id AND ep.minor_id 0 AND ep.name MS_Description; using var reader cmd.ExecuteReader(); while (reader.Read()) { tables.Add(new TableSchema { Name reader[TableName].ToString(), Schema reader[SchemaName].ToString(), Description reader[Description]?.ToString() }); } return tables; }3.3 列信息获取封装列元数据查询时需要注意处理不同数据类型public ListColumnSchema GetColumns(DbConnection connection, string tableName) { var columns new ListColumnSchema(); // 获取基础列信息 using var cmd connection.CreateCommand(); cmd.CommandText SELECT c.name, tp.name AS DataType, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, dc.definition AS DefaultValue, ep.value AS Description FROM sys.columns c INNER JOIN sys.types tp ON c.user_type_id tp.user_type_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id LEFT JOIN sys.extended_properties ep ON ep.major_id c.object_id AND ep.minor_id c.column_id AND ep.name MS_Description WHERE c.object_id OBJECT_ID(tableName); cmd.Parameters.Add(new SqlParameter(tableName, tableName)); using var reader cmd.ExecuteReader(); while (reader.Read()) { columns.Add(new ColumnSchema { Name reader[name].ToString(), DataType reader[DataType].ToString(), Length Convert.ToInt32(reader[max_length]), IsNullable Convert.ToBoolean(reader[is_nullable]), IsIdentity Convert.ToBoolean(reader[is_identity]), DefaultValue reader[DefaultValue]?.ToString(), Description reader[Description]?.ToString() }); } return columns; }4. 多数据库支持实现4.1 数据库差异处理策略不同数据库的元数据存储方式差异很大我们通过策略模式来处理public interface IDbSchemaStrategy { ListTableSchema GetTables(DbConnection connection); ListColumnSchema GetColumns(DbConnection connection, string tableName); ListForeignKeySchema GetForeignKeys(DbConnection connection, string tableName); } // SQL Server实现 public class SqlServerSchemaStrategy : IDbSchemaStrategy { /*...*/ } // MySQL实现 public class MySqlSchemaStrategy : IDbSchemaStrategy { public ListTableSchema GetTables(DbConnection connection) { var tables new ListTableSchema(); using var cmd connection.CreateCommand(); cmd.CommandText SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE BASE TABLE AND TABLE_SCHEMA DATABASE(); using var reader cmd.ExecuteReader(); while (reader.Read()) { tables.Add(new TableSchema { Name reader[TABLE_NAME].ToString(), Schema reader[TABLE_SCHEMA].ToString(), Description reader[TABLE_COMMENT]?.ToString() }); } return tables; } // 其他方法实现... }4.2 数据库类型自动检测通过分析连接字符串自动识别数据库类型public DbType DetectDbType(string connectionString) { if (connectionString.Contains(Server) connectionString.Contains(Database)) return DbType.SqlServer; if (connectionString.Contains(Data Source) connectionString.Contains(User ID)) return DbType.Oracle; if (connectionString.Contains(Uid) || connectionString.Contains(User ID)) return DbType.MySQL; throw new NotSupportedException(Unrecognized database type); }5. 高级功能实现5.1 表关系图生成基于获取的外键信息生成关系图public void GenerateRelationshipDiagram(ListTableSchema tables) { var sb new StringBuilder(); sb.AppendLine(startuml); sb.AppendLine(hide circle); sb.AppendLine(skinparam linetype ortho); foreach (var table in tables) { sb.AppendLine($entity \{table.Schema}.{table.Name}\ {{); foreach (var column in table.Columns) { sb.AppendLine($ {column.Name} : {column.DataType} (column.IsPrimaryKey ? PK : ) (column.IsForeignKey ? FK : )); } sb.AppendLine(}); } foreach (var table in tables) { foreach (var fk in table.ForeignKeys) { sb.AppendLine($\{fk.PkTableSchema}.{fk.PkTableName}\ ||..o{{ \{fk.FkTableSchema}.{fk.FkTableName}\); } } sb.AppendLine(enduml); File.WriteAllText(schema.puml, sb.ToString()); }5.2 变更对比功能实现表结构版本对比public ListSchemaDifference CompareSchemas(DatabaseSchema oldSchema, DatabaseSchema newSchema) { var differences new ListSchemaDifference(); // 检查被删除的表 foreach (var oldTable in oldSchema.Tables) { if (!newSchema.Tables.Any(t t.Equals(oldTable))) { differences.Add(new SchemaDifference { ChangeType ChangeType.TableDropped, ObjectName oldTable.FullName }); } } // 检查新增的表 foreach (var newTable in newSchema.Tables) { if (!oldSchema.Tables.Any(t t.Equals(newTable))) { differences.Add(new SchemaDifference { ChangeType ChangeType.TableCreated, ObjectName newTable.FullName }); } } // 检查表结构变更 foreach (var oldTable in oldSchema.Tables) { var newTable newSchema.Tables.FirstOrDefault(t t.Equals(oldTable)); if (newTable ! null) { // 列变更检查... // 索引变更检查... // 约束变更检查... } } return differences; }6. 性能优化技巧6.1 批量查询优化避免为每个表单独查询列信息public DatabaseSchema GetFullSchema(DbConnection connection) { var schema new DatabaseSchema(); // 一次性获取所有表的基本信息 schema.Tables GetTables(connection); // 使用单个查询获取所有列信息SQL Server示例 var allColumns new Dictionarystring, ListColumnSchema(); using (var cmd connection.CreateCommand()) { cmd.CommandText SELECT OBJECT_NAME(c.object_id) AS TableName, SCHEMA_NAME(t.schema_id) AS SchemaName, c.name, tp.name AS DataType, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, dc.definition AS DefaultValue, ep.value AS Description FROM sys.columns c INNER JOIN sys.tables t ON c.object_id t.object_id INNER JOIN sys.types tp ON c.user_type_id tp.user_type_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id LEFT JOIN sys.extended_properties ep ON ep.major_id c.object_id AND ep.minor_id c.column_id AND ep.name MS_Description; using var reader cmd.ExecuteReader(); while (reader.Read()) { var tableKey ${reader[SchemaName]}.{reader[TableName]}; if (!allColumns.ContainsKey(tableKey)) allColumns[tableKey] new ListColumnSchema(); allColumns[tableKey].Add(new ColumnSchema { // 填充列信息... }); } } // 将列信息关联到对应的表 foreach (var table in schema.Tables) { var tableKey ${table.Schema}.{table.Name}; if (allColumns.TryGetValue(tableKey, out var columns)) { table.Columns columns; } } return schema; }6.2 缓存机制实现添加内存缓存减少数据库查询public class CachedSchemaReader : IDbSchemaReader { private readonly IDbSchemaReader _innerReader; private readonly MemoryCache _cache new MemoryCache(new MemoryCacheOptions()); public CachedSchemaReader(IDbSchemaReader innerReader) { _innerReader innerReader; } public DatabaseSchema GetSchema(DbConnection connection) { var cacheKey $Schema_{connection.Database}; return _cache.GetOrCreate(cacheKey, entry { entry.AbsoluteExpirationRelativeToNow TimeSpan.FromMinutes(30); return _innerReader.GetSchema(connection); }); } }7. 异常处理与日志记录7.1 健壮的错误处理处理常见的数据库访问异常public DatabaseSchema SafeGetSchema(DbConnection connection) { try { if (connection.State ! ConnectionState.Open) connection.Open(); return GetSchema(connection); } catch (SqlException ex) when (ex.Number 4060) // 无效数据库 { Logger.Error($Invalid database: {connection.Database}); throw new SchemaReaderException(Invalid database specified, ex); } catch (SqlException ex) when (ex.Number 18456) // 登录失败 { Logger.Error(Authentication failed); throw new SchemaReaderException(Authentication failed, ex); } catch (DbException ex) { Logger.Error($Database error: {ex.Message}); throw new SchemaReaderException(Database operation failed, ex); } finally { connection.Close(); } }7.2 详细的日志记录使用结构化日志记录操作过程public class SchemaReaderLogger { private readonly ILogger _logger; public SchemaReaderLogger(ILogger logger) { _logger logger; } public void LogSchemaReading(string database, int tableCount) { _logger.LogInformation(Schema reading completed for {Database} with {TableCount} tables, database, tableCount); } public void LogColumnDetails(string tableName, int columnCount) { _logger.LogDebug(Table {TableName} contains {ColumnCount} columns, tableName, columnCount); } }8. 实际应用案例8.1 生成数据库文档将表结构信息输出为Markdown文档public void GenerateMarkdownDocument(DatabaseSchema schema, string outputPath) { var sb new StringBuilder(); sb.AppendLine($# Database Schema Documentation); sb.AppendLine($## {schema.DatabaseName}); sb.AppendLine($Generated at: {DateTime.Now:yyyy-MM-dd HH:mm:ss}); foreach (var table in schema.Tables.OrderBy(t t.Schema).ThenBy(t t.Name)) { sb.AppendLine($\n### {table.Schema}.{table.Name}); if (!string.IsNullOrEmpty(table.Description)) sb.AppendLine($\n{table.Description}\n); sb.AppendLine(| Column | Type | Nullable | Default | Description |); sb.AppendLine(|--------|------|----------|---------|-------------|); foreach (var column in table.Columns) { sb.AppendLine($| {column.Name} | {column.DataType} $| {(column.IsNullable ? ✓ : )} $| {column.DefaultValue ?? } $| {column.Description ?? } |); } if (table.ForeignKeys.Any()) { sb.AppendLine(\n#### Foreign Keys); foreach (var fk in table.ForeignKeys) { sb.AppendLine($- {string.Join(, , fk.FkColumns)} → ${fk.PkTableSchema}.{fk.PkTableName}({string.Join(, , fk.PkColumns)})); } } } File.WriteAllText(outputPath, sb.ToString()); }8.2 与ORM框架集成为Entity Framework Core生成实体类public void GenerateEfCoreEntities(DatabaseSchema schema, string outputDir) { foreach (var table in schema.Tables) { var sb new StringBuilder(); sb.AppendLine(using System;); sb.AppendLine(using System.ComponentModel.DataAnnotations;); sb.AppendLine(using System.ComponentModel.DataAnnotations.Schema;); sb.AppendLine(); sb.AppendLine($namespace {schema.DatabaseName}.Entities); sb.AppendLine({); // 类注释 if (!string.IsNullOrEmpty(table.Description)) { sb.AppendLine( /// summary); sb.AppendLine($ /// {table.Description}); sb.AppendLine( /// /summary); } sb.AppendLine($ [Table(\{table.Name}\, Schema \{table.Schema}\)]); sb.AppendLine($ public class {ToPascalCase(table.Name)}); sb.AppendLine( {); // 属性生成 foreach (var column in table.Columns) { if (!string.IsNullOrEmpty(column.Description)) { sb.AppendLine( /// summary); sb.AppendLine($ /// {column.Description}); sb.AppendLine( /// /summary); } if (column.IsPrimaryKey) sb.AppendLine( [Key]); if (column.IsIdentity) sb.AppendLine( [DatabaseGenerated(DatabaseGeneratedOption.Identity)]); sb.AppendLine($ public {GetClrType(column.DataType)} {ToPascalCase(column.Name)} {{ get; set; }}); sb.AppendLine(); } sb.AppendLine( }); sb.AppendLine(}); var className ToPascalCase(table.Name); File.WriteAllText(Path.Combine(outputDir, ${className}.cs), sb.ToString()); } } private string GetClrType(string dbType) { return dbType.ToLower() switch { int int, varchar string, datetime DateTime, decimal decimal, bit bool, _ object }; }9. 部署与使用指南9.1 命令行工具使用实现命令行接口public static void Main(string[] args) { var parser new CommandLineParser(); parser.AddOption(connectionString, c, true, Database connection string); parser.AddOption(output, o, false, Output file path); parser.AddOption(format, f, false, Output format (json|markdown|html)); if (!parser.Parse(args)) { Console.WriteLine(Usage: SchemaTool -c connectionString [-o output] [-f format]); return; } try { var connectionString parser.GetOptionValue(c); var outputFormat parser.GetOptionValue(f) ?? json; var outputPath parser.GetOptionValue(o); using var connection DbConnector.CreateConnection(connectionString); var schema new DatabaseSchemaReader().GetSchema(connection); switch (outputFormat.ToLower()) { case markdown: var markdown new MarkdownExporter().Export(schema); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(markdown); else File.WriteAllText(outputPath, markdown); break; case html: var html new HtmlExporter().Export(schema); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(html); else File.WriteAllText(outputPath, html); break; default: var json JsonSerializer.Serialize(schema, new JsonSerializerOptions { WriteIndented true }); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(json); else File.WriteAllText(outputPath, json); break; } } catch (Exception ex) { Console.Error.WriteLine($Error: {ex.Message}); Environment.Exit(1); } }9.2 图形界面设计WinForms主界面关键代码public class MainForm : Form { private readonly IDbSchemaReader _schemaReader; private TreeView _schemaTree; private PropertyGrid _propertyGrid; public MainForm() { _schemaReader new DatabaseSchemaReader(); InitializeComponents(); } private void InitializeComponents() { // 布局设置 this.Text Database Schema Explorer; this.Size new Size(800, 600); // 添加控件 var splitContainer new SplitContainer { Dock DockStyle.Fill, SplitterDistance 250 }; _schemaTree new TreeView { Dock DockStyle.Fill, ShowRootLines true, HideSelection false }; _schemaTree.AfterSelect SchemaTree_AfterSelect; _propertyGrid new PropertyGrid { Dock DockStyle.Fill, ToolbarVisible false }; splitContainer.Panel1.Controls.Add(_schemaTree); splitContainer.Panel2.Controls.Add(_propertyGrid); this.Controls.Add(splitContainer); // 添加菜单 var menuStrip new MenuStrip(); var fileMenu new ToolStripMenuItem(File); fileMenu.DropDownItems.Add(Connect..., null, Connect_Click); fileMenu.DropDownItems.Add(Export..., null, Export_Click); fileMenu.DropDownItems.Add(new ToolStripSeparator()); fileMenu.DropDownItems.Add(Exit, null, (s, e) Close()); menuStrip.Items.Add(fileMenu); this.MainMenuStrip menuStrip; this.Controls.Add(menuStrip); } private void Connect_Click(object sender, EventArgs e) { using var dialog new ConnectionDialog(); if (dialog.ShowDialog() DialogResult.OK) { try { Cursor Cursors.WaitCursor; var schema _schemaReader.GetSchema(dialog.Connection); PopulateTreeView(schema); } catch (Exception ex) { MessageBox.Show($Error reading schema: {ex.Message}, Error, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Cursor Cursors.Default; } } } private void PopulateTreeView(DatabaseSchema schema) { _schemaTree.BeginUpdate(); _schemaTree.Nodes.Clear(); var dbNode new TreeNode(schema.DatabaseName) { Tag schema }; var tablesNode new TreeNode(Tables); foreach (var table in schema.Tables) { var tableNode new TreeNode(${table.Schema}.{table.Name}) { Tag table }; var columnsNode new TreeNode(Columns); foreach (var column in table.Columns) { columnsNode.Nodes.Add(new TreeNode(column.Name) { Tag column }); } tableNode.Nodes.Add(columnsNode); if (table.ForeignKeys.Any()) { var fksNode new TreeNode(Foreign Keys); foreach (var fk in table.ForeignKeys) { fksNode.Nodes.Add(new TreeNode( ${string.Join(,, fk.FkColumns)} → {fk.PkTableName}({string.Join(,, fk.PkColumns)})) { Tag fk }); } tableNode.Nodes.Add(fksNode); } tablesNode.Nodes.Add(tableNode); } dbNode.Nodes.Add(tablesNode); _schemaTree.Nodes.Add(dbNode); _schemaTree.EndUpdate(); dbNode.Expand(); } private void SchemaTree_AfterSelect(object sender, TreeViewEventArgs e) { _propertyGrid.SelectedObject e.Node.Tag; } }10. 扩展与改进方向10.1 支持更多数据库类型目前工具支持SQL Server、MySQL和PostgreSQL可以扩展支持Oracle查询ALL_TABLES/ALL_TAB_COLUMNS视图SQLite查询sqlite_master表DB2查询SYSCAT.TABLES目录视图10.2 添加数据抽样功能在获取表结构的同时可以添加数据抽样功能帮助开发人员理解数据public DataTable SampleTableData(DbConnection connection, string tableName, int sampleSize 100) { var data new DataTable(); using var cmd connection.CreateCommand(); cmd.CommandText $SELECT TOP {sampleSize} * FROM {tableName}; using var adapter DbProviderFactories.GetFactory(connection).CreateDataAdapter(); adapter.SelectCommand cmd; adapter.Fill(data); return data; }10.3 集成到开发流程可以将工具集成到CI/CD流程中自动生成和比较数据库文档# 示例PowerShell脚本 $schemaBefore .\SchemaTool.exe -c $connectionString -f json | ConvertFrom-Json # 执行数据库迁移... $schemaAfter .\SchemaTool.exe -c $connectionString -f json | ConvertFrom-Json Compare-Object $schemaBefore.Tables $schemaAfter.Tables -Property Name | Format-Table在实际项目中这个工具已经帮助我们团队将数据库文档更新时间从平均2小时缩短到5分钟同时消除了人为错误导致的文档不一致问题。特别是在敏捷开发环境中能够快速响应数据库变更需求为团队提供了可靠的元数据支持。
C#实现多数据库表结构读取工具开发指南
1. 项目背景与核心价值在数据库应用开发过程中表结构信息是最基础的元数据。无论是生成文档、进行数据迁移还是开发ORM工具准确获取表结构都是关键的第一步。传统方式需要开发人员手动查询系统表或编写复杂SQL这个过程既耗时又容易出错。我最近接手的一个数据中台项目需要对接7种不同类型的数据库频繁的表结构变更让团队苦不堪言。每次变更后开发、测试、文档三个团队都要各自执行查询不仅效率低下还经常出现版本不一致的情况。这促使我开发了这个C#数据库表结构读取工具。这个工具的核心价值在于统一访问接口封装不同数据库的系统表查询差异标准化输出将表结构信息转化为统一的对象模型自动化处理支持批量导出和变更对比功能2. 技术方案设计2.1 整体架构设计采用三层架构设计[数据访问层] → [业务逻辑层] → [表示层] ↑ ↑ [数据库驱动] [表结构模型]数据访问层使用ADO.NET基础类库通过DbProviderFactory实现多数据库支持。业务逻辑层定义统一的表结构模型Table/Column/Key等实体类表示层提供控制台和WinForms两种界面。2.2 关键技术选型ADO.NET核心组件DbConnection数据库连接基础DbCommand执行查询命令DataReader高效读取查询结果Parameter防止SQL注入反射技术 动态创建实体类简化不同数据库的适配工作。例如var providerType Assembly.Load(System.Data).GetType($System.Data.{providerName}Client.{providerName}ClientFactory);设计模式应用工厂模式创建不同数据库的访问对象策略模式处理不同数据库的元数据查询逻辑装饰器模式添加缓存等扩展功能3. 核心实现细节3.1 数据库连接管理实现通用的连接字符串构建器public class DbConnector { public static DbConnection CreateConnection(string providerName, string connectionString) { var factory DbProviderFactories.GetFactory(providerName); var conn factory.CreateConnection(); conn.ConnectionString connectionString; return conn; } // 支持自动识别连接字符串中的关键参数 public static string BuildConnectionString(DbType dbType, string server, string database, string userId, string password) { return dbType switch { DbType.SqlServer $Server{server};Database{database};User Id{userId};Password{password};, DbType.MySQL $Server{server};Database{database};Uid{userId};Pwd{password};, _ throw new NotSupportedException() }; } }3.2 表结构信息查询针对SQL Server的元数据查询示例public ListTableSchema GetTables(DbConnection connection) { var tables new ListTableSchema(); using var cmd connection.CreateCommand(); cmd.CommandText SELECT t.name AS TableName, s.name AS SchemaName, ep.value AS Description FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id s.schema_id LEFT JOIN sys.extended_properties ep ON ep.major_id t.object_id AND ep.minor_id 0 AND ep.name MS_Description; using var reader cmd.ExecuteReader(); while (reader.Read()) { tables.Add(new TableSchema { Name reader[TableName].ToString(), Schema reader[SchemaName].ToString(), Description reader[Description]?.ToString() }); } return tables; }3.3 列信息获取封装列元数据查询时需要注意处理不同数据类型public ListColumnSchema GetColumns(DbConnection connection, string tableName) { var columns new ListColumnSchema(); // 获取基础列信息 using var cmd connection.CreateCommand(); cmd.CommandText SELECT c.name, tp.name AS DataType, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, dc.definition AS DefaultValue, ep.value AS Description FROM sys.columns c INNER JOIN sys.types tp ON c.user_type_id tp.user_type_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id LEFT JOIN sys.extended_properties ep ON ep.major_id c.object_id AND ep.minor_id c.column_id AND ep.name MS_Description WHERE c.object_id OBJECT_ID(tableName); cmd.Parameters.Add(new SqlParameter(tableName, tableName)); using var reader cmd.ExecuteReader(); while (reader.Read()) { columns.Add(new ColumnSchema { Name reader[name].ToString(), DataType reader[DataType].ToString(), Length Convert.ToInt32(reader[max_length]), IsNullable Convert.ToBoolean(reader[is_nullable]), IsIdentity Convert.ToBoolean(reader[is_identity]), DefaultValue reader[DefaultValue]?.ToString(), Description reader[Description]?.ToString() }); } return columns; }4. 多数据库支持实现4.1 数据库差异处理策略不同数据库的元数据存储方式差异很大我们通过策略模式来处理public interface IDbSchemaStrategy { ListTableSchema GetTables(DbConnection connection); ListColumnSchema GetColumns(DbConnection connection, string tableName); ListForeignKeySchema GetForeignKeys(DbConnection connection, string tableName); } // SQL Server实现 public class SqlServerSchemaStrategy : IDbSchemaStrategy { /*...*/ } // MySQL实现 public class MySqlSchemaStrategy : IDbSchemaStrategy { public ListTableSchema GetTables(DbConnection connection) { var tables new ListTableSchema(); using var cmd connection.CreateCommand(); cmd.CommandText SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE BASE TABLE AND TABLE_SCHEMA DATABASE(); using var reader cmd.ExecuteReader(); while (reader.Read()) { tables.Add(new TableSchema { Name reader[TABLE_NAME].ToString(), Schema reader[TABLE_SCHEMA].ToString(), Description reader[TABLE_COMMENT]?.ToString() }); } return tables; } // 其他方法实现... }4.2 数据库类型自动检测通过分析连接字符串自动识别数据库类型public DbType DetectDbType(string connectionString) { if (connectionString.Contains(Server) connectionString.Contains(Database)) return DbType.SqlServer; if (connectionString.Contains(Data Source) connectionString.Contains(User ID)) return DbType.Oracle; if (connectionString.Contains(Uid) || connectionString.Contains(User ID)) return DbType.MySQL; throw new NotSupportedException(Unrecognized database type); }5. 高级功能实现5.1 表关系图生成基于获取的外键信息生成关系图public void GenerateRelationshipDiagram(ListTableSchema tables) { var sb new StringBuilder(); sb.AppendLine(startuml); sb.AppendLine(hide circle); sb.AppendLine(skinparam linetype ortho); foreach (var table in tables) { sb.AppendLine($entity \{table.Schema}.{table.Name}\ {{); foreach (var column in table.Columns) { sb.AppendLine($ {column.Name} : {column.DataType} (column.IsPrimaryKey ? PK : ) (column.IsForeignKey ? FK : )); } sb.AppendLine(}); } foreach (var table in tables) { foreach (var fk in table.ForeignKeys) { sb.AppendLine($\{fk.PkTableSchema}.{fk.PkTableName}\ ||..o{{ \{fk.FkTableSchema}.{fk.FkTableName}\); } } sb.AppendLine(enduml); File.WriteAllText(schema.puml, sb.ToString()); }5.2 变更对比功能实现表结构版本对比public ListSchemaDifference CompareSchemas(DatabaseSchema oldSchema, DatabaseSchema newSchema) { var differences new ListSchemaDifference(); // 检查被删除的表 foreach (var oldTable in oldSchema.Tables) { if (!newSchema.Tables.Any(t t.Equals(oldTable))) { differences.Add(new SchemaDifference { ChangeType ChangeType.TableDropped, ObjectName oldTable.FullName }); } } // 检查新增的表 foreach (var newTable in newSchema.Tables) { if (!oldSchema.Tables.Any(t t.Equals(newTable))) { differences.Add(new SchemaDifference { ChangeType ChangeType.TableCreated, ObjectName newTable.FullName }); } } // 检查表结构变更 foreach (var oldTable in oldSchema.Tables) { var newTable newSchema.Tables.FirstOrDefault(t t.Equals(oldTable)); if (newTable ! null) { // 列变更检查... // 索引变更检查... // 约束变更检查... } } return differences; }6. 性能优化技巧6.1 批量查询优化避免为每个表单独查询列信息public DatabaseSchema GetFullSchema(DbConnection connection) { var schema new DatabaseSchema(); // 一次性获取所有表的基本信息 schema.Tables GetTables(connection); // 使用单个查询获取所有列信息SQL Server示例 var allColumns new Dictionarystring, ListColumnSchema(); using (var cmd connection.CreateCommand()) { cmd.CommandText SELECT OBJECT_NAME(c.object_id) AS TableName, SCHEMA_NAME(t.schema_id) AS SchemaName, c.name, tp.name AS DataType, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, dc.definition AS DefaultValue, ep.value AS Description FROM sys.columns c INNER JOIN sys.tables t ON c.object_id t.object_id INNER JOIN sys.types tp ON c.user_type_id tp.user_type_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id c.object_id AND dc.parent_column_id c.column_id LEFT JOIN sys.extended_properties ep ON ep.major_id c.object_id AND ep.minor_id c.column_id AND ep.name MS_Description; using var reader cmd.ExecuteReader(); while (reader.Read()) { var tableKey ${reader[SchemaName]}.{reader[TableName]}; if (!allColumns.ContainsKey(tableKey)) allColumns[tableKey] new ListColumnSchema(); allColumns[tableKey].Add(new ColumnSchema { // 填充列信息... }); } } // 将列信息关联到对应的表 foreach (var table in schema.Tables) { var tableKey ${table.Schema}.{table.Name}; if (allColumns.TryGetValue(tableKey, out var columns)) { table.Columns columns; } } return schema; }6.2 缓存机制实现添加内存缓存减少数据库查询public class CachedSchemaReader : IDbSchemaReader { private readonly IDbSchemaReader _innerReader; private readonly MemoryCache _cache new MemoryCache(new MemoryCacheOptions()); public CachedSchemaReader(IDbSchemaReader innerReader) { _innerReader innerReader; } public DatabaseSchema GetSchema(DbConnection connection) { var cacheKey $Schema_{connection.Database}; return _cache.GetOrCreate(cacheKey, entry { entry.AbsoluteExpirationRelativeToNow TimeSpan.FromMinutes(30); return _innerReader.GetSchema(connection); }); } }7. 异常处理与日志记录7.1 健壮的错误处理处理常见的数据库访问异常public DatabaseSchema SafeGetSchema(DbConnection connection) { try { if (connection.State ! ConnectionState.Open) connection.Open(); return GetSchema(connection); } catch (SqlException ex) when (ex.Number 4060) // 无效数据库 { Logger.Error($Invalid database: {connection.Database}); throw new SchemaReaderException(Invalid database specified, ex); } catch (SqlException ex) when (ex.Number 18456) // 登录失败 { Logger.Error(Authentication failed); throw new SchemaReaderException(Authentication failed, ex); } catch (DbException ex) { Logger.Error($Database error: {ex.Message}); throw new SchemaReaderException(Database operation failed, ex); } finally { connection.Close(); } }7.2 详细的日志记录使用结构化日志记录操作过程public class SchemaReaderLogger { private readonly ILogger _logger; public SchemaReaderLogger(ILogger logger) { _logger logger; } public void LogSchemaReading(string database, int tableCount) { _logger.LogInformation(Schema reading completed for {Database} with {TableCount} tables, database, tableCount); } public void LogColumnDetails(string tableName, int columnCount) { _logger.LogDebug(Table {TableName} contains {ColumnCount} columns, tableName, columnCount); } }8. 实际应用案例8.1 生成数据库文档将表结构信息输出为Markdown文档public void GenerateMarkdownDocument(DatabaseSchema schema, string outputPath) { var sb new StringBuilder(); sb.AppendLine($# Database Schema Documentation); sb.AppendLine($## {schema.DatabaseName}); sb.AppendLine($Generated at: {DateTime.Now:yyyy-MM-dd HH:mm:ss}); foreach (var table in schema.Tables.OrderBy(t t.Schema).ThenBy(t t.Name)) { sb.AppendLine($\n### {table.Schema}.{table.Name}); if (!string.IsNullOrEmpty(table.Description)) sb.AppendLine($\n{table.Description}\n); sb.AppendLine(| Column | Type | Nullable | Default | Description |); sb.AppendLine(|--------|------|----------|---------|-------------|); foreach (var column in table.Columns) { sb.AppendLine($| {column.Name} | {column.DataType} $| {(column.IsNullable ? ✓ : )} $| {column.DefaultValue ?? } $| {column.Description ?? } |); } if (table.ForeignKeys.Any()) { sb.AppendLine(\n#### Foreign Keys); foreach (var fk in table.ForeignKeys) { sb.AppendLine($- {string.Join(, , fk.FkColumns)} → ${fk.PkTableSchema}.{fk.PkTableName}({string.Join(, , fk.PkColumns)})); } } } File.WriteAllText(outputPath, sb.ToString()); }8.2 与ORM框架集成为Entity Framework Core生成实体类public void GenerateEfCoreEntities(DatabaseSchema schema, string outputDir) { foreach (var table in schema.Tables) { var sb new StringBuilder(); sb.AppendLine(using System;); sb.AppendLine(using System.ComponentModel.DataAnnotations;); sb.AppendLine(using System.ComponentModel.DataAnnotations.Schema;); sb.AppendLine(); sb.AppendLine($namespace {schema.DatabaseName}.Entities); sb.AppendLine({); // 类注释 if (!string.IsNullOrEmpty(table.Description)) { sb.AppendLine( /// summary); sb.AppendLine($ /// {table.Description}); sb.AppendLine( /// /summary); } sb.AppendLine($ [Table(\{table.Name}\, Schema \{table.Schema}\)]); sb.AppendLine($ public class {ToPascalCase(table.Name)}); sb.AppendLine( {); // 属性生成 foreach (var column in table.Columns) { if (!string.IsNullOrEmpty(column.Description)) { sb.AppendLine( /// summary); sb.AppendLine($ /// {column.Description}); sb.AppendLine( /// /summary); } if (column.IsPrimaryKey) sb.AppendLine( [Key]); if (column.IsIdentity) sb.AppendLine( [DatabaseGenerated(DatabaseGeneratedOption.Identity)]); sb.AppendLine($ public {GetClrType(column.DataType)} {ToPascalCase(column.Name)} {{ get; set; }}); sb.AppendLine(); } sb.AppendLine( }); sb.AppendLine(}); var className ToPascalCase(table.Name); File.WriteAllText(Path.Combine(outputDir, ${className}.cs), sb.ToString()); } } private string GetClrType(string dbType) { return dbType.ToLower() switch { int int, varchar string, datetime DateTime, decimal decimal, bit bool, _ object }; }9. 部署与使用指南9.1 命令行工具使用实现命令行接口public static void Main(string[] args) { var parser new CommandLineParser(); parser.AddOption(connectionString, c, true, Database connection string); parser.AddOption(output, o, false, Output file path); parser.AddOption(format, f, false, Output format (json|markdown|html)); if (!parser.Parse(args)) { Console.WriteLine(Usage: SchemaTool -c connectionString [-o output] [-f format]); return; } try { var connectionString parser.GetOptionValue(c); var outputFormat parser.GetOptionValue(f) ?? json; var outputPath parser.GetOptionValue(o); using var connection DbConnector.CreateConnection(connectionString); var schema new DatabaseSchemaReader().GetSchema(connection); switch (outputFormat.ToLower()) { case markdown: var markdown new MarkdownExporter().Export(schema); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(markdown); else File.WriteAllText(outputPath, markdown); break; case html: var html new HtmlExporter().Export(schema); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(html); else File.WriteAllText(outputPath, html); break; default: var json JsonSerializer.Serialize(schema, new JsonSerializerOptions { WriteIndented true }); if (string.IsNullOrEmpty(outputPath)) Console.WriteLine(json); else File.WriteAllText(outputPath, json); break; } } catch (Exception ex) { Console.Error.WriteLine($Error: {ex.Message}); Environment.Exit(1); } }9.2 图形界面设计WinForms主界面关键代码public class MainForm : Form { private readonly IDbSchemaReader _schemaReader; private TreeView _schemaTree; private PropertyGrid _propertyGrid; public MainForm() { _schemaReader new DatabaseSchemaReader(); InitializeComponents(); } private void InitializeComponents() { // 布局设置 this.Text Database Schema Explorer; this.Size new Size(800, 600); // 添加控件 var splitContainer new SplitContainer { Dock DockStyle.Fill, SplitterDistance 250 }; _schemaTree new TreeView { Dock DockStyle.Fill, ShowRootLines true, HideSelection false }; _schemaTree.AfterSelect SchemaTree_AfterSelect; _propertyGrid new PropertyGrid { Dock DockStyle.Fill, ToolbarVisible false }; splitContainer.Panel1.Controls.Add(_schemaTree); splitContainer.Panel2.Controls.Add(_propertyGrid); this.Controls.Add(splitContainer); // 添加菜单 var menuStrip new MenuStrip(); var fileMenu new ToolStripMenuItem(File); fileMenu.DropDownItems.Add(Connect..., null, Connect_Click); fileMenu.DropDownItems.Add(Export..., null, Export_Click); fileMenu.DropDownItems.Add(new ToolStripSeparator()); fileMenu.DropDownItems.Add(Exit, null, (s, e) Close()); menuStrip.Items.Add(fileMenu); this.MainMenuStrip menuStrip; this.Controls.Add(menuStrip); } private void Connect_Click(object sender, EventArgs e) { using var dialog new ConnectionDialog(); if (dialog.ShowDialog() DialogResult.OK) { try { Cursor Cursors.WaitCursor; var schema _schemaReader.GetSchema(dialog.Connection); PopulateTreeView(schema); } catch (Exception ex) { MessageBox.Show($Error reading schema: {ex.Message}, Error, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Cursor Cursors.Default; } } } private void PopulateTreeView(DatabaseSchema schema) { _schemaTree.BeginUpdate(); _schemaTree.Nodes.Clear(); var dbNode new TreeNode(schema.DatabaseName) { Tag schema }; var tablesNode new TreeNode(Tables); foreach (var table in schema.Tables) { var tableNode new TreeNode(${table.Schema}.{table.Name}) { Tag table }; var columnsNode new TreeNode(Columns); foreach (var column in table.Columns) { columnsNode.Nodes.Add(new TreeNode(column.Name) { Tag column }); } tableNode.Nodes.Add(columnsNode); if (table.ForeignKeys.Any()) { var fksNode new TreeNode(Foreign Keys); foreach (var fk in table.ForeignKeys) { fksNode.Nodes.Add(new TreeNode( ${string.Join(,, fk.FkColumns)} → {fk.PkTableName}({string.Join(,, fk.PkColumns)})) { Tag fk }); } tableNode.Nodes.Add(fksNode); } tablesNode.Nodes.Add(tableNode); } dbNode.Nodes.Add(tablesNode); _schemaTree.Nodes.Add(dbNode); _schemaTree.EndUpdate(); dbNode.Expand(); } private void SchemaTree_AfterSelect(object sender, TreeViewEventArgs e) { _propertyGrid.SelectedObject e.Node.Tag; } }10. 扩展与改进方向10.1 支持更多数据库类型目前工具支持SQL Server、MySQL和PostgreSQL可以扩展支持Oracle查询ALL_TABLES/ALL_TAB_COLUMNS视图SQLite查询sqlite_master表DB2查询SYSCAT.TABLES目录视图10.2 添加数据抽样功能在获取表结构的同时可以添加数据抽样功能帮助开发人员理解数据public DataTable SampleTableData(DbConnection connection, string tableName, int sampleSize 100) { var data new DataTable(); using var cmd connection.CreateCommand(); cmd.CommandText $SELECT TOP {sampleSize} * FROM {tableName}; using var adapter DbProviderFactories.GetFactory(connection).CreateDataAdapter(); adapter.SelectCommand cmd; adapter.Fill(data); return data; }10.3 集成到开发流程可以将工具集成到CI/CD流程中自动生成和比较数据库文档# 示例PowerShell脚本 $schemaBefore .\SchemaTool.exe -c $connectionString -f json | ConvertFrom-Json # 执行数据库迁移... $schemaAfter .\SchemaTool.exe -c $connectionString -f json | ConvertFrom-Json Compare-Object $schemaBefore.Tables $schemaAfter.Tables -Property Name | Format-Table在实际项目中这个工具已经帮助我们团队将数据库文档更新时间从平均2小时缩短到5分钟同时消除了人为错误导致的文档不一致问题。特别是在敏捷开发环境中能够快速响应数据库变更需求为团队提供了可靠的元数据支持。
