C#

C#, IF-Else vs Dictionary

Updated:

Well, this might be an example of over-engineered. Recently I have an attempt to try to optimize a section of code. Here’s the original If-Else statements:

public static void ConvertToSqlFormat(StringBuilder sb, Object ob, MySqlColumn col, 
                                      bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    if (ob == null || ob is System.DBNull)
    {
        sb.AppendFormat("NULL");
    }
    else if (ob is System.String)
    {
        string str = (string)ob;

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

        //sb.Append(str);
        if (escapeStringSequence)
            EscapeStringSequence(sb, str);

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");
    }
    else if (ob is System.Boolean)
    {
        sb.AppendFormat(Convert.ToInt32(ob).ToString());
    }
    else if (ob is System.Byte[])
    {
        ConvertByteArrayToHexString(sb, ob);
    }
    else if (ob is short)
    {
        sb.AppendFormat(((short)ob).ToString(_numberFormatInfo));
    }
    else if (ob is int)
    {
        sb.AppendFormat(((int)ob).ToString(_numberFormatInfo));
    }
    else if (ob is long)
    {
        sb.AppendFormat(((long)ob).ToString(_numberFormatInfo));
    }
    else if (ob is ushort)
    {
        sb.AppendFormat(((ushort)ob).ToString(_numberFormatInfo));
    }
    else if (ob is uint)
    {
        sb.AppendFormat(((uint)ob).ToString(_numberFormatInfo));
    }
    else if (ob is ulong)
    {
        sb.AppendFormat(((ulong)ob).ToString(_numberFormatInfo));
    }
    else if (ob is double)
    {
        sb.AppendFormat(((double)ob).ToString(_numberFormatInfo));
    }
    else if (ob is decimal)
    {
        sb.AppendFormat(((decimal)ob).ToString(_numberFormatInfo));
    }
    else if (ob is float)
    {
        sb.AppendFormat(((float)ob).ToString(_numberFormatInfo));
    }
    else if (ob is byte)
    {
        sb.AppendFormat(((byte)ob).ToString(_numberFormatInfo));
    }
    else if (ob is sbyte)
    {
        sb.AppendFormat(((sbyte)ob).ToString(_numberFormatInfo));
    }
    else if (ob is TimeSpan)
    {
        TimeSpan ts = (TimeSpan)ob;

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

        sb.AppendFormat(((int)ts.TotalHours).ToString().PadLeft(2, '0'));
        sb.AppendFormat(":");
        sb.AppendFormat(ts.Duration().Minutes.ToString().PadLeft(2, '0'));
        sb.AppendFormat(":");
        sb.AppendFormat(ts.Duration().Seconds.ToString().PadLeft(2, '0'));

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

    }
    else if (ob is System.DateTime)
    {
        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

        sb.AppendFormat(((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

        if (col != null && col.TimeFractionLength > 0)
        {
            sb.Append(".");
            string _microsecond = ((DateTime)ob).ToString("".PadLeft(col.TimeFractionLength, 'f'));
            sb.Append(_microsecond);
        }

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");
    }
    else if (ob is MySqlDateTime mdt)
    {
        if (mdt.IsValidDateTime)
        {
            DateTime dtime = mdt.GetDateTime();

            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");

            if (col != null)
            {
                if (col.MySqlDataType == "datetime")
                    sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
                else if (col.MySqlDataType == "date")
                    sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
                else if (col.MySqlDataType == "time")
                    sb.AppendFormat("{0}:{1}:{2}", mdt.Hour, mdt.Minute, mdt.Second);
                else
                    sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

                if (col.TimeFractionLength > 0)
                {
                    sb.Append(".");
                    sb.Append(((MySqlDateTime)ob).Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
                }
            }
            else
            {
                sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
            }

            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");
        }
        else
        {
            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");

            if (col != null)
            {
                if (col.MySqlDataType == "datetime")
                    sb.AppendFormat("0000-00-00 00:00:00");
                else if (col.MySqlDataType == "date")
                    sb.AppendFormat("0000-00-00");
                else if (col.MySqlDataType == "time")
                    sb.AppendFormat("00:00:00");
                else
                    sb.AppendFormat("0000-00-00 00:00:00");

                if (col.TimeFractionLength > 0)
                {
                    sb.Append(".".PadRight(col.TimeFractionLength, '0'));
                }
            }
            else
            {
                sb.AppendFormat("0000-00-00 00:00:00");
            }

            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");
        }
    }
    else if (ob is System.Guid)
    {
        if (col != null && col.MySqlDataType == "binary(16)")
        {
            ConvertByteArrayToHexString(sb, ob);
        }
        else if (col != null && col.MySqlDataType == "char(36)")
        {
            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");

            sb.Append(ob);

            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");
        }
        else
        {
            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");

            sb.Append(ob);

            if (wrapStringWithSingleQuote)
                sb.AppendFormat("'");
        }
    }
    else
    {
        throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
    }
}

and this is the Dictionary statements:

public static void ConvertToSqlFormat(StringBuilder sb, Object ob, MySqlColumn col, 
                                      bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    if (_dicSqlValueConversionHandler.TryGetValue(ob.GetType(), out var _handler))
    {
        _handler(sb, ob, col, escapeStringSequence, wrapStringWithSingleQuote);
    }
    else
    {
        throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
    }
}

static Dictionary<Type, Action<StringBuilder, Object, MySqlColumn, bool, bool>> _dicSqlValueConversionHandler = new Dictionary<Type, Action<StringBuilder, object, MySqlColumn, bool, bool>>()
{
    { typeof(string), HandleString },
    { typeof(bool), HandleBoolean },
    { typeof(byte[]), HandleByteArray },
    { typeof(short), HandleShort },
    { typeof(int), HandleInt },
    { typeof(long), HandleLong },
    { typeof(ushort), HandleUShort },
    { typeof(uint), HandleUInt },
    { typeof(ulong), HandleULong },
    { typeof(double), HandleDouble },
    { typeof(decimal), HandleDecimal },
    { typeof(float), HandleFloat },
    { typeof(byte), HandleByte },
    { typeof(sbyte), HandleSByte },
    { typeof(TimeSpan), HandleTimeSpan },
    { typeof(DateTime), HandleDateTime },
    { typeof(MySqlDateTime), HandleMySqlDateTime },
    { typeof(Guid), HandleGuid }
};

#region Handler Methods

private static void HandleString(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    var str = (string)ob;

    if (wrapStringWithSingleQuote)
        sb.Append("'");

    if (escapeStringSequence)
        EscapeStringSequence(sb, str);
    else
        sb.Append(str);

    if (wrapStringWithSingleQuote)
        sb.Append("'");
}

private static void HandleBoolean(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(Convert.ToInt32(ob).ToString());
}

private static void HandleByteArray(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    ConvertByteArrayToHexString(sb, ob);
}

private static void HandleShort(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((short)ob).ToString(_numberFormatInfo));
}

private static void HandleInt(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((int)ob).ToString(_numberFormatInfo));
}

private static void HandleLong(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((long)ob).ToString(_numberFormatInfo));
}

private static void HandleUShort(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((ushort)ob).ToString(_numberFormatInfo));
}

