using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Globalization;

//https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

public static class Convertor
{
    public static SqlDbType GetDBType(Type type)
    {
        var sqlParam = new SqlParameter();
        var tc = TypeDescriptor.GetConverter(sqlParam.DbType);

        if (tc.CanConvertFrom(type))
        {
            sqlParam.DbType = (DbType)tc.ConvertFrom(type.Name);
        }
        else
        {
            //Try brute force
            try
            {
                sqlParam.DbType = (DbType)tc.ConvertFrom(type.Name);
            }
            catch
            {
                //Do Nothing; will return NVarChar as default
            }
        }
        return sqlParam.SqlDbType;
    }


    public static string ParseValue(SqlDbType psdtParameter, string pstrValue, string pstrDateFormat = null)
    {
        object objReturn = new object();
        if (pstrValue != "")
        {
            switch (psdtParameter.ToString())
            {
                case "BigInt":
                    objReturn = TypeDescriptor.GetConverter(typeof(Int64)).ConvertFromString(pstrValue);
                    break;
                case "Bit":
                    objReturn = TypeDescriptor.GetConverter(typeof(Boolean)).ConvertFromString(pstrValue);
                    break;
                case "NText":
                case "NVarChar":
                case "VarChar":
                case "NChar":
                case "Text":
                case "Char":
                    objReturn = TypeDescriptor.GetConverter(typeof(String)).ConvertFromString(pstrValue);
                    break;
                case "SmallDateTime":
                case "DateTime":
                    objReturn = DateTime.ParseExact(pstrValue, pstrDateFormat, CultureInfo.InvariantCulture);
                    //TypeDescriptor.GetConverter(typeof(DateTime)).ConvertFromString(pstrValue);
                    break;
                case "Money":
                case "SmallMoney":
                case "Decimal":
                    objReturn = TypeDescriptor.GetConverter(typeof(Decimal)).ConvertFromString(null, CultureInfo.InvariantCulture, pstrValue);
                    break;
                case "Float":
                    objReturn = TypeDescriptor.GetConverter(typeof(Double)).ConvertFromString(pstrValue);
                    break;
                case "Binary":
                case "VarBinary":
                case "Timestamp":
                case "Image":
                    objReturn = TypeDescriptor.GetConverter(typeof(Byte[])).ConvertFromString(pstrValue);
                    break;
                case "Int":
                    objReturn = TypeDescriptor.GetConverter(typeof(Int32)).ConvertFromString(pstrValue);
                    break;
                case "Real":
                    objReturn = TypeDescriptor.GetConverter(typeof(Single)).ConvertFromString(pstrValue);
                    break;
                case "SmallInt":
                    objReturn = TypeDescriptor.GetConverter(typeof(Int16)).ConvertFromString(pstrValue);
                    break;
                case "TinyInt":
                    objReturn = TypeDescriptor.GetConverter(typeof(Byte)).ConvertFromString(pstrValue);
                    break;
            }
            return objReturn.ToString();
        }
        else
        {
            return null;
        }
    }

    public static Type GetClrType(SqlDbType sqlDataType)
    {
        switch (sqlDataType)
        {
            case SqlDbType.BigInt:
                return typeof(long?);

            case SqlDbType.Binary:
            case SqlDbType.Image:
            case SqlDbType.Timestamp:
            case SqlDbType.VarBinary:
                return typeof(byte[]);

            case SqlDbType.Bit:
                return typeof(bool?);

            case SqlDbType.Char:
            case SqlDbType.NChar:
            case SqlDbType.NText:
            case SqlDbType.NVarChar:
            case SqlDbType.Text:
            case SqlDbType.VarChar:
            case SqlDbType.Xml:
                return typeof(string);

            case SqlDbType.DateTime:
            case SqlDbType.SmallDateTime:
            case SqlDbType.Date:
            case SqlDbType.Time:
            case SqlDbType.DateTime2:
                return typeof(DateTime?);

            case SqlDbType.Decimal:
            case SqlDbType.Money:
            case SqlDbType.SmallMoney:
                return typeof(decimal?);

            case SqlDbType.Float:
                return typeof(double?);

            case SqlDbType.Int:
                return typeof(int?);

            case SqlDbType.Real:
                return typeof(float?);

            case SqlDbType.UniqueIdentifier:
                return typeof(Guid?);

            case SqlDbType.SmallInt:
                return typeof(short?);

            case SqlDbType.TinyInt:
                return typeof(byte?);

            case SqlDbType.Variant:
            case SqlDbType.Udt:
                return typeof(object);

            case SqlDbType.Structured:
                return typeof(DataTable);

            case SqlDbType.DateTimeOffset:
                return typeof(DateTimeOffset?);

            default:
                throw new ArgumentOutOfRangeException("sqlDataType");
        }
    }
}