MySQLHelper.cs

using MySql.Data.MySqlClient;
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Data;
using System.Text.RegularExpressions;
using System.Collections.Concurrent;

/// <summary>
///     Helps you with your MySql queries!
///     <para>
///         Before start using this class, initialize your MySql credentials using <see cref="Setup(string, int, string, string, string)"/>
///     </para>
/// </summary>
public static class MySQLHelper {
    private static string host = "localhost",database,username = "root",password = "root",connStr = "";
    private static int port = 3306;
    private static bool initialized = false, tor = false;

    /// <returns>
    /// MySQL Connection with parameters that has been passed to <see cref="Setup(string, int, string, string, string)"/> method
    /// </returns>
    public static MySqlConnection Connection {
        get {
            return new MySqlConnection(connStr!=""?connStr:("Server=" + host + ";Database=" + database + ";port=" + port + ";User Id=" + username + ";password=" + password));
        }
    }

    /// <returns>
    /// <seealso cref="DbDataReader"/> that has been created after successful <see cref="Select(string, object[])"/> call
    /// </returns>
    public static DbDataReader ResultAsReader { get; private set; }

    /// <returns>
    /// <see cref="ResultAsReader"/> in array form
    /// </returns>
    public static Dictionary<string, object>[] ResultAsArray {
        get {
            return ToArray(ResultAsReader);
        }
    }

    /// <returns>
    /// Exception that has been thrown after unsuccessful <see cref="Select(string, object[])"/> or <see cref="Update(string, object[])"/> call
    /// </returns>
    public static Exception LastError { get; private set; }

    /// <summary>
    /// Initializes MySQL connection parameters
    /// </summary>
    /// <param name="host">Hostname of MySQL server, "localhost" by default</param>
    /// <param name="port">Port, 3306 by default</param>
    /// <param name="database">Database name</param>
    /// <param name="username">Login to use for authorization, "root" by default</param>
    /// <param name="password">Password to use for authorization, "root" by default</param>
    public static void Setup(string host, int port, string database, string username, string password) {
        connStr = "";
        MySQLHelper.host = host;
        MySQLHelper.port = port;
        MySQLHelper.database = database;
        MySQLHelper.username = username;
        MySQLHelper.password = password;
        initialized = true;
    }

    /// <summary>
    /// Sets the database to access
    /// </summary>
    /// <param name="db">Database name</param>
    public static void SetDatabase(string db) {
        database = db;
        initialized = true;
    }

    /// <summary>
    /// Expert setup
    /// </summary>
    /// <param name="connectionString">string that <see cref="MySqlConnection"/> uses for initialization</param>
    public static void Setup(string connectionString) {
        connStr = connectionString;
        initialized = true;
    }

    /// <summary>
    /// When sql error occurs this method will decide, throw an exception or not
    /// </summary>
    public static void ThrowOnError(bool val) {
        tor = val;
    }

    /// <summary>
    ///     Performs an select request 
    /// </summary>
    /// <returns><see cref="bool"/> value, whenever request was successful or not</returns>
    /// <remarks>
    ///     Important!
    ///     This method does not return any <see cref="DbDataReader"/> class, instead it writes it to <seealso cref="ResultAsReader"/>.
    /// </remarks>
    /// <param name="request">Query string, use ? for marking variables</param>
    /// <param name="variables">Variables to be passed in request, usage:
    ///     <para>
    ///         <example>
    ///             1.<see cref="MySQLHelper"/> will try to automatically detect type of each element in array and assign these values to variables in request
    ///             <code>
    ///                 Update("SELECT value1 FROM myTable WHERE value2=? value3=?", 1, "example");
    ///             </code>
    ///         </example>
    ///     </para>
    ///     <para>
    ///         <example>
    ///             2.You manually setting type for values in array and <see cref="MySQLHelper"/> will assign these values to variables in request
    ///             <code>
    ///                 Update("SELECT value1 FROM myTable WHERE value2=? value3=?", <see cref="MySqlDbType.Int32"/>, 1, <see cref="MySqlDbType.String"/>, "example");
    ///             </code>
    ///         </example>
    ///     </para>
    ///     <remarks>
    ///         You may also mix Types and Non-Types values for variables if you are sure about type of passed objects
    ///     </remarks>
    /// </param>
    public static bool Select(string request, params object[] variables) {
        if (!initialized) throw new Exception("MySQLHelper is not initialized yet.In order to use it,you need to setup it first!");
        MySqlConnection conn = Connection;
        conn.Open();
        try {
            var dt = new DataTable();
            dt.Load(BuildCommand(conn, request, variables).ExecuteReader());
            ResultAsReader = dt.CreateDataReader();
            return true;
        } catch(Exception e) {
            LastError = e;
            if (tor) throw e;
            return false;
        } finally {
            conn.Close();
            conn.Dispose();
        }
    }