private static void HandleUInt(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((uint)ob).ToString(_numberFormatInfo));
}

private static void HandleULong(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((ulong)ob).ToString(_numberFormatInfo));
}

private static void HandleDouble(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((double)ob).ToString(_numberFormatInfo));
}

private static void HandleDecimal(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((decimal)ob).ToString(_numberFormatInfo));
}

private static void HandleFloat(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((float)ob).ToString(_numberFormatInfo));
}

private static void HandleByte(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((byte)ob).ToString(_numberFormatInfo));
}

private static void HandleSByte(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence, wrapStringWithSingleQuote
    sb.Append(((sbyte)ob).ToString(_numberFormatInfo));
}

private static void HandleTimeSpan(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: col, escapeStringSequence
    var ts = (TimeSpan)ob;

    if (wrapStringWithSingleQuote)
        sb.Append("'");

    sb.Append(((int)ts.TotalHours).ToString().PadLeft(2, '0'));
    sb.Append(":");
    sb.Append(ts.Duration().Minutes.ToString().PadLeft(2, '0'));
    sb.Append(":");
    sb.Append(ts.Duration().Seconds.ToString().PadLeft(2, '0'));

    if (wrapStringWithSingleQuote)
        sb.Append("'");
}

private static void HandleDateTime(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: escapeStringSequence
    var dateTime = (DateTime)ob;

    if (wrapStringWithSingleQuote)
        sb.Append("'");

    sb.Append(dateTime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

    if (col != null && col.TimeFractionLength > 0)
    {
        sb.Append(".");
        string microsecond = dateTime.ToString("".PadLeft(col.TimeFractionLength, 'f'));
        sb.Append(microsecond);
    }

    if (wrapStringWithSingleQuote)
        sb.Append("'");
}

private static void HandleMySqlDateTime(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: escapeStringSequence
    var mdt = (MySqlDateTime)ob;

    if (mdt.IsValidDateTime)
    {
        var dtime = mdt.GetDateTime();

        if (wrapStringWithSingleQuote)
            sb.Append("'");

        if (col != null)
        {
            if (col.MySqlDataType == "datetime")
                sb.Append(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
            else if (col.MySqlDataType == "date")
                sb.Append(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
            else if (col.MySqlDataType == "time")
                sb.Append($"{mdt.Hour}:{mdt.Minute}:{mdt.Second}");
            else
                sb.Append(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

            if (col.TimeFractionLength > 0)
            {
                sb.Append(".");
                sb.Append(mdt.Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
            }
        }
        else
        {
            sb.Append(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
        }

        if (wrapStringWithSingleQuote)
            sb.Append("'");
    }
    else
    {
        if (wrapStringWithSingleQuote)
            sb.Append("'");

        if (col != null)
        {
            if (col.MySqlDataType == "datetime")
                sb.Append("0000-00-00 00:00:00");
            else if (col.MySqlDataType == "date")
                sb.Append("0000-00-00");
            else if (col.MySqlDataType == "time")
                sb.Append("00:00:00");
            else
                sb.Append("0000-00-00 00:00:00");

            if (col.TimeFractionLength > 0)
            {
                sb.Append(".");
                sb.Append(new string('0', col.TimeFractionLength));
            }
        }
        else
        {
            sb.Append("0000-00-00 00:00:00");
        }

        if (wrapStringWithSingleQuote)
            sb.Append("'");
    }
}

private static void HandleGuid(StringBuilder sb, object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    // Ignores: escapeStringSequence
    if (col != null && col.MySqlDataType == "binary(16)")
    {
        ConvertByteArrayToHexString(sb, ob);
    }
    else if (col != null && col.MySqlDataType == "char(36)")
    {
        if (wrapStringWithSingleQuote)
            sb.Append("'");

        sb.Append(ob);

        if (wrapStringWithSingleQuote)
            sb.Append("'");
    }
    else
    {
        if (wrapStringWithSingleQuote)
            sb.Append("'");

        sb.Append(ob);

        if (wrapStringWithSingleQuote)
            sb.Append("'");
    }
}

#endregion

public static void ConvertToSqlFormat(StringBuilder sb, Object ob, MySqlColumn col, bool escapeStringSequence, bool wrapStringWithSingleQuote)
{
    if (_dicSqlValueConversionHandler.TryGetValue(ob.GetType(), out var _handler))
    {
        _handler(sb, ob, col, escapeStringSequence, wrapStringWithSingleQuote);
    }
    else
    {
        throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
    }
}

and I put a benchmark test on big amount of rows (500000 rows) and columns in a loop. Here’s the result:

1st batch of test:

dictionary
---------------------------------
Round 1    0h 0m 32s 356ms    717.023 MB
Round 2    0h 0m 31s 749ms    717.023 MB
Round 3    0h 0m 31s 731ms    717.023 MB

if else
---------------------------------
Round 1    0h 0m 32s 987ms    717.023 MB
Round 2    0h 0m 31s 780ms    717.023 MB
Round 3    0h 0m 31s 982ms    717.023 MB

2nd batch of test:

dictionary
---------------------------------
Round 1    0h 0m 33s 168ms    717.023 MB
Round 2    0h 0m 34s 204ms    717.023 MB
Round 3    0h 0m 33s 193ms    717.023 MB

if else
---------------------------------
Round 1    0h 0m 31s 993ms    717.023 MB
Round 2    0h 0m 31s 881ms    717.023 MB
Round 3    0h 0m 31s 806ms    717.023 MB

Well, em… that doesn’t look like “a lot” of performance boost or differences. The test is carried out with the cpu Intel Core i7-4770S (3.10-3.90 GHz, 4 cores 8 threads), not sure the cpu has something to do with this test or not.

Github | Youtube | X.com | Facebook

© adriancs.com. All Rights Reserved.