System.Data.DbType to TSQL parameter datatype

Do you guys know of a mapping list or ideally a .NET standard class or function that will convert the System.Data.DbType enum options to a TSQL "string" corresponding to the specified SQL Server Type.

// Type: System.Data.DbType
// Assembly: System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
// MVID: CB77DBFA-81C4-4326-93F6-E2EEC5D3C28A
// Assembly location: C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll

    namespace System.Data
    {
      /// <summary>
      /// Specifies the data type of a field, a property, or a Parameter object of a .NET Framework data provider.
      /// </summary>
      /// <filterpriority>2</filterpriority>
      public enum DbType
      {
        AnsiString = 0,
        Binary = 1,
        Byte = 2,
        Boolean = 3,
        Currency = 4,
        Date = 5,
        DateTime = 6,
        Decimal = 7,
        Double = 8,
        Guid = 9,
        Int16 = 10,
        Int32 = 11,
        Int64 = 12,
        Object = 13,
        SByte = 14,
        Single = 15,
        String = 16,
        Time = 17,
        UInt16 = 18,
        UInt32 = 19,
        UInt64 = 20,
        VarNumeric = 21,
        AnsiStringFixedLength = 22,
        StringFixedLength = 23,
        Xml = 25,
        DateTime2 = 26,
        DateTimeOffset = 27,
      }
    }

Example:

DbType.Int32 -> INT
DbType.AnsiString -> VARCHAR
DbType.String -> NVARCHAR
DbType.Guid -> UNIQUEIDENTIFIER

Thank you for your help.

Answers


Here some lines of code which I wrote before:

 public enum Recurring_DayOfWeek
  {
    Sunday = 2,
    Monday = 4,
    Tuesday = 8,
    Wednesday = 16,
    Thursday = 32,
    Friday = 64,
    Saturday = 128
  }

Req_Param is the description of Enum but if you don't have description will work, in the EnumHelper I leave example....

     System.Data.SqlDbType sqlDatatype =     (System.Data.SqlDbType)Enum.Parse(typeof(System.Data.SqlDbType),     EnumHelper.EnumDescription(Req_Param), true);




using System.ComponentModel;
 public static class EnumHelper
{

    public enum Speed
    {
        [Description("5 metters per second")]
        Five = 5,
        [Description("10 metters per second")]
        Ten = 10,
        [Description("15 metters per second")]
        Fifteen = 15,
        [Description("20 metters per second")]
        Twenty = 20,
        //[Description("25 metters per second")]
        TwentyFive = 25,
       [Description("30 metters per second")]
        Thirty = 30
    }

    /// <summary>
    /// get the string value of Enum Attribute
    /// </summary>
    /// <param name="EnumConstant"></param>
    /// <returns>
    /// string enumDesctiption = EnumHelper.EnumDescription(EnumHelper.Speed.Thirty);
    ///  enumDesctiption = EnumHelper.EnumDescription(DayOfWeek.Monday); when there is no desc returns as string the ENUM property
    /// </returns>
    public static string EnumDescription(Enum EnumConstant)
    {
        System.Reflection.FieldInfo fi = EnumConstant.GetType().GetField(EnumConstant.ToString());
        DescriptionAttribute[] aattr = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
        if (aattr.Length > 0)
        {
          return aattr[0].Description;
        }
        else
        {
            return EnumConstant.ToString();
        }
    }



}

Be careful with BIT and validation C#, VB.NET <--> SQL


