Return to Answer

mysqlcommands in using blocks as suggested by Dan Lyons

Source Link

edited Apr 20, 2013 at 6:47

2.4k
1
13
17

public class MySQLGear : IDisposable
{
    private readonly MySqlConnection _connection;
    
    public MySQLGear(string connectionString)
    {
        _connection = new MySqlConnection(connectionString);
    }

    protected MySqlConnection Connection()
    {
        if (_connection == null)
        {
            throw new ObjectDisposedException("The underlying connection has been closed");
        }

        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
        
        return _connection;
    }

    public MySqlDataReader GetReader(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection());)
        {
            return cmdMysql.ExecuteReader();
        }
    }

    public int MakeQuery(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection());)
        {
            return cmdMysql.ExecuteNonQuery();
        }
    }

    public object MakeScalar(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection());)
        {
            return cmdMysql.ExecuteScalar();
        }
    }
    
    ~MySQLGear()
    {
        Dispose(false);
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (_connection != null)
        {
            if (_connection.State != ConnectionState.Closed) 
            {
                _connection.Dispose();
            }
            _connection = null;
        }
    }
}

public class MySQLGear : IDisposable
{
    private readonly MySqlConnection _connection;
    
    public MySQLGear(string connectionString)
    {
        _connection = new MySqlConnection(connectionString);
    }

    protected MySqlConnection Connection()
    {
        if (_connection == null)
        {
            throw new ObjectDisposedException("The underlying connection has been closed");
        }

        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
        
        return _connection;
    }

    public MySqlDataReader GetReader(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteReader();
    }

    public int MakeQuery(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteNonQuery();
    }

    public object MakeScalar(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteScalar();
    }
    
    ~MySQLGear()
    {
        Dispose(false);
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (_connection != null)
        {
            if (_connection.State != ConnectionState.Closed) 
            {
                _connection.Dispose();
            }
            _connection = null;
        }
    }
}

public class MySQLGear : IDisposable
{
    private readonly MySqlConnection _connection;
    
    public MySQLGear(string connectionString)
    {
        _connection = new MySqlConnection(connectionString);
    }

    protected MySqlConnection Connection()
    {
        if (_connection == null)
        {
            throw new ObjectDisposedException("The underlying connection has been closed");
        }

        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
        
        return _connection;
    }

    public MySqlDataReader GetReader(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection()))
        {
            return cmdMysql.ExecuteReader();
        }
    }

    public int MakeQuery(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection()))
        {
            return cmdMysql.ExecuteNonQuery();
        }
    }

    public object MakeScalar(string queryMysql)
    {
        using (var cmdMysql = new MySqlCommand(queryMysql, Connection()))
        {
            return cmdMysql.ExecuteScalar();
        }
    }
    
    ~MySQLGear()
    {
        Dispose(false);
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (_connection != null)
        {
            if (_connection.State != ConnectionState.Closed) 
            {
                _connection.Dispose();
            }
            _connection = null;
        }
    }
}

Source Link

created Apr 18, 2013 at 17:01

Peter Kiss

2.4k
1
13
17

Question #1

No. You do not need to ref the connection object in your methods' parameters - think object oriented way not class oriented.

Question #2

The only way is to make this as a non static class. Avoid always statics when the current problem contains some kind of state management like a MySQL connection.

Question #3

No you don't but you should implement the IDisposable interface and apply the correct disposable pattern.

public class MySQLGear : IDisposable
{
    private readonly MySqlConnection _connection;
    
    public MySQLGear(string connectionString)
    {
        _connection = new MySqlConnection(connectionString);
    }

    protected MySqlConnection Connection()
    {
        if (_connection == null)
        {
            throw new ObjectDisposedException("The underlying connection has been closed");
        }

        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
        
        return _connection;
    }

    public MySqlDataReader GetReader(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteReader();
    }

    public int MakeQuery(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteNonQuery();
    }

    public object MakeScalar(string queryMysql)
    {
        var cmdMysql = new MySqlCommand(queryMysql, Connection());
        return cmdMysql.ExecuteScalar();
    }
    
    ~MySQLGear()
    {
        Dispose(false);
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (_connection != null)
        {
            if (_connection.State != ConnectionState.Closed) 
            {
                _connection.Dispose();
            }
            _connection = null;
        }
    }
}