Convert System.Type to SqlDbType

public static SqlDbType GetDBType(Type type)
{
SqlParameter p1 = new SqlParameter();
TypeConverter tc = TypeDescriptor.GetConverter(p1.DbType);

if (tc.CanConvertFrom(type))
p1.DbType = (DbType)tc.ConvertFrom(type.Name);
else
{
try { p1.DbType = (DbType)tc.ConvertFrom(type.Name); }
catch { }
}
return p1.SqlDbType;
}

public static SqlDbType GetDBType(Type type)
{
SqlParameter p1 = new SqlParameter();
TypeConverter tc = TypeDescriptor.GetConverter(p1.DbType);

if (tc.CanConvertFrom(type))
p1.DbType = (DbType)tc.ConvertFrom(type.Name);
else
{
try { p1.DbType = (DbType)tc.ConvertFrom(type.Name); }
catch { }
}
return p1.SqlDbType;
}

Thanks,
Atul Rungta
+91-9879662225
@*{Save Paper - Save Trees}*@

After some testing and finding that no one solution will fit.

Keeping in mind that the overall goal is to get the parameter values to actually attempt to execute the query I came up with a hybrid solution that may help these cases.

Keep in mind that this is designed and geared toward my specific needs and may not fully match yours.

First I defined a static enum that would allow me to manipulate this data and not have to worry about remembering the sizes for different variable types.

C#

public enum SqlValue
{
    BigInt = 8,
    Int = 4,
    TinyInt = 1,
    Date = 3,
    DateTime = 8,
    Bit = 1,
    Money = 8,
    Decimal = 8,
    Float = 8,
    NVarCharMax = 2147483647,
    UniqueIdentifier = 16
}

Next I attempted to map these data types manually to the system types that I might be using.

C#

internal static Dictionary<SqlDbType, SqlValue> dbTypeToSqlValueDictionary = new Dictionary<SqlDbType, SqlValue>()
{
    {SqlDbType.BigInt, SqlValue.BigInt},
    {SqlDbType.Int, SqlValue.Int},
    {SqlDbType.TinyInt, SqlValue.TinyInt},
    {SqlDbType.Date, SqlValue.Date},
    {SqlDbType.DateTime, SqlValue.DateTime},
    {SqlDbType.Bit, SqlValue.Bit},
    {SqlDbType.Money, SqlValue.Money},
    {SqlDbType.Decimal, SqlValue.Decimal},
    {SqlDbType.Float, SqlValue.Float},
    {SqlDbType.NVarChar, SqlValue.NVarCharMax},
    {SqlDbType.VarChar, SqlValue.NVarCharMax},
    {SqlDbType.UniqueIdentifier, SqlValue.UniqueIdentifier}
};

Next I implemented a static dictionary for my system to look up before it attempts a brute force conversion

C#

internal static Dictionary<Type, SqlDbType> typeToDBTypeDictionary = new Dictionary<Type, SqlDbType>()
{
    {typeof(long), SqlDbType.BigInt},
    {typeof(long?), SqlDbType.BigInt},
    {typeof(int), SqlDbType.Int},
    {typeof(int?), SqlDbType.Int},
    {typeof(byte), SqlDbType.TinyInt},
    {typeof(byte?), SqlDbType.TinyInt},
    {typeof(DateTime), SqlDbType.DateTime},
    {typeof(DateTime?), SqlDbType.DateTime},
    {typeof(bool), SqlDbType.Bit},
    {typeof(bool?), SqlDbType.Bit},
    {typeof(decimal), SqlDbType.Decimal},
    {typeof(decimal?), SqlDbType.Decimal},
    {typeof(double), SqlDbType.Float},
    {typeof(double?), SqlDbType.Float},
    {typeof(Guid), SqlDbType.UniqueIdentifier},
    {typeof(Guid?), SqlDbType.UniqueIdentifier},
    {typeof(string), SqlDbType.NVarChar}
};

Last we have the function that is called to convert this data. allowing you to pass in "override" values for both the SqlDBType and the Size of the parameter.

C#

