How to derive SqlDbType from column data type

  • Thread starter Thread starter Vicenç Masanas
  • Start date Start date
V

Vicenç Masanas

I need to know the correct SqlDbType of a column to use it in a dinamically
build parametrized sqlCommand.

As I can see when I build a parameter it expects a System.Data.SqlDbType but
if I look at the DataType property of a column in a table in a dataset I get
a diferent 'type' of data.

Is there a way to derive the SqlDbType from the dataset.datatable.column
datatype?

Vicenc
 
Technically, you do not need to supply the SqlDbType Param or the Size
param. What you are trying to do is exactly what will happen under the
covers if you only supply the param name and value.
 
Vicenç Masanas said:
I need to know the correct SqlDbType of a column to use it in a dinamically
build parametrized sqlCommand.

As I can see when I build a parameter it expects a System.Data.SqlDbType but
if I look at the DataType property of a column in a table in a dataset I get
a diferent 'type' of data.

Is there a way to derive the SqlDbType from the dataset.datatable.column
datatype?
Click to expand...
Yes. The SqlParameter class has a mapping between SqlDbTypes and framework
types.

To invoke the mapping, just create a SqlParameter and set it's value to an
instance of the type in question, and then examine the
SqlParameter.SqlDbType. Like this:

Public Function GetSqlDbType(ByVal frameworkType As Type) As SqlDbType
Dim p As New SqlClient.SqlParameter
p.Value = Activator.CreateInstance(frameworkType)
Return p.SqlDbType
End Function

David
 
It seems to be ok, but when it finds a String column the CreateInstance
fails because there is no constructor without parameters.
 
What I finally did is:
p = New SqlParameter(columna,value)

p.SourceColumn = c.ColumnName

p.Value = Nothing

cmmd.Parameters.Add(p)

And for now it seems to work. May there is a better way...
 
You should be able to leave out the p.Value = Nothing call. Truly, all
you need is the param name and the value. Ideally you should pass the
type and size, but again the paramater type is 'smart enough' to do this
for you. If your data is prety basic, strings, integers, etc then i
would simply pass the name and value. The Value can be the source
column.

(@myParameter, myColumn.Item.Tostring)
 
Back
Top