I am writing this question to get some advice on improving this Database Management library. I'll explain a little about it below.
Database Manager - DatabaseManager is the holder, it generates new connections when the NewConnection is called, it returns a new DatabaseConnection with the saved connection string.
Database Connection - DatabaseConnection is a connection containing a new connection that's created on each call from DatabaseManager
Usage:
using (var databaseConnection = Serber.GetDatabase().NewDatabaseConnection)
{
databaseConnection.SetQuery("SELECT * FROM `table` WHERE `enabled` = '1' ORDER BY `name` DESC;");
databaseConnection.Open();
using (MySqlDataReader Reader = databaseConnection.ExecuteReader())
{
while (Reader.Read())
{
try
{
// do some work
}
catch (DatabaseException ex)
{
log.Error("Unable to load item for ID [" + Reader.GetInt32("id") + "]", ex);
}
}
}
}
DatabaseManager:
internal sealed class DatabaseManager
{
private readonly string _connectionString;
public DatabaseManager()
{
var connectionString = new MySqlConnectionStringBuilder
{
ConnectionLifeTime = (60 * 5),
ConnectionTimeout = 30,
Database = Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.database"),
DefaultCommandTimeout = 120,
Logging = false,
MaximumPoolSize = uint.Parse(Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.pool_maxsize")),
MinimumPoolSize = uint.Parse(Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.pool_minsize")),
Password = Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.password"),
Pooling = Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.pooling") == "1",
Port = uint.Parse(Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.port")),
Server = Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.hostname"),
UseCompression = false,
UserID = Hariak.HariakServer.Config.GetConfigValueByKey("database.mysql.username"),
};
_connectionString = connectionString.ToString();
}
public bool ConnectionWorks()
{
try
{
using (var databaseConnection = NewDatabaseConnection)
{
databaseConnection.OpenConnection();
}
return true;
}
catch (Exception)
{
return false;
}
}
public DatabaseConnection NewDatabaseConnection => new DatabaseConnection(_connectionString);
}
DatabaseConnection:
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();
}
public 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
{
if (_parameters != null && _parameters.Count > 0)
{
_command.Parameters.AddRange(_parameters.ToArray());
}
return int.Parse(_command.ExecuteScalar().ToString());
}
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 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()
{
Dispose(true);
}
private void Dispose(bool disposing)
{
if (!disposing)
{
return;
}
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
_connection = null;
}
if (_parameters != null)
{
_parameters.Clear();
_parameters = null;
}
if (_command != null)
{
_command.Dispose();
_command = null;
}
}
}
