C# 读取数据库表结构工具设计与实现思路_Asp.net

一、系统概述

读取数据库表结构是数据库管理、代码生成（如orm模型）、数据迁移等场景的基础需求。本工具基于 c# 语言和 ado.net 框架，支持多数据库兼容（sql server、mysql、postgresql），通过查询数据库系统元数据（如information_schema）获取表的列信息（名称、数据类型、约束）、主键/外键、索引等结构，输出为结构化数据（如datatable、json）或可视化报告。工具具备低侵入性（无需数据库驱动额外配置）、高扩展性（支持自定义查询）特点，适用于开发调试、数据库文档生成等场景。

二、核心设计思路

2.1 元数据来源

不同数据库通过系统表/视图暴露元数据，核心查询对象包括：

列信息：information_schema.columns（通用）、sys.columns（sql server）、information_schema.columns（mysql/postgresql）
主键信息：information_schema.key_column_usage（通用）、sys.key_constraints（sql server）
外键信息：information_schema.referential_constraints（通用）
索引信息：sys.indexes（sql server）、pg_indexes（postgresql）

2.2 多数据库兼容策略

通过抽象工厂模式封装不同数据库的查询逻辑，核心步骤：

根据数据库类型（如sqlserver、mysql）选择对应的连接字符串和查询模板；
使用dbproviderfactory创建通用数据库对象（dbconnection、dbcommand）；
执行参数化查询，避免sql注入。

三、实现步骤与代码

3.1 开发环境

语言：c# 9.0+
框架：.net 6.0（跨平台）
依赖：system.data.common（通用ado.net）、数据库驱动（如microsoft.data.sqlclient、mysqlconnector）

3.2 核心类设计

3.2.1 数据库类型枚举

public enum databasetype
{
    sqlserver,   // sql server
    mysql,       // mysql
    postgresql,  // postgresql
    oracle       // oracle（预留）
}

3.2.2 表结构信息模型

// 列信息
public class columninfo
{
    public string tablename { get; set; }    // 表名
    public string columnname { get; set; }   // 列名
    public string datatype { get; set; }     // 数据类型（如int、nvarchar(50)）
    public int maxlength { get; set; }       // 最大长度
    public bool isnullable { get; set; }     // 是否允许null
    public string defaultvalue { get; set; } // 默认值
    public int ordinalposition { get; set; }  // 列顺序
}
// 主键信息
public class primarykeyinfo
{
    public string tablename { get; set; }
    public string columnname { get; set; }
    public int keyorder { get; set; }  // 主键顺序（复合主键）
}
// 表结构汇总
public class tableschema
{
    public string tablename { get; set; }
    public list<columninfo> columns { get; set; } = new();
    public list<primarykeyinfo> primarykeys { get; set; } = new();
}

3.3 核心功能实现

3.3.1 数据库连接与查询（通用方法）

using system.data.common;
using microsoft.data.sqlclient;  // sql server驱动
using mysqlconnector;            // mysql驱动
public class dbschemareader
{
    private readonly string _connectionstring;
    private readonly databasetype _dbtype;
    public dbschemareader(string connectionstring, databasetype dbtype)
    {
        _connectionstring = connectionstring;
        _dbtype = dbtype;
    }
    // 创建数据库连接
    private dbconnection createconnection()
    {
        return _dbtype switch
        {
            databasetype.sqlserver => new sqlconnection(_connectionstring),
            databasetype.mysql => new mysqlconnection(_connectionstring),
            _ => throw new notsupportedexception($"不支持的数据库类型: {_dbtype}")
        };
    }
    // 执行查询并返回datatable
    public datatable executequery(string sql)
    {
        using var conn = createconnection();
        conn.open();
        using var cmd = conn.createcommand();
        cmd.commandtext = sql;
        using var adapter = getdataadapter(cmd);
        var dt = new datatable();
        adapter.fill(dt);
        return dt;
    }
    // 获取数据库适配器（适配不同数据库）
    private dbdataadapter getdataadapter(dbcommand cmd)
    {
        return _dbtype switch
        {
            databasetype.sqlserver => new sqldataadapter((sqlcommand)cmd),
            databasetype.mysql => new mysqldataadapter((mysqlcommand)cmd),
            _ => throw new notsupportedexception()
        };
    }
}

