Return to Answer

added 3 characters in body

Source Link

edited Apr 19, 2013 at 22:46

It's a bad practice to keep an open connection. I'd advice you to store a connection string and open connection each time within a using block.

public class OleDbGear
{
    private readonly string _connectionString;

    /// <summary>
    ///     Default constructor.
    /// </summary>
    /// <param name="connSrting">Full connSrting to aname="connString">Connection databasestring.</param>
    public OleDbGear(string connSrting)
    {
        _connectionString = connSrting;connString;
    }

    /// <summary>
    ///     Returns open connection.
    /// </summary>
    private OleDbConnection GetOpenConnection()
    {
        var dbConnection = new OleDbConnection(_connectionString);
        dbConnection.Open();
        return dbConnection;
    }

    private T Execute <T>(string query, Action<OleDbCommand> paramsSetup, Func<OleDbCommand, T> func)
    {
        using (OleDbConnection connection = GetOpenConnection())
        using (var cmd = new OleDbCommand(query, connection))
        {
            if (paramsSetup != null)
                paramsSetup(cmd);
            return func(cmd);
        }
    }

    public int ExecuteNonQuery(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteNonQuery());
    }

    public object ExecuteSclar(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteScalar());
    }

    public T ExecuteReader <T>(string query, Func<OleDbDataReader, T> converter, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd =>
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader == null)
                        throw new Exception("DB error");
                    return converter(reader);
                }
            });
    }
}

Wrapping all in using blocks ensures correct disposing of all resources. Action<OleDbCommand> paramsSetup helps you to provide parameters of your query (no injection). Func<OleDbDataReader, T> converter just converts reader object into usable result.

It's a bad practice to keep open connection. I'd advice you to store connection string and open connection each time within using block.

public class OleDbGear
{
    private readonly string _connectionString;

    /// <summary>
    ///     Default constructor.
    /// </summary>
    /// <param name="connSrting">Full connSrting to a database.</param>
    public OleDbGear(string connSrting)
    {
        _connectionString = connSrting;
    }

    /// <summary>
    ///     Returns open connection.
    /// </summary>
    private OleDbConnection GetOpenConnection()
    {
        var dbConnection = new OleDbConnection(_connectionString);
        dbConnection.Open();
        return dbConnection;
    }

    private T Execute <T>(string query, Action<OleDbCommand> paramsSetup, Func<OleDbCommand, T> func)
    {
        using (OleDbConnection connection = GetOpenConnection())
        using (var cmd = new OleDbCommand(query, connection))
        {
            if (paramsSetup != null)
                paramsSetup(cmd);
            return func(cmd);
        }
    }

    public int ExecuteNonQuery(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteNonQuery());
    }

    public object ExecuteSclar(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteScalar());
    }

    public T ExecuteReader <T>(string query, Func<OleDbDataReader, T> converter, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd =>
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader == null)
                        throw new Exception("DB error");
                    return converter(reader);
                }
            });
    }
}

Wrapping all in using blocks ensures disposing of all resources. Action<OleDbCommand> paramsSetup helps you to provide parameters of your query (no injection). Func<OleDbDataReader, T> converter just converts reader object into usable result.

It's a bad practice to keep an open connection. I'd advice you to store a connection string and open connection each time within a using block.

public class OleDbGear
{
    private readonly string _connectionString;

    /// <summary>
    ///     Default constructor.
    /// </summary>
    /// <param name="connString">Connection string.</param>
    public OleDbGear(string connSrting)
    {
        _connectionString = connString;
    }

    /// <summary>
    ///     Returns open connection.
    /// </summary>
    private OleDbConnection GetOpenConnection()
    {
        var dbConnection = new OleDbConnection(_connectionString);
        dbConnection.Open();
        return dbConnection;
    }

    private T Execute <T>(string query, Action<OleDbCommand> paramsSetup, Func<OleDbCommand, T> func)
    {
        using (OleDbConnection connection = GetOpenConnection())
        using (var cmd = new OleDbCommand(query, connection))
        {
            if (paramsSetup != null)
                paramsSetup(cmd);
            return func(cmd);
        }
    }

    public int ExecuteNonQuery(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteNonQuery());
    }

    public object ExecuteSclar(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteScalar());
    }

    public T ExecuteReader <T>(string query, Func<OleDbDataReader, T> converter, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd =>
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader == null)
                        throw new Exception("DB error");
                    return converter(reader);
                }
            });
    }
}

Source Link

created Apr 19, 2013 at 21:41

Kostia

It's a bad practice to keep open connection. I'd advice you to store connection string and open connection each time within using block.

Something like this (just change to mysql funcs):

public class OleDbGear
{
    private readonly string _connectionString;

    /// <summary>
    ///     Default constructor.
    /// </summary>
    /// <param name="connSrting">Full connSrting to a database.</param>
    public OleDbGear(string connSrting)
    {
        _connectionString = connSrting;
    }

    /// <summary>
    ///     Returns open connection.
    /// </summary>
    private OleDbConnection GetOpenConnection()
    {
        var dbConnection = new OleDbConnection(_connectionString);
        dbConnection.Open();
        return dbConnection;
    }

    private T Execute <T>(string query, Action<OleDbCommand> paramsSetup, Func<OleDbCommand, T> func)
    {
        using (OleDbConnection connection = GetOpenConnection())
        using (var cmd = new OleDbCommand(query, connection))
        {
            if (paramsSetup != null)
                paramsSetup(cmd);
            return func(cmd);
        }
    }

    public int ExecuteNonQuery(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteNonQuery());
    }

    public object ExecuteSclar(string query, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd => cmd.ExecuteScalar());
    }

    public T ExecuteReader <T>(string query, Func<OleDbDataReader, T> converter, Action<OleDbCommand> paramsSetup = null)
    {
        return Execute(query, paramsSetup, cmd =>
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader == null)
                        throw new Exception("DB error");
                    return converter(reader);
                }
            });
    }
}

You also could consider creating a sup method:

    public IEnumerable<T> ExecuteAndReadToEnd <T>(string query, Func<OleDbDataReader, T> lineReader, Action<OleDbCommand> paramsSetup = null)
    {
        return ExecuteReader(query, reader =>
            {
                var results = new List<T>();
                while (reader.Read())
                    results.Add(lineReader(reader));
                return results;
            }, 
            paramsSetup);
    }

as in most cases you are going to read all lines.

Usage example (suppose Instance is your reference to a db):

...
    public IEnumerable<string> GetAllPurchases(int customerId)
    {
        var query = "SELECT Purchase FROM Purchases WHERE CustomerId=@id";
        return Instance.ExecuteAndReadToEnd(query, reader => reader.GetString(0),
                                            cmd => cmd.Parameters.AddWithValue("@id", customerId));
    }

Good luck!