MySQL数据库库

Question

因此，我创建了一个数据库管理器，并想知道我是否可以以任何方式改进它？谢谢。

DatabaseManager.cs：

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

namespace Sahara.Core.Database
{
    class DatabaseManager
    {
        private readonly DatabaseSettings databaseSettings;
        private readonly string connectionString;

        public DatabaseManager(DatabaseSettings databaseSettings)
        {
            this.databaseSettings = databaseSettings;

            MySqlConnectionStringBuilder mysqlConnectionString = new MySqlConnectionStringBuilder
            {
                ConnectionLifeTime = (60 * 5),
                ConnectionTimeout = 30,
                Database = databaseSettings.DatabaseName,
                DefaultCommandTimeout = 120,
                Logging = false,
                MaximumPoolSize = databaseSettings.MaximumConnections,
                MinimumPoolSize = 3,
                Password = databaseSettings.DatabasePassword,
                Pooling = true,
                Port = 3306,
                Server = databaseSettings.DatabaseHost,
                UseCompression = false,
                UserID = databaseSettings.DatabaseUsername
            };

            this.connectionString = mysqlConnectionString.ToString();
        }

        public bool ValidConnection()
        {
            try
            {
                using (var databaseConnection = GetDatabaseConnection())
                {
                    databaseConnection.OpenConnection();
                    databaseConnection.SetQuery("SELECT 1+1;");
                    databaseConnection.RunQuery();
                }
            }
            catch (MySqlException)
            {
                return false;
            }

            return true;
        }

        public DatabaseConnection GetDatabaseConnection()
        {
            return new DatabaseConnection(connectionString);
        }
    }
}

DatabaseConnection.cs：

using MySql.Data.MySqlClient;
using Sahara.Core.Logging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sahara.Core.Database
{
    class DatabaseConnection : IDisposable
    {
        private MySqlConnection mysqlConnection;
        private MySqlCommand mysqlCommand;
        private MySqlTransaction mysqlTransaction;
        private List<MySqlParameter> mysqlParameters;
        private DateTime startTime;

        public DatabaseConnection(string ConnectionStr)
        {
            this.mysqlConnection = new MySqlConnection(ConnectionStr);
            this.mysqlCommand = this.mysqlConnection.CreateCommand();
        }

        public void OpenConnection()
        {
            if (this.mysqlConnection.State == ConnectionState.Open)
            {
                throw new InvalidOperationException("Connection already open.");
            }

            this.mysqlConnection.Open();
            this.startTime = DateTime.Now;
        }

        public bool IsConnectionOpen()
        {
            return this.mysqlConnection.State == ConnectionState.Open;
        }

        public void AddParameter(string param, object value)
        {
            if (this.mysqlParameters == null) { this.mysqlParameters = new List<MySqlParameter>(); }
                this.mysqlParameters.Add(new MySqlParameter(param, value));
        }

        public void SetQuery(string Query)
        {
            this.mysqlCommand.Parameters.Clear();
            this.mysqlCommand.CommandText = Query;
        }

        public void RunQuery(string query)
        {
            SetQuery(query);
            RunQuery();
        }

        public void RunQuery()
        {
            try
            {
                mysqlCommand.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                Sahara.GetServer().GetLogManager().Log("MySQL Error: " + exception.Message, LogType.Error);
                throw exception;
            }
        }

        public DataTable getTable()
        {
            var dataTable = new DataTable();

            try
            {
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(mysqlCommand))
                {
                    adapter.Fill(dataTable);
                }
            }
            catch (Exception exception)
            {
                Sahara.GetServer().GetLogManager().Log("MySQL Error: " + exception.Message, LogType.Error);
            }

            return dataTable;
        }

        public int getInteger()
        {
            int result = 0;
            try
            {
                object obj2 = mysqlCommand.ExecuteScalar();
                if (obj2 != null)
                {
                    int.TryParse(obj2.ToString(), out result);
                }
            }
            catch (Exception exception)
            {
                Sahara.GetServer().GetLogManager().Log("MySQL Error: " + exception.Message, LogType.Error);
            }

            return result;
        }

        public string getString()
        {
            string str = string.Empty;

            try
            {
                object obj2 = mysqlCommand.ExecuteScalar();
                if (obj2 != null)
                {
                    str = obj2.ToString();
                }
            }
            catch (Exception exception)
            {
                Sahara.GetServer().GetLogManager().Log("MySQL Error: " + exception.Message, LogType.Error);
            }

            return str;
        }

        public DataRow getRow()
        {
            DataRow row = null;
            try
            {
                DataSet dataSet = new DataSet();
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(mysqlCommand))
                {
                    adapter.Fill(dataSet);
                }
                if ((dataSet.Tables.Count > 0) && (dataSet.Tables[0].Rows.Count == 1))
                {
                    row = dataSet.Tables[0].Rows[0];
                }
            }
            catch (Exception exception)
            {
                Sahara.GetServer().GetLogManager().Log("MySQL Error: " + exception.Message, LogType.Error);
            }

            return row;
        }

        public void Dispose()
        {
            if (this.mysqlConnection.State == ConnectionState.Open)
            {
                this.mysqlConnection.Close();
                this.mysqlConnection = null;
            }

            if (this.mysqlParameters != null)
            {
                this.mysqlParameters.Clear();
                this.mysqlParameters = null;
            }

            if (this.mysqlTransaction != null)
            {
                this.mysqlTransaction.Dispose();
                this.mysqlTransaction = null;
            }

            if (this.mysqlCommand != null)
            {
                this.mysqlCommand.Dispose();
                this.mysqlCommand = null;
            }

            int Finish = (DateTime.Now - startTime).Milliseconds;
            Sahara.GetServer().GetLogManager().Log("Query completed in " + Finish + "ms", LogType.Information);
        }
    }
}

