C#自定义数据库工具类

背景

最近老是要做后台的底层数据处理操作，重写了无数份类似的代码，也修改了无数重犯的bug，没办法，只能想着搞一个通用的类库来给自己减轻负担

步骤

1.打开vs2019新建一个C#空项目

2.右键单击解决方案框里的引用，进入弹出菜单中的管理NUGET包

3.搜索并安装mysql数据库驱动

4.回到项目，新增一个类，命名为MySqlHandler.cs，内容如下

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace MySqlUtils
{
    /// <summary>
    /// 我的数据库操作类
    /// </summary>
    public class MySqlHandler
    {
        /// <summary>
        /// 数据库地址
        /// </summary>
        private readonly string host = string.Empty;

        /// <summary>
        /// 数据库端口号
        /// </summary>
        private readonly string port = string.Empty;

        /// <summary>
        /// 数据库用户
        /// </summary>
        private readonly string user = string.Empty;

        /// <summary>
        /// 数据库密码
        /// </summary>
        private readonly string password = string.Empty;

        /// <summary>
        /// 数据库名称
        /// </summary>
        private readonly string database = string.Empty;

        /// <summary>
        /// 连接超时
        /// </summary>
        private readonly string connecttimeout = string.Empty;

        /// <summary>
        /// 带参构造
        /// </summary>
        /// <param name="filepath">配置文件</param>
        /// <exception cref="Exception"></exception>
        public MySqlHandler(string filepath)
        {
            try
            {
                string[] rows = File.ReadAllLines(filepath);
                foreach (string line in rows)
                {
                    string[] row = line.Split('=');
                    switch (row[0].ToUpper())
                    {
                        case "HOST":
                        case "地址":
                            host = row[1];
                            break;
                        case "PORT":
                        case "端口":
                            port = row[1];
                            break;
                        case "USER":
                        case "用户名":
                            user = row[1];
                            break;
                        case "PASSWORD":
                        case "密码":
                            password = row[1];
                            break;
                        case "DATABASE":
                        case "数据库":
                            database = row[1];
                            break;
                        case "CONNECTTIMEOUT":
                        case "连接超时":
                            connecttimeout = row[1];
                            break;
                        default:
                            throw new Exception($"意外的配置字段[{row[0]}]！");
                    }
                }

                FieldInfo[] fieldInfos = this.GetType().GetFields();
                foreach (FieldInfo fieldInfo in fieldInfos)
                {
                    if ((string)fieldInfo.GetValue(fieldInfo.Name) == string.Empty)
                    {
                        throw new Exception($"字段[{fieldInfo.Name}]未进行配置！");
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        private MySqlConnection GetMySqlConnection()
        {
            //连接串
            string connectstring = $"host={host};port={port};user={user};password={password};database={database};connect timeout={connecttimeout};Integrated Security=false;";
            //返回数据库连接对象
            return new MySqlConnection(connectstring);
        }

        /// <summary>
        /// 查询操作
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commmandTimeout">查询超时，默认是10秒</param>
        /// <returns>查询结果</returns>
        public List<List<string>> Query(string cmdText, int commandTimeout=10)
        {
            //结果
            List<List<string>> data = new List<List<string>>();

            //数据库连接
            MySqlConnection mySqlConnection = GetMySqlConnection();
            //数据库读取器
            MySqlDataReader mySqlDataReader = null;

            try
            {
                //打开数据库连接
                mySqlConnection.Open();

                //数据库命令对象
                MySqlCommand mySqlCommand = new MySqlCommand(cmdText, mySqlConnection) { CommandTimeout = commandTimeout };

                //执行sql命令
                mySqlDataReader = mySqlCommand.ExecuteReader();

                //读取器获取到数据
                if (mySqlDataReader.HasRows)
                {
                    //一行一行读取
                    while (mySqlDataReader.Read())
                    {
                        //此行内容
                        List<string> row = new List<string>(mySqlDataReader.FieldCount);

                        //依次读取每一个字段
                        for (int i = 0; i < mySqlDataReader.FieldCount; i++)
                        {
                            //初始化字段为空
                            string col = string.Empty;

                            //尝试读取
                            try
                            {
                                col = mySqlDataReader.GetString(i);
                            }
                            catch
                            {
                                //吃掉读取错误
                            }

                            row.Add(col);
                        }

                        //将此行内容加到结果中
                        data.Add(row);
                    }
                }

                //返回结果
                return data;
            }
            catch
            {
                throw;
            }
            finally
            {
                //关闭读取器
                if (mySqlDataReader != null && !mySqlDataReader.IsClosed)
                {
                    mySqlDataReader.Close();
                }

                //关闭数据库连接
                if (mySqlConnection != null && mySqlConnection.State == System.Data.ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// 更新操作
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandTimeout">查询超时，默认是10秒</param>
        /// <returns>受影响的记录行数</returns>
        public int Update(string cmdText, int commandTimeout=10)
        {
            //数据库连接
            MySqlConnection mySqlConnection = GetMySqlConnection();

            try
            {
                //打开数据库连接
                mySqlConnection.Open();

                //数据库命令对象
                MySqlCommand mySqlCommand = new MySqlCommand(cmdText, mySqlConnection) { CommandTimeout = commandTimeout };

                //执行更新操作，并返回受影响的记录行数
                return mySqlCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                //关闭数据库连接
                if (mySqlConnection != null && mySqlConnection.State == System.Data.ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
            }
        }
    }
}

5.建立配置文件，命名为mysql.txt，将其放在程序的bin/Debug/文件夹下，内容如下

6.测试数据库设置

我这边简单测试，建立了一个数据库，名命为student，里边建立一个表，命名为data，其表中分三个字段，分别是_index、_timestamp、_number。

7.建立测试类，名命为Demo.cs，内容如下：

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

namespace MySqlUtils
{
    class Demo
    {
        /// <summary>
        /// 主函数
        /// </summary>
        static void Main()
        {
            string filepath = "mysql.txt";

            QueryDemo(filepath);

            Console.WriteLine("\n===========================\n");

            UpdateDemo(filepath);

            Console.ReadKey();
        }

        /// <summary>
        /// 查询样例
        /// </summary>
        static void QueryDemo(string filepath)
        {
            Console.WriteLine("start QueryDemo");

            //string filepath = @"C:\Users\bingling\Desktop\mysql.txt";

            MySqlHandler mySqlHandler = new MySqlHandler(filepath);

            string cmdText = "select * from data";

            try
            {
                List<List<string>> data = mySqlHandler.Query(cmdText);

                foreach (List<string> row in data)
                {
                    foreach (string col in row)
                    {
                        if (col == string.Empty)
                        {
                            Console.Write("null ");
                        }
                        else
                        {
                            Console.Write(col + " ");
                        }
                    }
                    Console.WriteLine();
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("end QueryDemo");
        }

        /// <summary>
        /// 更新样例
        /// </summary>
        static void UpdateDemo(string filepath)
        {
            Console.WriteLine("start UpdateDemo");

            //string filepath = @"C:\Users\bingling\Desktop\mysql.txt";

            MySqlHandler mySqlHandler = new MySqlHandler(filepath);

            string cmdText = "insert into data(_index,_time,_number) values('2','2021-07-15 00:00:00','2')";

            try
            {
                int row = mySqlHandler.Update(cmdText);

                Console.WriteLine("受影响记录行数:" + row);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("end UpdateDemo");
        }
    }
}

8.运行结果