/UtilsExtensionsAndHelpers.MySql/MySqlQuery.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace UtilsExtensionsAndHelpers.Utils.Implimentations
{
    public class MySqlQuery: IQuery, IRow
    {
        #region Fields
        private MySqlConnection _connection;
        private MySqlCommand _command;
        private MySqlDataReader _reader;
        private bool _connectionClosed;
        private int _timeOut;
        private bool _usePersistentConnection;
        private readonly Dictionary<string, int> _columns = new Dictionary<string, int>();

        private static Dictionary<DbType,MySqlDbType> _dbTypeMap = new Dictionary<DbType, MySqlDbType>
        {
            {DbType.String, MySqlDbType.VarString},
            {DbType.Int64, MySqlDbType.Int64},
            {DbType.Int32, MySqlDbType.Int32},
            {DbType.Int16, MySqlDbType.Int16},
            {DbType.Byte, MySqlDbType.Byte},
            {DbType.Binary, MySqlDbType.Blob}
        }; 
        #endregion

        #region IDisposable
        public void Dispose()
        {

            if (_reader != null)
            {
                _reader.Dispose();
                _reader = null;
            }
            if (_command != null)
            {
                _command.Dispose();
                _command = null;
            }
            if (_connectionClosed && _connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
        #endregion

        #region Constructors
        public MySqlQuery(string connectionString) : this(connectionString, 0){}
        public MySqlQuery(string connectionString, int timeOut) : this(connectionString, timeOut, false){}
        public MySqlQuery(string connectionString, int timeOut, bool usePersistentConnection):this(new MySqlConnection(connectionString), timeOut, usePersistentConnection){}
        public MySqlQuery(MySqlConnection connection):this(connection, 0){}
        public MySqlQuery(MySqlConnection connection, int timeOut):this(connection, timeOut, false){}
        public MySqlQuery(MySqlConnection connection, int timeOut, bool usePersistentConnection)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            _connection = connection;
            _timeOut = timeOut;
            _usePersistentConnection = usePersistentConnection;

        }
        protected MySqlQuery(MySqlConnection connection, string sql, int timeOut, CommandType commandType, IEnumerable<SqlParameter> parameters)
        {
            _connection = connection;
            _timeOut = timeOut;
            if (_connection == null) throw new ArgumentNullException("connection");
            if (sql == null) throw new ArgumentNullException("sql");

            try {
                _connectionClosed = false;
                _connection.Open();
            } catch {
                _connectionClosed = false;
            }

            _command = new MySqlCommand(sql, _connection);
            _command.CommandType = commandType;
            _command.CommandTimeout = timeOut;

            if (parameters == null)
            {
                _reader = _command.ExecuteReader();
            }
            else
            {
                _command.Prepare();
                _command.Parameters.AddRange(parameters.Select(ToMySqlParameter).ToArray());
                _reader = _command.ExecuteReader();
            }
        }
        private MySqlParameter ToMySqlParameter(SqlParameter p)
        {
            var mysqlParameter = new MySqlParameter
            {
                ParameterName = p.ParameterName,
                Value = p.Value,
                DbType = p.DbType,
                IsNullable = p.IsNullable,
                Scale = p.Scale,
                Size = p.Size,
                Direction = p.Direction,
                Precision = p.Precision,
                SourceColumn = p.SourceColumn,
                SourceColumnNullMapping = p.SourceColumnNullMapping,
                SourceVersion = p.SourceVersion
            };
            return mysqlParameter;
        }
        #endregion

        #region Execute for one
        public T ExecuteSqlForOne<T>(string sql, Func<IRow, T> conversionAction)
        {
            return ExecuteSqlForOne(sql, null, conversionAction);
        }
        public T ExecuteSqlForOne<T>(string sql, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            var item = default(T);
            using (var q = new MySqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                while (q.Read())
                {
                    item = conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
            return item;
        }

        public T ExecuteProcedureForOne<T>(string procedureName, Func<IRow, T> conversionAction)
        {
            return ExecuteProcedureForOne(procedureName, null, conversionAction);
        }
        public T ExecuteProcedureForOne<T>(string procedureName, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            var item = default(T);
            using (var q = new MySqlQuery(_connection, procedureName, _timeOut, CommandType.StoredProcedure, parameters))
            {
                while (q.Read())
                {
                    item = conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
            return item;
        }
        #endregion

        #region Execute for many
        public IEnumerable<T> ExecuteSqlForMany<T>(string sql, Func<IRow, T> conversionAction)
        {
            return ExecuteSqlForMany(sql, null, conversionAction);
        }
        public IEnumerable<T> ExecuteSqlForMany<T>(string sql, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            using (var q = new MySqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                while (q.Read())
                {
                    yield return conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }

        public IEnumerable<T> ExecuteProcedureForMany<T>(string procedureName, Func<IRow, T> conversionAction)
        {
            return ExecuteProcedureForMany(procedureName, null, conversionAction);
        }
        public IEnumerable<T> ExecuteProcedureForMany<T>(string procedureName, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            using (var q = new MySqlQuery(_connection, procedureName, _timeOut, CommandType.StoredProcedure, parameters))
            {
                while (q.Read())
                {
                    yield return conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }
        #endregion

        #region Execute no result
        public void ExecuteSqlNoResult(string sql)
        {
            ExecuteSqlNoResult(sql, null);
        }
        public void ExecuteSqlNoResult(string sql, IEnumerable<SqlParameter> parameters)
        {
            using (var q = new MySqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                q.Read();
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }

        public void ExecuteProcedureNoResult(string procedureName)
        {
            ExecuteProcedureNoResult(procedureName, null);
        }
        public void ExecuteProcedureNoResult(string procedureName, IEnumerable<SqlParameter> parameters)
        {
            using (var q = new MySqlQuery(_connection, procedureName, _timeOut, CommandType.Text, parameters))
            {
                q.Read();
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }
        #endregion

        


        #region OrdinalColumn
        /// <summary>
        /// Returns the ordinal column number for a named column.
        /// The ordinal is cached so unlike SqlDataReader.GetOrdinal() it's not a performance hit to call it inside a loop.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public int this[string column]
        {
            get
            {
                if (!_columns.ContainsKey(column))
                    _columns[column] = _reader.GetOrdinal(column);
                return _columns[column];
            }
        }
        #endregion

        #region SqlDataReader Pass-through

        public bool GetBoolean(string column)
        {
            return _reader.GetBoolean(this[column]);
        }

        public byte GetByte(string column)
        {
            return _reader.GetByte(this[column]);
        }

        public DateTime GetDateTime(string column)
        {
            return _reader.GetDateTime(this[column]);
        }

        public decimal GetDecimal(string column)
        {
            return _reader.GetDecimal(this[column]);
        }

        public double GetDouble(string column)
        {
            return _reader.GetDouble(this[column]);
        }

        public float GetFloat(string column)
        {
            return _reader.GetFloat(this[column]);
        }

        public short GetInt16(string column)
        {
            return _reader.GetInt16(this[column]);
        }

        public int GetInt32(string column)
        {
            return _reader.GetInt32(this[column]);
        }

        public long GetInt64(string column)
        {
            return _reader.GetInt64(this[column]);
        }

        public string GetString(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetString(this[column]);
        }

        public bool IsDBNull(string column)
        {
            return _reader.IsDBNull(this[column]);
        }

        public bool Read()
        {
            return _reader.Read();
        }

        #endregion

        #region GetNullable

        public bool? GetNullableBoolean(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetBoolean(this[column]);
        }

        public byte? GetNullableByte(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetByte(this[column]);
        }

        public DateTime? GetNullableDateTime(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDateTime(this[column]);
        }

        public decimal? GetNullableDecimal(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDecimal(this[column]);
        }

        public double? GetNullableDouble(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDouble(this[column]);
        }

        public float? GetNullableFloat(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetFloat(this[column]);
        }

        public short? GetNullableInt16(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt16(this[column]);
        }

        public int? GetNullableInt32(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt32(this[column]);
        }

        public long? GetNullableInt64(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt64(this[column]);
        }

        #endregion
    }
}