3.3.2 读取表列信息（以sql server为例）

// 获取指定表的所有列信息
public list<columninfo> getcolumns(string tablename)
{
    string sql = @"
        select 
            c.table_name as tablename,
            c.column_name as columnname,
            c.data_type as datatype,
            c.character_maximum_length as maxlength,
            case when c.is_nullable = 'yes' then 1 else 0 end as isnullable,
            c.column_default as defaultvalue,
            c.ordinal_position as ordinalposition
        from information_schema.columns c
        where c.table_name = @tablename
        order by c.ordinal_position";
    using var conn = createconnection();
    conn.open();
    using var cmd = conn.createcommand();
    cmd.commandtext = sql;
    cmd.parameters.add(new sqlparameter("@tablename", tablename));
    using var reader = cmd.executereader();
    var columns = new list<columninfo>();
    while (reader.read())
    {
        columns.add(new columninfo
        {
            tablename = reader["tablename"].tostring(),
            columnname = reader["columnname"].tostring(),
            datatype = reader["datatype"].tostring(),
            maxlength = reader["maxlength"] == dbnull.value ? 0 : convert.toint32(reader["maxlength"]),
            isnullable = convert.toboolean(reader["isnullable"]),
            defaultvalue = reader["defaultvalue"]?.tostring(),
            ordinalposition = convert.toint32(reader["ordinalposition"])
        });
    }
    return columns;
}

3.3.3 读取主键信息（通用方法）

// 获取指定表的主键列
public list<primarykeyinfo> getprimarykeys(string tablename)
{
    string sql = @"
        select 
            kcu.table_name as tablename,
            kcu.column_name as columnname,
            kcu.ordinal_position as keyorder
        from information_schema.key_column_usage kcu
        join information_schema.table_constraints tc 
            on kcu.constraint_name = tc.constraint_name
        where tc.table_name = @tablename 
            and tc.constraint_type = 'primary key'
        order by kcu.ordinal_position";
    // 执行查询（类似getcolumns方法，略）
    // ...
}

3.3.4 整合表结构（完整示例）

// 获取指定表的所有结构信息
public tableschema gettableschema(string tablename)
{
    var schema = new tableschema { tablename = tablename };
    schema.columns = getcolumns(tablename);
    schema.primarykeys = getprimarykeys(tablename);
    return schema;
}

参考代码 c#-读取数据库表结构 www.youwenfan.com/contentcss/123124.html

四、关键技术点

4.1 多数据库适配

连接字符串差异：
- sql server："server=.;database=testdb;user id=sa;password=123456;"
- mysql："server=localhost;database=testdb;uid=root;pwd=123456;"
查询语法差异：部分数据库（如oracle）的系统表名称不同，需调整sql模板。

4.2 性能优化

批量查询：通过information_schema一次性获取所有表结构，避免多次查询；
缓存机制：对频繁访问的表结构缓存结果（如用memorycache）。

4.3 异常处理

连接失败：捕获dbexception，提示“数据库连接失败，请检查连接字符串”；
表不存在：查询前先判断表是否存在（如select 1 from information_schema.tables where table_name = @tablename）。

五、扩展功能

5.1 可视化报告（json/html）

将tableschema对象序列化为json或html，示例：

// 序列化为json
var json = jsonserializer.serialize(schema, new jsonserializeroptions { writeindented = true });
// 生成html表格
string html = $"<h1>{schema.tablename} 结构</h1><table border='1'>...";

5.2 代码生成（orm模型）

根据表结构自动生成c#实体类：

// 生成实体类代码
string generateentityclass(tableschema schema)
{
    var sb = new stringbuilder();
    sb.appendline($"public class {schema.tablename}");
    sb.appendline("{");
    foreach (var col in schema.columns)
    {
        string type = mapdbtypetocsharp(col.datatype);
        sb.appendline($"    public {type} {col.columnname} {{ get; set; }}");
    }
    sb.appendline("}");
    return sb.tostring();
}
// 数据库类型映射c#类型
private string mapdbtypetocsharp(string dbtype)
{
    return dbtype.tolower() switch
    {
        "int" => "int",
        "nvarchar" => "string",
        "datetime" => "datetime",
        _ => "object"
    };
}