internal static void GetDBType<TSelf>(TSelf value, ref SqlDbType? sqlDbType, ref int? size)
{
    Type type = typeof(TSelf);
    if (sqlDbType == null)
    {
        // Check to see if the type is defined in the static dictionary
        // if so return the type
        if (typeToDBTypeDictionary.ContainsKey(type))
            sqlDbType = typeToDBTypeDictionary[type];
        else
        {
            try
            {
                // Attempt to convert the variable using a type infer
                SqlMetaData metaData = SqlMetaData.InferFromValue(value, "DatabaseTypeMetaData");
                sqlDbType = metaData.SqlDbType;
            }
            catch
            {
                // Attempt to convert the variable using a type converter
                SqlParameter parameter = new SqlParameter();
                TypeConverter typeConverter = TypeDescriptor.GetConverter(parameter.SqlDbType);
                if (typeConverter.CanConvertFrom(type))
                    parameter.SqlDbType = (SqlDbType)typeConverter.ConvertFrom(type.Name);
                else
                {
                    // Try brute force
                    try
                    {
                        parameter.SqlDbType = (SqlDbType)typeConverter.ConvertFrom(type.Name);
                    }
                    // Do Nothing
                    catch { }
                }
                sqlDbType = parameter.SqlDbType;
            }
        }
    }
    if (size == null)
    {
        // check to see if the size is defined in the static dictionary
        // if so, return the size
        if (dbTypeToSqlValueDictionary.ContainsKey(sqlDbType.Value))
            size = (int)dbTypeToSqlValueDictionary[sqlDbType.Value];
        else
        {
            // Attempt to convert the variable using a type infer
            SqlMetaData metaData = new SqlMetaData("SizeMetaData", sqlDbType.Value);
            try
            {
                size = (int)metaData.MaxLength;
            }
            catch
            {
                size = (int)SqlValue.NVarCharMax;
            }
        }
    }
}

Hopefully this helps you get your result using this sort of logic.

I have tested this using the data types that my system uses and it appears to work correctly. If you want the client to be notified if it finally fails, throw an additional bool into the mix and have the system throw an error if it is unable to map your type to any other.

nvarchar(1)

It isn't generic as he says it is, and worse, if the converter cannot convert the type in question the code simply calls ConvertFrom anyway, catches any exception (even if it's totally unrelated, such as OutOfMemoryException!) and pretends everything worked out ok.

Thanks

Did you find the answer ? I have this problem too

private SqlDbType GetDBType(Type theType)
{
    SqlParameter p1 = new SqlParameter();
    TypeConverter tc = TypeDescriptor.GetConverter(p1.DbType);
    if (tc.CanConvertFrom(theType))
        p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
    else
    {
        //Try brute force
        try
        {
            p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
        }
        catch {} //Do Nothing
    }
    return p1.SqlDbType;
}

<br />
Microsoft.SqlServer.Server.SqlMetaData sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(new byte[] { 1, 2 }, "someName");<br />
					sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(2.3, "someName");<br />
					sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(11, "someName");<br />
					sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(System.Web.HttpValidationStatus.Valid, "someName");<br />
					sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue("hello", "someName");<br />
<br />
Console.WriteLine(sm.SqlDbType);<br />
Console.WriteLine(sm.TypeName);<br />
Console.WriteLine(sm.MaxLength);<br />

Your article is interesting and useful.
I am trying to find a method for convert
DbType to .NET Type but now I did not found

I will appreciate your help so much.

Tnanks,

Ing. Aarón Ibáñez Werthermänn
IBM Certified Specialist for RUP
Microsoft Certified Trainer
MCSD .NET, MCAD

Just curious. Why do you throw out the exception if the type conversion fails? Would you not want to report it back up to the caller since the conversion did not work?

Hello, I try your code to convert .net type to dbs type, but I have problem with byte[] (it throws exception - invalid type..). Any idea to fix this problem? Thx.

vipi

.net 1 or 2?
size of array?

try use SqlDbType instead DbType.
SqlDbType has VarBinary.

modified on Wednesday, April 30, 2008 4:39 AM

Convert System.Type to SqlDbType

Introduction

Background

Highlights

License

Comments and Discussions