2
\$\begingroup\$

Below is a database manager and a class called database connection. The database Manager controls the database connection string and generates a new database connection when you need one.

I'm looking for possibly a way to make this faster and overall better performance wise. I'll also be welcome to small improvements that just better the classes. I understand the speed can't really be improved that much but I'm just meaning the speed the code runs at. I don't really know much about C#'s MySQL library.

Usage:

using (var dbConnection = new DatabaseConnection(_connectionString))
{
    dbConnection.SetQuery("SELECT * FROM `table` WHERE `column = @columnValue");
    dbConnection.AddParameter("columnValue", "some value");
    var row = dbConnection.GetRow();`
}

DatabaseManager

using System;
using MySql.Data.MySqlClient;
using Panda.Root.Core.Configuration;

namespace Panda.Root.Core.Database
{
    internal class DatabaseManager
    {
        private readonly string _connectionString;

        public DatabaseManager()
        {
            var config = Program.GetPandaServer().GetHandler("configuration") as ConfigurationManager;

            if (config == null)
            {
                return;
            }

            var connectionString = new MySqlConnectionStringBuilder
            {
                ConnectionLifeTime = 60 * 5,
                ConnectionTimeout = 30,
                Database = config.GetValueByKey("database.mysql.database"),
                DefaultCommandTimeout = 120,
                Logging = false,
                MaximumPoolSize = uint.Parse(config.GetValueByKey("database.mysql.pool_maxsize")),
                MinimumPoolSize = uint.Parse(config.GetValueByKey("database.mysql.pool_minsize")),
                Password = config.GetValueByKey("database.mysql.password"),
                Pooling = config.GetValueByKey("database.mysql.pooling") == "1",
                Port = uint.Parse(config.GetValueByKey("database.mysql.port")),
                Server = config.GetValueByKey("database.mysql.hostname"),
                UseCompression = false,
                UserID = config.GetValueByKey("database.mysql.username"),
            };

            _connectionString = connectionString.ToString();
        }

        public bool ConnectionWorks()
        {
            try
            {
                using (var dbConnection = new DatabaseConnection(_connectionString))
                {
                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public DatabaseConnection GetConnection() => new DatabaseConnection(_connectionString);
    }
}

Connection:

using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using NLog;

namespace Panda.Root.Core.Database
{
    internal sealed class DatabaseConnection : IDisposable
    {
        private static readonly ILogger Logger = LogManager.GetCurrentClassLogger();

        private MySqlConnection _connection;
        private List<MySqlParameter> _parameters;
        private MySqlCommand _command;

        public DatabaseConnection(string connectionString)
        {
            _connection = new MySqlConnection(connectionString);
            _command = _connection.CreateCommand();

            OpenConnection();
        }

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

            _connection.Open();
        }

        public void AppendParameter(string key, object value)
        {
            if (_parameters == null)
            {
                _parameters = new List<MySqlParameter>();
            }

            _parameters.Add(new MySqlParameter(key, value));
        }

        public void SetQuery(string query)
        {
            _command.CommandText = query;
        }

        public int ExecuteNonQuery()
        {
            if (_parameters != null && _parameters.Count > 0)
            {
                _command.Parameters.AddRange(_parameters.ToArray());
            }

            try
            {
                return _command.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return 0;
            }
            finally
            {
                _command.CommandText = string.Empty;
                _command.Parameters.Clear();

                if (_parameters != null && _parameters.Count > 0)
                {
                    _parameters.Clear();
                }
            }
        }

        public int GetLastId()
        {
            try
            {
                return (int)_command.LastInsertedId;
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return 0;
            }
            finally
            {
                _command.CommandText = string.Empty;
            }
        }

        public int ExecuteSingleInt()
        {
            try
            {
                return int.Parse(ExecuteSingleString());
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return 0;
            }
        }

        public string ExecuteSingleString()
        {
            try
            {
                if (_parameters != null && _parameters.Count > 0)
                {
                    _command.Parameters.AddRange(_parameters.ToArray());
                }

                return (_command.ExecuteScalar() ?? "").ToString();
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return "";
            }
            finally
            {
                _command.CommandText = string.Empty;
                _command.Parameters.Clear();

                if (_parameters != null && _parameters.Count > 0)
                {
                    _parameters.Clear();
                }
            }
        }

        public bool TryExecuteSingleInt(out int value)
        {
            try
            {
                if (_parameters != null && _parameters.Count > 0)
                {
                    _command.Parameters.AddRange(_parameters.ToArray());
                }

                var scalar = _command.ExecuteScalar();

                if (scalar == null)
                {
                    value = 0;
                    return false;
                }

                value = int.Parse(scalar.ToString());
                return true;
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                value = 0;
                return false;
            }
            finally
            {
                _command.CommandText = string.Empty;
                _command.Parameters.Clear();

                if (_parameters != null && _parameters.Count > 0)
                {
                    _parameters.Clear();
                }
            }
        }

        public MySqlDataReader ExecuteReader()
        {
            if (_parameters != null && _parameters.Count > 0)
            {
                _command.Parameters.AddRange(_parameters.ToArray());
            }

            try
            {
                return _command.ExecuteReader();
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return null;
            }
            finally
            {
                _command.CommandText = string.Empty;
                _command.Parameters.Clear();

                if (_parameters != null && _parameters.Count > 0)
                {
                    _parameters.Clear();
                }
            }
        }

        public DataSet ExecuteDataSet()
        {
            if (_parameters != null && _parameters.Count > 0)
            {
                _command.Parameters.AddRange(_parameters.ToArray());
            }

            var dataSet = new DataSet();

            try
            {
                using (var adapter = new MySqlDataAdapter(_command))
                {
                    adapter.Fill(dataSet);
                }

                return dataSet;
            }
            catch (MySqlException e)
            {
                Logger.Error(e, "Database error was logged.");
                return null;
            }
            finally
            {
                _command.CommandText = string.Empty;
                _command.Parameters.Clear();

                if (_parameters != null && _parameters.Count > 0)
                {
                    _parameters.Clear();
                }
            }
        }

        public DataTable ExecuteTable()
        {
            var dataSet = ExecuteDataSet();
            return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
        }

        public DataRow ExecuteRow()
        {
            var dataTable = ExecuteTable();
            return dataTable.Rows.Count > 0 ? dataTable.Rows[0] : null;
        }

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

            if (_parameters != null)
            {
                _parameters.Clear();
                _parameters = null;
            }

            if (_command == null)
            {
                return;
            }

            _command.Dispose();
            _command = null;
        }
    }
}
\$\endgroup\$
  • \$\begingroup\$ the speed the code runs at - is the code slow? How did you measure it? \$\endgroup\$ – t3chb0t Jun 12 '17 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.