DatabaseSettings.cs：

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

namespace Sahara.Core.Database
{
    class DatabaseSettings
    {
        public string DatabaseHost
        {
            get;
            set;
        }

        public string DatabaseUsername
        {
            get;
            set;
        }

        public string DatabasePassword
        {
            get;
            set;
        }

        public string DatabaseName
        {
            get;
            set;
        }

        public uint DatabasePort
        {
            get;
            set;
        }

        public uint MaximumConnections
        {
            get;
            set;
        }
    }
}

Answer 1

DatabaseManager

• 将databaseSettings存储为实例变量不是个好主意，因为对象可能是从外部修改的。如果是这样的话，创建的连接字符串和databaseSettings的属性是不同的。

DatabaseConnection

• 在IsConnectionOpen中使用OpenConnection方法(而不是复制比较)
• 通过直接返回结果可以简化Get*方法:公共字符串getString() { try { object obj2 = mysqlCommand.ExecuteScalar()；返回obj2 != null？{ Sahara.GetServer().GetLogManager().Log("MySQL错误：“+ exception.Message，LogType.Error)；}
• 我认为处理连接是有意义的，即使状态不是开放的。检查变量是否为null，以允许多次调用Dispose()。
• get*方法应该重命名为Get*
• 应将Finish重命名为finish

API

API有点混乱，因为添加查询可以清除前面添加的参数列表。这是一些非直觉的行为，你必须知道;)

如果您希望围绕常规DB创建一些内容，我更希望创建一个包含SQL和所有参数的查询对象，而不是在一个操作中执行该查询对象，这样连接对象就不会具有“查询状态”。

抽象

可以抽象所有这些内容，以便DatabaseManager和DatabaseConection可以与任何数据库管理系统(而不仅仅是MySQL)一起工作。

如果您想支持所有DBMS，只需使用抽象类型(DbConnection代替MySqlConnection，DbCommand代替MySqlCommand，DbParameter代替MySqlParameter，等等)。