C# 通过 mysql.data.dll 链接 MySQL 数据库

1. 下载 mysql.data.dll

官网去下载:
https://dev.mysql.com/downloads/windows/visualstudio/

2. 引用 mysql.data.dll

3. 创建链接类

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinFormsBase.Connect
{
    public class MysqlConnect
    {
        private static MySqlConnection _conn = null;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dr"></param>
        public MysqlConnect(string sql, out MySqlDataReader dr, string database = "test")
        {
            Init(database);
            Open();
            dr = ExecSql(sql);
        }
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dictionary"></param>
        /// <param name="database"></param>
        public MysqlConnect(string sql, out Dictionary<string,List<string>> dictionary, string database)
        {
            Init(database);
            Open();
            dictionary = GetData(ExecSql(sql));
        }

        public MysqlConnect(string database = "test")
        {
            Init(database);
            Open();
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="database"></param>
        /// <returns></returns>
        public static void Init(string database)
        {
            string connection = $"server=localhost;user id=root;password=root;database={database};pooling=true;";
            _conn = new MySqlConnection(connection);
        }

        /// <summary>
        /// 开启链接
        /// </summary>
        public static void Open()
        {
            _conn.Open();
        }

        /// <summary>
        /// 关闭链接
        /// </summary>
        public static void Close()
        {
            _conn.Close();
        }

        /// <summary>
        /// 执行 Mysql 语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecSql(string sql)
        {
            MySqlCommand comm = new MySqlCommand(sql, _conn);
            return comm.ExecuteReader();
        }
        
        /// <summary>
        /// 查看数据 
        /// </summary>
        /// <param name="dr"></param>
        public static Dictionary<string, List<string>> GetData(MySqlDataReader dr)
        {
            Dictionary<String, List<String>> dictionary = new Dictionary<string, List<string>>();
            
            int i = 0;
            while (true)
            {
                try
                {
                    dictionary[dr.GetName(i)] = new List<string>();
                    i++;
                }
                catch
                {
                    break;
                }
            }

            while (dr.Read())
            {
                for (var j = 0; j < dictionary.Count; j++)
                {
                    dictionary[dr.GetName(j)].Add(dr.GetValue(j).ToString());
                }
            }
            
            return dictionary;
        }
        
    }
}

4. 执行语句查看

定义一个接口

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

namespace WinFormsBase.Service
{
    public interface IGetMysqlInfo
    {
        /// <summary>
        /// 得到数据库
        /// </summary>
        /// <returns></returns>
        List<Database> GetDatabse();

        /// <summary>
        /// 得到数据库中的表
        /// </summary>
        /// <returns></returns>
        List<Tables> GetTables(string database);

        /// <summary>
        /// 获取指定数据库中某张表的数据
        /// </summary>
        /// <returns></returns>
        Dictionary<string,List<string>> GetTableData(string database, string table);

    }
}

实现该接口

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinFormsBase.Connect;
using WinFormsBase.Model;

namespace WinFormsBase.Service.Impl
{
    public class GetMysqlInfo : IGetMysqlInfo
    {
        public List<Database> GetDatabse()
        {
            List<Database> databases = new List<Database>();
            MySqlDataReader dr = null;
            // 初始化
            new MysqlConnect("show databases;", out dr);
            // 添加数据库名称
            while (dr.Read())
            {
                databases.Add(new Database
                {
                    Name = (String) dr.GetValue(0)
                });
            }

            MysqlConnect.Close();
            return databases;
        }

        public List<Tables> GetTables(string database)
        {
            List<Tables> tables = new List<Tables>();
            MySqlDataReader dr = null;
            // 初始化
            new MysqlConnect("show tables;", out dr, database);
            // 添加表名称
            while (dr.Read())
            {
                tables.Add(new Tables
                {
                    Name = (String) dr.GetValue(0)
                });
            }

            MysqlConnect.Close();
            return tables;
        }

        public Dictionary<string, List<string>> GetTableData(string database, string table)
        {
            String sql = $"select * from {table}";
            new MysqlConnect(sql, out Dictionary<string, List<string>> dictionary, database);
            return dictionary;
        }
    }
}

winform 代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using WinFormsBase.Model;
using WinFormsBase.Service;
using WinFormsBase.Service.Impl;
using WinFormsBase.Utils;

namespace WinFormsBase.helper
{
    public partial class DataShow : Form
    {
        private readonly IGetMysqlInfo _getMysqlInfo;
        private Dictionary<string, List<string>> _tableData = new Dictionary<string, List<string>>();

        public DataShow()
        {
            InitializeComponent();
            // 初始化接口
            _getMysqlInfo = new GetMysqlInfo();
            // 添加照片
            AddImages();
            // 初始化数据库和数据表
            InitDatabases();
        }


        /// <summary>
        /// 初始化数据库和数据表
        /// </summary>
        private void InitDatabases()
        {
            // 获取数据库名称
            List<Database> databases = _getMysqlInfo.GetDatabse();
            this.DataTree.Nodes.Clear();
            foreach (Database database in databases)
            {
                // 添加数据库结点显示
                TreeNode databaseNode = new TreeNode
                {
                    Text = database.Name,
                    Tag = database.Name,
                    ImageIndex = 0,
                    SelectedImageIndex = 0
                };
                this.DataTree.Nodes.Add(databaseNode);
                // 添加数据库子节点
                List<Tables> tables = _getMysqlInfo.GetTables(database.Name);
                // 添加数据库中表的结点
                foreach (Tables table in tables)
                {
                    TreeNode tableNode = new TreeNode
                    {
                        Text = table.Name,
                        Tag = table.Name,
                        ImageIndex = 1,
                        SelectedImageIndex = 1
                    };
                    // 添加数据库子节点
                    databaseNode.Nodes.Add(tableNode);
                }
            }
        }

        /// <summary>
        /// 添加照片
        /// </summary>
        private void AddImages()
        {
            // 添加照片
            this.DataImage.Images.Add(Properties.Resources.database);
            this.DataImage.Images.Add(Properties.Resources.table);
        }

        /// <summary>
        /// 显示数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DataTree_AfterSelect(object sender, TreeViewEventArgs e)
        {
            // 清空容器数据
            _tableData = new Dictionary<string, List<string>>();
            // 判断不是数据库是表
            if (e.Node.Parent != null)
            {
                // 获取数据
                _tableData = _getMysqlInfo.GetTableData(e.Node.Parent.Text, e.Node.Text);
                // 展示内容
                ShowFiles();
            }
        }

        /// <summary>
        /// 展示数据
        /// </summary>
        private void ShowFiles()
        {
            // 清空
            this.dataShowList.Clear();
            // 向页面展示
            this.dataShowList.View = View.Details;
            // 整行选中
            this.dataShowList.FullRowSelect = true;
            // 获取数据库中的列
            var tableDataKeys = _tableData.Keys;
            foreach (var keys in tableDataKeys)
            {
                // 添加数据库的列
                this.dataShowList.Columns.Add(keys, 100, HorizontalAlignment.Left);
            }
            // 更新文件和文件夹列表
            UpdateListItems();
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        private void UpdateListItems()
        {
            this.dataShowList.BeginUpdate(); //数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
            // 获取数据库中的数据
            var tableDataValues = _tableData.Values;
            // 转化为 List
            List<List<String>> list = tableDataValues.ToList();
            // 对数据进行转置
            var transfer = BaseUtil.Transfer(list);
            // 获取行数
            int rows = transfer.GetLength(0);
            // 获取列数
            int cols = transfer.GetLength(1);
            // 添加显示数据
            for (var i = 0; i < rows; i++)
            {
                ListViewItem listViewItem = new ListViewItem(transfer[i, 0], 0);
                for (var j = 1; j < cols; j++)
                {
                    listViewItem.SubItems.Add(transfer[i, j]);
                }
                this.dataShowList.Items.Add(listViewItem);
            }
            this.dataShowList.EndUpdate(); //结束数据处理，UI界面一次性绘制。
        }

    }
}

        /// <summary>
        /// 得到转置后的二维数组
        /// </summary>
        /// <param name="inputData"></param>
        /// <returns></returns>
        public static string[,] Transfer(List<List<String>> inputData)
        {
            int rows = inputData.Count;
            int cols = 0;
            if (rows > 0)
            {
                cols = inputData[0].Count;
            }

            String[,] outputData = new String[cols, rows];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < cols; j++)
                {
                    outputData[j, i] = inputData[i][j];
                }
            }

            return outputData;
        }

效果

5. 代码下载

https://download.csdn.net/download/YKenan/19265137

链接：https://pan.baidu.com/s/1lQaZRdUQ3djSmw-YIFAqTg 提取码：dwdc
复制这段内容后打开百度网盘手机App，操作更方便哦