Click here to Skip to main content
15,868,164 members
Articles / Programming Languages / SQL

Convert System.Type to SqlDbType

Rate me:
Please Sign up or sign in to vote.
3.20/5 (13 votes)
8 Dec 2006CPOL 99.4K   24   13
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:

VB
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)


Written By
Web Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Rungta Atul25-Feb-16 23:40
Rungta Atul25-Feb-16 23:40 
SuggestionC# Version Pin
Rungta Atul25-Feb-16 23:39
Rungta Atul25-Feb-16 23:39 
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}*@
SuggestionHybrid Solution Pin
Komron Nouri12-Aug-14 14:57
Komron Nouri12-Aug-14 14:57 
GeneralMy vote of 1 Pin
mohammad dianati22-Apr-13 22:41
mohammad dianati22-Apr-13 22:41 
GeneralMy vote of 2 Pin
dojohansen9-Nov-09 2:35
dojohansen9-Nov-09 2:35 
QuestionI cannot use this method on Compact Framework, Please Help Me Pin
rizaSHSHKA23-May-09 1:17
rizaSHSHKA23-May-09 1:17 
AnswerRe: I cannot use this method on Compact Framework, Please Help Me Pin
donkhuan22-Dec-09 7:33
donkhuan22-Dec-09 7:33 
GeneralC# version of the code Pin
mcsjr19-Aug-08 5:58
mcsjr19-Aug-08 5:58 
AnswerTry SqlMetaData Pin
TheMega5-May-08 0:03
TheMega5-May-08 0:03 
GeneralI need to Convert from DbType to .NET Type Pin
AIbanezW20-Jan-08 20:22
AIbanezW20-Jan-08 20:22 
QuestionWhy throw out the exception? Pin
varnk21-May-07 10:52
varnk21-May-07 10:52 
Generaldoesn't work with byte array Pin
pindurav18-Feb-07 20:34
pindurav18-Feb-07 20:34 
QuestionRe: doesn't work with byte array [modified] Pin
TheMega29-Apr-08 22:24
TheMega29-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.