c# wpf-练习-完成mysql数据库实体类工具-修改版

本文主要是用来展示数据库以及数据库中包含的所有表，在根据表生成c#实体类。只做了mysql的，sqlserver没有。用了MySql.Data

运行图片：

下面是代码：
主要逻辑

using Google.Protobuf.Collections;
using MySql.Data.MySqlClient;
using Panuon.UI.Silver;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Xml.Linq;

namespace WpfApp1
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        static DbConnection _conn;
        public MainWindow()
        {
            InitializeComponent();
            
        }

        public void Grid_Loaded(object sender, RoutedEventArgs e)
        {
            _conn = new MySqlConnection(DbLib.mySqlcon);
            try
            {
                _conn.Open();
                #region 获取mysql数据库列表
                cmbColors.ItemsSource = GetDatabases();
                if (GetDatabases().Count > 0)
                {
                    // 选取数据库默认选项
                    cmbColors.SelectedIndex = 0;
                    cmbColors.SelectedItem = GetDatabases()[0].Name;
                }
                #endregion

                #region 获取数据库表列表
                if (!(string.IsNullOrEmpty(((WpfApp1.Databases)cmbColors.SelectedItem).Name)))
                {
                    cmbColorstwo.ItemsSource = GetTables(((WpfApp1.Databases)cmbColors.SelectedItem).Name);

                    if (GetTables(((WpfApp1.Databases)cmbColors.SelectedItem).Name).Count > 0)
                    {
                        // 选取数据库表默认选项
                        cmbColorstwo.SelectedIndex = 0;
                        cmbColorstwo.SelectedItem = GetTables(((WpfApp1.Databases)cmbColors.SelectedItem).Name)[0].Name;
                    }
                }

                string[] list = new string[] { };
                string strings = "11 23 aa";
                list = strings.Split().ToArray();
                int hj = list[list.Length - 1].Length;
                #endregion
                //GetTables("Library");
                //GetViews("Library");
                //GetColumns("Library", "book");
            }
            catch (Exception ex)
            {
                _conn.Close();
                MessageBox.Show(ex.Message);
            }
        }


        /// <summary>
        /// 获取所有数据库信息
        /// </summary>
         static List<WpfApp1.Databases> GetDatabases()
        {
            DataTable dt = _conn.GetSchema("Databases");
            List<WpfApp1.Databases> list = new List<WpfApp1.Databases>();
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                WpfApp1.Databases dbs = new WpfApp1.Databases();
                dbs.ID = i;
                dbs.Name = $"{dr["DATABASE_NAME"]}";
                list.Add(dbs);
                i++;
                //Console.WriteLine(
                //    $"数据库名：{dr["DATABASE_NAME"]}，" +
                //    $"字符集：{dr["DEFAULT_CHARACTER_SET_NAME"]}，" +
                //    $"排序规则：{dr["DEFAULT_COLLATION_NAME"]}");
            }
            return list;
        }
        /// <summary>
        /// 获取所有表信息
        /// </summary>
        /// <param name="database">所有数据库表</param>
        static List<WpfApp1.DataTables> GetTables(string database)
        {
            List<WpfApp1.DataTables> list = new List<WpfApp1.DataTables>();
            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);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                WpfApp1.DataTables dbs = new WpfApp1.DataTables();
                dbs.ID = i;
                dbs.Name = $"{dr["TABLE_NAME"]}";
                dbs.CREATE_TIME = $"{dr["CREATE_TIME"]}";
                list.Add(dbs);
                i++;
                //Console.WriteLine(
                //    $"表名：{dr["TABLE_NAME"]}，" +
                //    $"创建时间：{dr["CREATE_TIME"]}，" +
                //    $"排序规则：{dr["TABLE_COLLATION"]}，" +
                //    $"备注：{dr["TABLE_COMMENT"]}");
            }
            return list;
        }
        /// <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 List<WpfApp1.DataTableItem> 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"]}");
            //}
            List<WpfApp1.DataTableItem> list = new List<WpfApp1.DataTableItem>();
            foreach (DataRow dr in dt.Rows)
            {
                WpfApp1.DataTableItem dbs = new WpfApp1.DataTableItem();
                dbs.COLUMN_NAME = $"{dr["COLUMN_NAME"]}";
                dbs.COLUMN_DEFAULT = $"{dr["COLUMN_DEFAULT"]}";
                dbs.IS_NULLABLE = $"{dr["IS_NULLABLE"]}";
                dbs.DATA_TYPE = $"{dr["DATA_TYPE"]}";
                dbs.CHARACTER_MAXIMUM_LENGTH = $"{dr["CHARACTER_MAXIMUM_LENGTH"]}";
                dbs.NUMERIC_PRECISION = $"{dr["NUMERIC_PRECISION"]}";
                dbs.NUMERIC_SCALE = $"{dr["NUMERIC_SCALE"]}";
                dbs.DATETIME_PRECISION = $"{dr["DATETIME_PRECISION"]}";
                dbs.CHARACTER_SET_NAME = $"{dr["CHARACTER_SET_NAME"]}";
                dbs.COLLATION_NAME = $"{dr["COLLATION_NAME"]}";
                dbs.COLUMN_TYPE = $"{dr["COLUMN_TYPE"]}";
                dbs.COLUMN_KEY = $"{dr["COLUMN_KEY"]}";
                dbs.EXTRA = $"{dr["EXTRA"]}";
                dbs.COLUMN_COMMENT = $"{dr["COLUMN_COMMENT"]}";
                list.Add(dbs);
            }
            return list;

        }

        private void ComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void cmbColors_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            // 改变数据库同时需要改变数据库表列表，以及
        }

        private void TextBox1_KeyDown(object sender, KeyEventArgs e)
        {
            var textbox = sender as TextBox;
            if (e.Key == Key.Enter &&
                !(Keyboard.IsKeyDown(Key.LeftCtrl) || Keyboard.IsKeyDown(Key.RightCtrl)) &&
                !(Keyboard.IsKeyDown(Key.LeftShift) || Keyboard.IsKeyDown(Key.LeftShift)))
            {
                textbox.Text = "";
                e.Handled = true;
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            List<WpfApp1.DataTableItem> list = GetColumns(((WpfApp1.Databases)cmbColors.SelectedValue).Name, ((WpfApp1.DataTables)cmbColorstwo.SelectedValue).Name);
            //TextBox1.Text = "";
            //string str = "";
            //for (int s = 0; s < list.Count; s++)
            //{
            //    str += $"字段名：{list[s].COLUMN_NAME}，" +
            //        $"默认值：{list[s].COLUMN_DEFAULT}，" +
            //        $"可空：{list[s].IS_NULLABLE}，" +
            //        $"类型：{list[s].DATA_TYPE}，" +
            //        $"文本长度：{list[s].CHARACTER_MAXIMUM_LENGTH}，" +
            //        $"数字精度：{list[s].NUMERIC_PRECISION}，" +
            //        $"小数位数：{list[s].NUMERIC_SCALE}，" +
            //        $"时间精度：{list[s].DATETIME_PRECISION}，" +
            //        $"字符集：{list[s].CHARACTER_SET_NAME}，" +
            //        $"排序规则：{list[s].COLLATION_NAME}，" +
            //        $"字段类型：{list[s].COLUMN_TYPE}，" +
            //        $"键类型：{list[s].COLUMN_KEY}，" +
            //        $"扩展：{list[s].EXTRA}，" +
            //        $"备注：{list[s].COLUMN_COMMENT}" + " \n";
            // }
            TextBox1.Visibility = Visibility.Collapsed;
            dataGridOne.Visibility = Visibility.Visible;
            dataGridOne.ItemsSource = list;
        }

        private void cmbColors_SelectionChanged_1(object sender, SelectionChangedEventArgs e)
        {
            if (!string.IsNullOrEmpty(((WpfApp1.Databases)cmbColors.SelectedValue).Name))
            {
                cmbColorstwo.ItemsSource = GetTables(((WpfApp1.Databases)cmbColors.SelectedValue).Name.Replace("System.Windows.Controls.ComboBoxItem: ",""));

                if (GetTables(((WpfApp1.Databases)cmbColors.SelectedItem).Name).Count > 0)
                {
                    // 选取数据库表默认选项
                    cmbColorstwo.SelectedIndex = 0;
                    cmbColorstwo.SelectedItem = GetTables(((WpfApp1.Databases)cmbColors.SelectedItem).Name)[0].Name;
                }
            }
        }

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            
            List<WpfApp1.DataTableItem> list = GetColumns(((WpfApp1.Databases)cmbColors.SelectedValue).Name, ((WpfApp1.DataTables)cmbColorstwo.SelectedValue).Name);
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append($"public class {((WpfApp1.DataTables)cmbColorstwo.SelectedValue).Name} \r\n{{\r\n");
            for (int s = 0; s < list.Count; s++)
            {
                stringBuilder.Append($"  public {GetTypeOfColumn(list[s].DATA_TYPE.ToString(), list[s].IS_NULLABLE.ToString())} {list[s].COLUMN_NAME.ToString()} {{get;set;}}\r\n");
            }
            stringBuilder.Append("} \r\n");
            TextBox1.Visibility = Visibility.Visible;
            dataGridOne.Visibility = Visibility.Collapsed;
            TextBox1.Text = stringBuilder.ToString();
        }


        //获取列的类型
        /// <summary>
        /// 处理类型
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="nullAble">是否空</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private static string GetTypeOfColumn(string type, string nullAble)
        {
            if (type.Equals("int") && nullAble.Equals("NO"))
                return "int";
            else if (type.Equals("int") && nullAble.Equals("YES"))
                return "int?";
            else if (type.Equals("bit") && nullAble.Equals("Not"))
                return "bool";
            else if (type.Equals("bit") && nullAble.Equals("YES"))
                return "bool?";
            else if ((type.Equals("decimal") || type.Equals("numeric") || type.Equals("float") || type.Equals("real")) && nullAble.Equals("NO"))
                return "decimal";
            else if ((type.Equals("decimal") || type.Equals("numeric") || type.Equals("float") || type.Equals("real")) && nullAble.Equals("Yes"))
                return "decimal?";
            else if (type.Equals("datetime") && nullAble.Equals("YES"))
                return "DateTime?";
            else if (type.Equals("datetime") && nullAble.Equals("NO"))
                return "DateTime";
            else if (type.Equals("nchar") || type.Equals("char") || type.Equals("nvarchar") || type.Equals("varchar") || type.Equals("text"))
                return "string";
            else throw new Exception("无此类型");
        }
    }
}

