Click here to Skip to main content
14,030,226 members
Click here to Skip to main content
Add your own
alternative version

Stats

84.1K views
24 bookmarked
Posted 8 Dec 2006
Licenced CPOL

Convert System.Type to SqlDbType

, 8 Dec 2006
Rate this:
Please Sign up or sign in to vote.
Convert any compatible System.Type to SqlDbType.

Introduction

This is a generic implementation of converting any compatible System.Type to SqlDbType for use in parameter objects.

Background

I wanted a generic method to convert system data types into a format which can be given to Parameter objects. After a lot of online search, I came across only some crude implementations which involved elaborate Select Case constructs.

Here is my implementation:

Private Function GetDBType(ByVal theType As System.Type) As SqlDbType
    Dim p1 As SqlClient.SqlParameter
    Dim tc As System.ComponentModel.TypeConverter
    p1 = New SqlClient.SqlParameter()
    tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType)
    If tc.CanConvertFrom(theType) Then
        p1.DbType = tc.ConvertFrom(theType.Name)
    Else
        'Try brute force
        Try
            p1.DbType = tc.ConvertFrom(theType.Name)
        Catch ex As Exception
            'Do Nothing
        End Try
    End If
    Return p1.SqlDbType
End Function

Highlights

This code uses intrinsic converters available in most objects. The object TypeConverter is the key in this case. These converters are also used by the system to persist data in XML files. The method GetConverter of TypeDescriptor retrieves the TypeConverter associated with the target object for which conversion is to be performed.

Simply use this function where needed. It will get you the SqlDbType equivalent of the System.Type passed to it as far as possible; else, by default, it will return the String data type equivalent. For SqlDbType, it gives NVarChar.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Girish Chandra
Web Developer
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Rungta Atul25-Feb-16 23:40
memberRungta Atul25-Feb-16 23:40 
SuggestionC# Version Pin
Rungta Atul25-Feb-16 23:39
memberRungta Atul25-Feb-16 23:39 
SuggestionHybrid Solution Pin
Komron Nouri12-Aug-14 14:57
memberKomron Nouri12-Aug-14 14:57 
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.

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.

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

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.

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.
GeneralMy vote of 1 Pin
mohamad dianaty22-Apr-13 22:41
membermohamad dianaty22-Apr-13 22:41 
GeneralMy vote of 2 Pin
dojohansen9-Nov-09 2:35
memberdojohansen9-Nov-09 2:35 
QuestionI cannot use this method on Compact Framework, Please Help Me Pin
rizaSHSHKA23-May-09 1:17
memberrizaSHSHKA23-May-09 1:17 
AnswerRe: I cannot use this method on Compact Framework, Please Help Me Pin
donkhuan22-Dec-09 7:33
memberdonkhuan22-Dec-09 7:33 
GeneralC# version of the code Pin
mcsjr19-Aug-08 5:58
membermcsjr19-Aug-08 5:58 
AnswerTry SqlMetaData Pin
TheMega5-May-08 0:03
memberTheMega5-May-08 0:03 
GeneralI need to Convert from DbType to .NET Type Pin
AIbanezW20-Jan-08 20:22
memberAIbanezW20-Jan-08 20:22 
QuestionWhy throw out the exception? Pin
varnk21-May-07 10:52
membervarnk21-May-07 10:52 
Generaldoesn't work with byte array Pin
pindurav18-Feb-07 20:34
memberpindurav18-Feb-07 20:34 
QuestionRe: doesn't work with byte array [modified] Pin
TheMega29-Apr-08 22:24
memberTheMega29-Apr-08 22:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.190419.4 | Last Updated 8 Dec 2006
Article Copyright 2006 by Girish Chandra
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid