Convert 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.