    /// <summary>
    ///     Performs an update. insert or delete request 
    /// </summary>
    /// <returns><see cref="bool"/> value, whenever request was successful or not</returns>
    /// <param name="request">Query string, use ? for marking variables</param>
    /// <param name="variables">Variables to be passed in request, usage:
    ///     <para>
    ///         <example>
    ///             1.<see cref="MySQLHelper"/> will try to automatically detect type of each element in array and assign these values to variables in request
    ///             <code>
    ///                 Update("UPDATE myTable SET value1=? value2=?", 1, "example");
    ///             </code>
    ///         </example>
    ///     </para>
    ///     <para>
    ///         <example>
    ///             2.You manually setting type for values in array and <see cref="MySQLHelper"/> will assign these values to variables in request
    ///             <code>
    ///                 Update("UPDATE myTable SET value1=? value2=?", <see cref="MySqlDbType.Int32"/>, 1, <see cref="MySqlDbType.String"/>, "example");
    ///             </code>
    ///         </example>
    ///     </para>
    ///     <remarks>
    ///         You may also mix Types and Non-Types values for variables if you are sure about type of passed objects
    ///     </remarks>
    /// </param>
    public static bool Update(string request,params object[] variables) {
        if (!initialized) throw new Exception("MySQLHelper is not initialized yet.In order to use it,you need to setup it first!");
        MySqlConnection conn = Connection;
        conn.Open();
        try {
            BuildCommand(conn, request, variables).ExecuteNonQuery();
            return true;
        } catch(Exception e) {
            LastError = e;
            if (tor) throw e;
            return false;
        } finally {
            conn.Close();
            conn.Dispose();
        }
    }

    private static Dictionary<string,object>[] ToArray(DbDataReader reader) {
        List<Dictionary<string, object>> ret = new List<Dictionary<string, object>>();
        if(reader.HasRows)
            while (reader.Read()) {
                Dictionary<string, object> temp = new Dictionary<string, object>();
                for(int i = 0;i < reader.FieldCount;i++)
                    temp[reader.GetName(i)] = reader.GetValue(i);
                ret.Add(temp);
            }
        return ret.ToArray();
    }

    private static MySqlCommand BuildCommand(MySqlConnection conn,string query,object[] data) {
        string req = query;
        for (int i = 0; req.Contains("?"); i++) req = new Regex(Regex.Escape("?")).Replace(req, "@" + i,1);
        MySqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = req;
        for (int i = 0; i < data.Length; i++)
            if (data[i].GetType() == typeof(MySqlDbType))
                cmd.Parameters.Add("@" + i, (MySqlDbType)data[i]).Value = data[i++];
            else
                cmd.Parameters.Add("@" + i, data[i].GetSqlType()).Value = data[i];
        return cmd;
    }

    /// <inheritdoc cref="ToSqlType(Type)"/>
    public static MySqlDbType GetSqlType(this Object obj) {
        return obj.GetType().ToSqlType();
    }

