2
\$\begingroup\$

I'm a teen developer who just does this as a hobby, but I still care about good practice even if I don't fully understand it. Two of the most used phrases when sharing the code of my C# MySQL library is bundling and abstraction,

First of all... do I need to reduce said phrases? If so, how can I do that? Among any other general recommendations on areas, I can improve.

DatabaseProvider: This simply just holds state for database settings (host, name, port, etc) and returns a new instance of DatabaseConnection whenever the application needs one.

public class DatabaseProvider : IDatabaseProvider
{
    private readonly string _connectionString;

    public DatabaseProvider(string databaseName, string password, uint port, string host, string username)
    {
        _connectionString = new MySqlConnectionStringBuilder
        {
            Database = databaseName,
            Password = password,
            Port = port,
            Server = host,
            UserID = username,
            SslMode = MySqlSslMode.None,
            DefaultCommandTimeout = 30,
            CharacterSet = "utf8mb4",
            AllowUserVariables = true,
            MinimumPoolSize = 1,
        }.ToString();
    }

    public DatabaseConnection GetConnection()
    {
        var connection = new MySqlConnection(_connectionString);
        var command = connection.CreateCommand();
        
        return new DatabaseConnection(connection, command);
    }
}

DatabaseConnection: Also pretty simple, although very different from the provider. This is the actual abstracted instance of MySqlConnection with utility methods to perform frequent operations like fetching rows or collections of rows or just running general queries. It also supports parameters although they aren't type-safe.

public class DatabaseConnection : IDisposable
{
    private readonly MySqlConnection _connection;
    private readonly MySqlCommand _command;

    public DatabaseConnection(MySqlConnection connection, MySqlCommand command)
    {
        _connection = connection;
        _command = command;

        _connection.Open();
    }

    public void SetQuery(string commandText)
    {
        _command.Parameters.Clear();
        _command.CommandText = commandText;
    }

    public void ExecuteQuery()
    {
        _command.ExecuteNonQuery();
    }

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

        using var adapter = new MySqlDataAdapter(_command);
        adapter.Fill(dataTable);

        return dataTable;
    }

    public DataRow ExecuteRow()
    {
        DataRow dataRow = null;

        var dataSet = new DataSet();

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

        if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count == 1)
        {
            dataRow = dataSet.Tables[0].Rows[0];
        }

        return dataRow;
    }

    public void AddParameter(string name, object value)
    {
        _command.Parameters.AddWithValue(name, value);
    }

    public void Dispose()
    {
        if (_connection.State != ConnectionState.Closed)
        {
            _connection.Close();
        }

        _connection.Dispose();
        _command.Dispose();
    }
}
\$\endgroup\$
  • \$\begingroup\$ Are the complaints about too much abstraction or not enough? Are you aware of the .net Entity Framework for databases that works with SQL server or MySQL? \$\endgroup\$ – pacmaninbw Sep 25 at 16:15
  • 1
    \$\begingroup\$ Well the complaints are usually to do with too much boilerplate so I would say not enough. I've heard of various frameworks and ORMs although they all seem overkill for what I want to do. \$\endgroup\$ – AAA Sep 25 at 16:21
  • \$\begingroup\$ Could you please provide the using statements for the 2 classes you show here such as using System.Data; and using MySql.Data.MySqlClient;. This will help us review the code. \$\endgroup\$ – pacmaninbw Sep 25 at 16:56

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.