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
p1.DbType = tc.ConvertFrom(theType.Name)
Catch ex As Exception
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
.