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"]}");
            }
        }
    }
}