涉及到的类
Databases.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WpfApp1
{
    public partial class Databases
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    public partial class DataTables
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string CREATE_TIME { get; set; }
    }

    public partial class DataTableItem
    {
        public string COLUMN_NAME { get; set; }
        public string COLUMN_DEFAULT { get; set; } // 默认值
        public string IS_NULLABLE { get; set; } // 可空
        public string DATA_TYPE { get; set; } // 类型
        public string CHARACTER_MAXIMUM_LENGTH { get; set; } // 文本长度
        public string NUMERIC_PRECISION { get; set; } // 数字长度
        public string NUMERIC_SCALE { get; set; } // 小数位数
        public string DATETIME_PRECISION { get; set; } // 时间精度
        public string CHARACTER_SET_NAME { get; set; } // 字符集
        public string COLLATION_NAME { get; set; } // 排序规则
        public string COLUMN_TYPE { get; set; } // 字段类型
        public string COLUMN_KEY { get; set; } // 键类型
        public string EXTRA { get; set; } // 扩展
        public string COLUMN_COMMENT { get; set; } // 备注

    }
}

xaml

<Window x:Class="WpfApp1.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WpfApp1"
        xmlns:system="clr-namespace:System;assembly=mscorlib"
        mc:Ignorable="d"
        Title="MainWindow" Height="500" Width="800">
    <Grid Loaded="Grid_Loaded">
        <Viewbox Stretch="fill">
            <!--使用viewbox 和canvas 是为了缩放页面的时候自动延申-->
            <Canvas Width="800" Height="500">
                <StackPanel Orientation="Horizontal" Width="800" Height="500">

                    <Label Background="#ffffff" VerticalAlignment="Top"  Margin="10,10, 0, 0" Content="数据库"/>
                    <ComboBox x:Name="cmbColors" Height="26" VerticalAlignment="Top" Width="150" Margin="4, 10, 0 0" FontSize="14" SelectionChanged="cmbColors_SelectionChanged_1" >
                        <ComboBox.ItemTemplate>
                            <DataTemplate>
                                <StackPanel Orientation="Horizontal" Height="20">
                                    <TextBlock Text="{Binding Name}" />
                                </StackPanel>
                            </DataTemplate>
                        </ComboBox.ItemTemplate>
                    </ComboBox>

                    <Label  Background="#ffffff" VerticalAlignment="Top"  Margin="10,10, 0, 0" Content="数据库表"/>
                    <ComboBox x:Name="cmbColorstwo" Height="26" VerticalAlignment="Top" Width="150" Margin="4, 10, 0 0" FontSize="14" >
                        <ComboBox.ItemTemplate>
                            <DataTemplate>
                                <StackPanel Orientation="Horizontal" Height="20">
                                    <TextBlock Text="{Binding Name}" />
                                </StackPanel>
                            </DataTemplate>
                        </ComboBox.ItemTemplate>
                    </ComboBox>

                    <Button Height="26" VerticalAlignment="Top" Margin="10,10,10,0" Background="#18a058" Foreground="#ffffff" Cursor="Hand" Content="字段详情" Click="Button_Click"/>
                    <Button Height="26" VerticalAlignment="Top" Margin="0,10,0,0" Background="#18a058" Foreground="#ffffff" Cursor="Hand" Content="转化c#类" Click="Button_Click_1"/>
                </StackPanel>
                <TextBox Height="425" TextWrapping="Wrap" x:Name="TextBox1" Background="#f1f1f1"  Width="760" VerticalScrollBarVisibility="Visible" VerticalContentAlignment="Top"  Canvas.Left="20" Canvas.Top="54" VerticalAlignment="Top" AcceptsReturn="True" KeyDown="TextBox1_KeyDown"  HorizontalContentAlignment="Left" ></TextBox>
                <DataGrid x:Name="dataGridOne" Height="425" Background="#f1f1f1"  Width="760"  VerticalContentAlignment="Top"  Canvas.Left="20" Canvas.Top="54" VerticalAlignment="Top" AutoGenerateColumns="False" >
                    <DataGrid.Columns>
                        <DataGridTextColumn Header="字段名" Binding="{Binding COLUMN_NAME}" />
                        <DataGridTextColumn Header="默认值" Binding="{Binding COLUMN_DEFAULT}" />
                        <DataGridTextColumn Header="可空" Binding="{Binding IS_NULLABLE}" />
                        <DataGridTextColumn Header="类型" Binding="{Binding DATA_TYPE}" />
                        <DataGridTextColumn Header="文本长度" Binding="{Binding CHARACTER_MAXIMUM_LENGTH}" />
                        <DataGridTextColumn Header="数字精度" Binding="{Binding NUMERIC_PRECISION}" />
                        <DataGridTextColumn Header="小数位数" Binding="{Binding NUMERIC_SCALE}" />
                        <DataGridTextColumn Header="时间精度" Binding="{Binding DATETIME_PRECISION}" />
                        <DataGridTextColumn Header="字符集" Binding="{Binding CHARACTER_SET_NAME}" />
                        <DataGridTextColumn Header="排序规则" Binding="{Binding COLLATION_NAME}" />
                        <DataGridTextColumn Header="字段类型" Binding="{Binding COLUMN_TYPE}" />
                        <DataGridTextColumn Header="键类型" Binding="{Binding COLUMN_KEY}" />
                        <DataGridTextColumn Header="扩展" Binding="{Binding EXTRA}" />
                        <DataGridTextColumn Header="备注" Binding="{Binding COLUMN_COMMENT}" />
                    </DataGrid.Columns>
                </DataGrid>
            </Canvas>
        </Viewbox>
        <!--HorizontalAlignment：水平方向
        VerticalAlignment：垂直方向
        Foreground:字体颜色-->
    </Grid>
</Window>

数据库连接配置，放数据库的信息
DbLib.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WpfApp1
{
    public class DbLib
    {
        public static string mySqlcon = "Server=数据库ip;Port=3306;User=用户名;Password=密码;Charset=utf8";
    }
}