    /// <summary>
    ///     Attempting to convert <see cref="Type"/> to <see cref="MySqlDbType"/>. If convertion fails, this method returns universal MySql type (<seealso cref="MySqlDbType.LongBlob"/>)
    /// </summary>
    public static MySqlDbType ToSqlType(this Type giveType) {
        var typeMap = new Dictionary<Type, MySqlDbType>();
        typeMap[typeof(string)] = MySqlDbType.String;
        typeMap[typeof(char[])] = MySqlDbType.VarChar;
        typeMap[typeof(char)] = MySqlDbType.VarChar;
        typeMap[typeof(short)] = MySqlDbType.Int16;
        typeMap[typeof(int)] = MySqlDbType.Int32;
        typeMap[typeof(long)] = MySqlDbType.Int64;
        typeMap[typeof(byte[])] = MySqlDbType.VarBinary;
        typeMap[typeof(bool)] = MySqlDbType.Bit;
        typeMap[typeof(DateTime)] = MySqlDbType.DateTime;
        typeMap[typeof(decimal)] = MySqlDbType.Decimal;
        typeMap[typeof(double)] = MySqlDbType.Double;
        typeMap[typeof(float)] = MySqlDbType.Float;
        typeMap[typeof(byte)] = MySqlDbType.Byte;
        typeMap[typeof(TimeSpan)] = MySqlDbType.Time;
        typeMap[typeof(Guid)] = MySqlDbType.Guid;
        return typeMap.ContainsKey(giveType)?typeMap[giveType]:MySqlDbType.LongBlob;
    }

    //Custom getters
    public static ushort GetUInt16(this DbDataReader reader, int ordinal) {
        return (UInt16)reader.GetValue(ordinal);
    }
    public static uint GetUInt32(this DbDataReader reader, int ordinal) {
        return (UInt32)reader.GetValue(ordinal);
    }
    public static ulong GetUInt64(this DbDataReader reader, int ordinal) {
        return (UInt64)reader.GetValue(ordinal);
    }
    public static ushort GetUInt16(this DbDataReader reader, string id) {
        return (UInt16)reader.GetValue(reader.GetOrdinal(id));
    }
    public static uint GetUInt32(this DbDataReader reader, string id) {
        return (UInt32)reader.GetValue(reader.GetOrdinal(id));
    }
    public static ulong GetUInt64(this DbDataReader reader, string id) {
        return (UInt64)reader.GetValue(reader.GetOrdinal(id));
    }

    //Shortened getters
    public static short GetInt16(this DbDataReader reader, string id) {
        return reader.GetInt16(reader.GetOrdinal(id));
    }
    public static int GetInt32(this DbDataReader reader, string id) {
        return reader.GetInt32(reader.GetOrdinal(id));
    }
    public static long GetInt64(this DbDataReader reader, string id) {
        return reader.GetInt64(reader.GetOrdinal(id));
    }
    public static string GetString(this DbDataReader reader, string id) {
        return reader.GetString(reader.GetOrdinal(id));
    }
    public static bool GetBoolean(this DbDataReader reader, string id) {
        return reader.GetBoolean(reader.GetOrdinal(id));
    }
    public static byte GetByte(this DbDataReader reader, string id) {
        return reader.GetByte(reader.GetOrdinal(id));
    }
    public static char GetChar(this DbDataReader reader, string id) {
        return reader.GetChar(reader.GetOrdinal(id));
    }
    public static DateTime GetDateTime(this DbDataReader reader, string id) {
        return reader.GetDateTime(reader.GetOrdinal(id));
    }
    public static decimal GetDecimal(this DbDataReader reader, string id) {
        return reader.GetDecimal(reader.GetOrdinal(id));
    }
    public static double GetDouble(this DbDataReader reader, string id) {
        return reader.GetDouble(reader.GetOrdinal(id));
    }
    public static float GetFloat(this DbDataReader reader, string id) {
        return reader.GetFloat(reader.GetOrdinal(id));
    }
    public static Guid GetGuid(this DbDataReader reader, string id) {
        return reader.GetGuid(reader.GetOrdinal(id));
    }
    public static Stream GetStream(this DbDataReader reader, string id) {
        return reader.GetStream(reader.GetOrdinal(id));
    }
    public static TextReader GetTextReader(this DbDataReader reader, string id) {
        return reader.GetTextReader(reader.GetOrdinal(id));
    }
    public static object GetValue(this DbDataReader reader, string id) {
        return reader.GetValue(reader.GetOrdinal(id));
    }
}