C# 获取MySql的数据库结构信息

原创已于 2024-01-03 21:42:38 修改 · 230 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #c#

于 2023-02-03 17:31:50 首次发布

该代码示例展示了如何使用MySql.Data.MySqlClient库获取MySQL数据库的数据库名、表信息、视图信息以及列信息。同时，也提及了SQLServer中获取所有数据库名的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.Common;

namespace ConsoleApp1
{
    class Program
    {
        static DbConnection _conn;

        static void Main(string[] args)
        {
            _conn = new MySqlConnection("Server=localhost;Port=3306;User=root;Password=;Charset=utf8");
            try
            {
                _conn.Open();
                GetDatabases();
                GetTables("Library");
                GetViews("Library");
                GetColumns("Library", "book");
            }
            finally
            {
                _conn.Close();
            }
            
            Console.ReadLine();
        }

        /// <summary>
        /// 获取所有数据库信息
        /// </summary>
        static void GetDatabases()
        {
            DataTable dt = _conn.GetSchema("Databases");
            foreach(DataRow dr in dt.Rows)
            {
                Console.WriteLine(
                    $"数据库名：{dr["DATABASE_NAME"]}，" +
                    $"字符集：{dr["DEFAULT_CHARACTER_SET_NAME"]}，" +
                    $"排序规则：{dr["DEFAULT_COLLATION_NAME"]}");
            }
        }
        /// <summary>
        /// 获取所有表信息
        /// </summary>
        /// <param name="database">数据库</param>
        static void GetTables(string database)
        {
            string[] restrictionValues = new string[4];
            restrictionValues[0] = null;        // Catalog
            restrictionValues[1] = database;    // Owner
            restrictionValues[2] = null;        // Table
            restrictionValues[3] = null;        // Column
            DataTable dt = _conn.GetSchema("Tables", restrictionValues);
            foreach (DataRow dr in dt.Rows)
            {
                Console.WriteLine(
                    $"表名：{dr["TABLE_NAME"]}，" +
                    $"创建时间：{dr["CREATE_TIME"]}，" +
                    $"排序规则：{dr["TABLE_COLLATION"]}，" +
                    $"备注：{dr["TABLE_COMMENT"]}");
            }
        }
        /// <summary>
        /// 获取所有视图信息
        /// </summary>
        /// <param name="database">数据库</param>
        static void GetViews(string database)
        {
            string[] restrictionValues = new string[4];
            restrictionValues[0] = null;        // Catalog
            restrictionValues[1] = database;    // Owner
            restrictionValues[2] = null;        // Table
            restrictionValues[3] = null;        // Column
            DataTable dt = _conn.GetSchema("Views", restrictionValues);
            foreach (DataRow dr in dt.Rows)
            {
                Console.WriteLine(
                    $"视图名：{dr["TABLE_NAME"]}，" +
                    $"定义者：{dr["DEFINER"]}，" +
                    $"安全性：{dr["SECURITY_TYPE"]}");
            }
        }
        /// <summary>
        /// 获取表或视图的列信息
        /// </summary>
        /// <param name="database">数据库</param>
        /// <param name="table">表或视图</param>
        static void GetColumns(string database, string table)
        {
            string[] restrictionValues = new string[4];
            restrictionValues[0] = null;        // Catalog
            restrictionValues[1] = database;    // Owner
            restrictionValues[2] = table;       // Table
            restrictionValues[3] = null;        // Column
            DataTable dt = _conn.GetSchema("Columns", restrictionValues);
            foreach (DataRow dr in dt.Rows)
            {
                Console.WriteLine(
                    $"字段名：{dr["COLUMN_NAME"]}，" +
                    $"默认值：{dr["COLUMN_DEFAULT"]}，" +
                    $"可空：{dr["IS_NULLABLE"]}，" +
                    $"类型：{dr["DATA_TYPE"]}，" +
                    $"文本长度：{dr["CHARACTER_MAXIMUM_LENGTH"]}，" +
                    $"数字精度：{dr["NUMERIC_PRECISION"]}，" +
                    $"小数位数：{dr["NUMERIC_SCALE"]}，" +
                    $"时间精度：{dr["DATETIME_PRECISION"]}，" +
                    $"字符集：{dr["CHARACTER_SET_NAME"]}，" +
                    $"排序规则：{dr["COLLATION_NAME"]}，" +
                    $"字段类型：{dr["COLUMN_TYPE"]}，" +
                    $"键类型：{dr["COLUMN_KEY"]}，" +
                    $"扩展：{dr["EXTRA"]}，" +
                    $"备注：{dr["COLUMN_COMMENT"]}");
            }
        }
    }
}

2. SqlServer获取所有数据库

/// <summary>
/// 取所有数据库名,添加到lvDB
/// </summary>
/// <returns></returns>
private ArrayList GetAllDataBase()
{
 ArrayList DBNameList = new ArrayList();
 SqlConnection Connection = new SqlConnection(
  String.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}","(local)","sa","adminwinter"));
 DataTable DBNameTable = new DataTable();
 SqlDataAdapter Adapter = new SqlDataAdapter("select name from master..sysdatabases", Connection);
 lock (Adapter)
 { 
  Adapter.Fill(DBNameTable);
 }
 foreach (DataRow row in DBNameTable.Rows)
 {
  DBNameList.Add(row["name"]);
 }
 return DBNameList;
}

参考网址【https://www.cnblogs.com/lgyup/p/16528729.html】