Click here to Skip to main content
14,661,658 members
Articles » Languages » VB.NET » HowTo
Posted 8 Dec 2006


24 bookmarked

Convert System.Type to SqlDbType

Rate this:
3.20 (13 votes)
Please Sign up or sign in to vote.
3.20 (13 votes)
8 Dec 2006CPOL
Convert any compatible System.Type to SqlDbType.


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


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)
        'Try brute force
            p1.DbType = tc.ConvertFrom(theType.Name)
        Catch ex As Exception
            'Do Nothing
        End Try
    End If
    Return p1.SqlDbType
End Function


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.


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


About the Author

Girish Chandra
Web Developer
India India
No Biography provided

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 
GeneralMy vote of 1 Pin
mohammad dianati22-Apr-13 22:41
Membermohammad dianati22-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.