I can't remember where did I get this from but I'll post it for future reference.

 namespace CodeInsiders.SharpQL
    {
        using System;
        using System.Collections.Generic;
        using System.Data;

        public static class SqlMapper
        {
            private static readonly Dictionary<DbType, Type> DbTypeMapToNullableType = new Dictionary<DbType, Type>
                                                                                       {
                                                                                           { DbType.Byte, typeof(byte?) },
                                                                                           { DbType.SByte, typeof(sbyte?) },
                                                                                           { DbType.Int16, typeof(short?) },
                                                                                           { DbType.UInt16, typeof(ushort?) },
                                                                                           { DbType.Int32, typeof(int?) },
                                                                                           { DbType.UInt32, typeof(uint?) },
                                                                                           { DbType.Int64, typeof(long?) },
                                                                                           { DbType.UInt64, typeof(ulong?) },
                                                                                           { DbType.Single, typeof(float?) },
                                                                                           { DbType.Double, typeof(double?) },
                                                                                           { DbType.Decimal, typeof(decimal?) },
                                                                                           { DbType.Boolean, typeof(bool?) },
                                                                                           { DbType.StringFixedLength, typeof(char?) },
                                                                                           { DbType.Guid, typeof(Guid?) },
                                                                                           { DbType.DateTime, typeof(DateTime?) },
                                                                                           { DbType.DateTimeOffset, typeof(DateTimeOffset?) },
                                                                                           { DbType.Binary, typeof(byte[]) }
                                                                                       };

            private static readonly Dictionary<DbType, Type> DbTypeMapToType = new Dictionary<DbType, Type>
                                                                               {
                                                                                   { DbType.Byte, typeof(byte) },
                                                                                   { DbType.SByte, typeof(sbyte) },
                                                                                   { DbType.Int16, typeof(short) },
                                                                                   { DbType.UInt16, typeof(ushort) },
                                                                                   { DbType.Int32, typeof(int) },
                                                                                   { DbType.UInt32, typeof(uint) },
                                                                                   { DbType.Int64, typeof(long) },
                                                                                   { DbType.UInt64, typeof(ulong) },
                                                                                   { DbType.Single, typeof(float) },
                                                                                   { DbType.Double, typeof(double) },
                                                                                   { DbType.Decimal, typeof(decimal) },
                                                                                   { DbType.Boolean, typeof(bool) },
                                                                                   { DbType.String, typeof(string) },
                                                                                   { DbType.StringFixedLength, typeof(char) },
                                                                                   { DbType.Guid, typeof(Guid) },
                                                                                   { DbType.DateTime, typeof(DateTime) },
                                                                                   { DbType.DateTimeOffset, typeof(DateTimeOffset) },
                                                                                   { DbType.Binary, typeof(byte[]) }
                                                                               };

            private static readonly Dictionary<SqlDbType, Type> SqlDbTypeToNullableType = new Dictionary<SqlDbType, Type>
                                                                                          {
                                                                                              { SqlDbType.BigInt, typeof(long?) },
                                                                                              { SqlDbType.Binary, typeof(byte[]) },
                                                                                              { SqlDbType.Image, typeof(byte[]) },
                                                                                              { SqlDbType.Timestamp, typeof(byte[]) },
                                                                                              { SqlDbType.VarBinary, typeof(byte[]) },
                                                                                              { SqlDbType.Bit, typeof(bool?) },
                                                                                              { SqlDbType.Char, typeof(string) },
                                                                                              { SqlDbType.NChar, typeof(string) },
                                                                                              { SqlDbType.NText, typeof(string) },
                                                                                              { SqlDbType.NVarChar, typeof(string) },
                                                                                              { SqlDbType.Text, typeof(string) },
                                                                                              { SqlDbType.VarChar, typeof(string) },
                                                                                              { SqlDbType.Xml, typeof(string) },
                                                                                              { SqlDbType.DateTime, typeof(DateTime?) },
                                                                                              { SqlDbType.SmallDateTime, typeof(DateTime?) },
                                                                                              { SqlDbType.Date, typeof(DateTime?) },
                                                                                              { SqlDbType.Time, typeof(DateTime?) },
                                                                                              { SqlDbType.DateTime2, typeof(DateTime?) },
                                                                                              { SqlDbType.Decimal, typeof(decimal?) },
                                                                                              { SqlDbType.Money, typeof(decimal?) },
                                                                                              { SqlDbType.SmallMoney, typeof(decimal?) },
                                                                                              { SqlDbType.Float, typeof(double?) },
                                                                                              { SqlDbType.Int, typeof(int?) },
                                                                                              { SqlDbType.Real, typeof(float?) },
                                                                                              { SqlDbType.UniqueIdentifier, typeof(Guid?) },
                                                                                              { SqlDbType.SmallInt, typeof(short?) },
                                                                                              { SqlDbType.TinyInt, typeof(byte?) },
                                                                                              { SqlDbType.Variant, typeof(object) },
                                                                                              { SqlDbType.Udt, typeof(object) },
                                                                                              { SqlDbType.Structured, typeof(DataTable) },
                                                                                              { SqlDbType.DateTimeOffset, typeof(DateTimeOffset) }
                                                                                          };

            private static readonly Dictionary<SqlDbType, Type> SqlDbTypeToType = new Dictionary<SqlDbType, Type>
                                                                                  {
                                                                                      { SqlDbType.BigInt, typeof(long) },
                                                                                      { SqlDbType.Binary, typeof(byte[]) },
                                                                                      { SqlDbType.Image, typeof(byte[]) },
                                                                                      { SqlDbType.Timestamp, typeof(byte[]) },
                                                                                      { SqlDbType.VarBinary, typeof(byte[]) },
                                                                                      { SqlDbType.Bit, typeof(bool) },
                                                                                      { SqlDbType.Char, typeof(string) },
                                                                                      { SqlDbType.NChar, typeof(string) },
                                                                                      { SqlDbType.NText, typeof(string) },
                                                                                      { SqlDbType.NVarChar, typeof(string) },
                                                                                      { SqlDbType.Text, typeof(string) },
                                                                                      { SqlDbType.VarChar, typeof(string) },
                                                                                      { SqlDbType.Xml, typeof(string) },
                                                                                      { SqlDbType.DateTime, typeof(DateTime) },
                                                                                      { SqlDbType.SmallDateTime, typeof(DateTime) },
                                                                                      { SqlDbType.Date, typeof(DateTime) },
                                                                                      { SqlDbType.Time, typeof(DateTime) },
                                                                                      { SqlDbType.DateTime2, typeof(DateTime) },
                                                                                      { SqlDbType.Decimal, typeof(decimal) },
                                                                                      { SqlDbType.Money, typeof(decimal) },
                                                                                      { SqlDbType.SmallMoney, typeof(decimal) },
                                                                                      { SqlDbType.Float, typeof(double) },
                                                                                      { SqlDbType.Int, typeof(int) },
                                                                                      { SqlDbType.Real, typeof(float) },
                                                                                      { SqlDbType.UniqueIdentifier, typeof(Guid) },
                                                                                      { SqlDbType.SmallInt, typeof(short) },
                                                                                      { SqlDbType.TinyInt, typeof(byte) },
                                                                                      { SqlDbType.Variant, typeof(object) },
                                                                                      { SqlDbType.Udt, typeof(object) },
                                                                                      { SqlDbType.Structured, typeof(DataTable) },
                                                                                      { SqlDbType.DateTimeOffset, typeof(DateTimeOffset) }
                                                                                  };

            private static readonly Dictionary<Type, DbType> TypeToDbType = new Dictionary<Type, DbType>
                                                                            {
                                                                                { typeof(byte), DbType.Byte },
                                                                                { typeof(sbyte), DbType.SByte },
                                                                                { typeof(short), DbType.Int16 },
                                                                                { typeof(ushort), DbType.UInt16 },
                                                                                { typeof(int), DbType.Int32 },
                                                                                { typeof(uint), DbType.UInt32 },
                                                                                { typeof(long), DbType.Int64 },
                                                                                { typeof(ulong), DbType.UInt64 },
                                                                                { typeof(float), DbType.Single },
                                                                                { typeof(double), DbType.Double },
                                                                                { typeof(decimal), DbType.Decimal },
                                                                                { typeof(bool), DbType.Boolean },
                                                                                { typeof(string), DbType.String },
                                                                                { typeof(char), DbType.StringFixedLength },
                                                                                { typeof(Guid), DbType.Guid },
                                                                                { typeof(DateTime), DbType.DateTime },
                                                                                {
                                                                                    typeof(DateTimeOffset),
                                                                                    DbType.DateTimeOffset
                                                                                },
                                                                                { typeof(byte[]), DbType.Binary },
                                                                                { typeof(byte?), DbType.Byte },
                                                                                { typeof(sbyte?), DbType.SByte },
                                                                                { typeof(short?), DbType.Int16 },
                                                                                { typeof(ushort?), DbType.UInt16 },
                                                                                { typeof(int?), DbType.Int32 },
                                                                                { typeof(uint?), DbType.UInt32 },
                                                                                { typeof(long?), DbType.Int64 },
                                                                                { typeof(ulong?), DbType.UInt64 },
                                                                                { typeof(float?), DbType.Single },
                                                                                { typeof(double?), DbType.Double },
                                                                                { typeof(decimal?), DbType.Decimal },
                                                                                { typeof(bool?), DbType.Boolean },
                                                                                { typeof(char?), DbType.StringFixedLength },
                                                                                { typeof(Guid?), DbType.Guid },
                                                                                { typeof(DateTime?), DbType.DateTime },
                                                                                {
                                                                                    typeof(DateTimeOffset?),
                                                                                    DbType.DateTimeOffset
                                                                                },
                                                                            };

            public static Type ToClrType(this DbType dbType) {
                Type type;
                if (DbTypeMapToType.TryGetValue(dbType, out type)) {
                    return type;
                }

                throw new ArgumentOutOfRangeException("dbType", dbType, "Cannot map the DbType to Type");
            }

            public static Type ToClrType(this SqlDbType sqlDbType) {
                Type type;
                if (SqlDbTypeToType.TryGetValue(sqlDbType, out type)) {
                    return type;
                }

                throw new ArgumentOutOfRangeException("sqlDbType", sqlDbType, "Cannot map the SqlDbType to Type");
            }

            public static DbType ToDbType(this Type type) {
                DbType dbType;
                if (TypeToDbType.TryGetValue(type, out dbType)) {
                    return dbType;
                }

                throw new ArgumentOutOfRangeException("type", type, "Cannot map the Type to DbType");
            }

            public static Type ToNullableClrType(this DbType dbType) {
                Type type;
                if (DbTypeMapToNullableType.TryGetValue(dbType, out type)) {
                    return type;
                }

                throw new ArgumentOutOfRangeException("dbType", dbType, "Cannot map the DbType to Nullable Type");
            }

            public static Type ToNullableClrType(this SqlDbType sqlDbType) {
                Type type;
                if (SqlDbTypeToNullableType.TryGetValue(sqlDbType, out type)) {
                    return type;
                }

                throw new ArgumentOutOfRangeException("sqlDbType", sqlDbType, "Cannot map the SqlDbType to Nullable Type");
            }
        }
    }

Need Your Help

Moving/copying one remote database to another remote database

mysql database heroku migrate

I'm using heroku to deploy my app. So far, I've been using a development version of my app, and seeding some data into it. I also have a production version set up on heroku.