Programmically pull data types for MS SQL table columns for later insertion

Question

Rails has an awesome way of looking up column names and expected datatypes from the DB, alleviating a lot of programming.

I'm trying to build something like this in C#.NET, because we have large tables that are ever changing. I'll be adding parameters like so:

SqlParameter param = new SqlParameter("parametername", *SqlDbType.Int*);
param.Direction = ParameterDirection.Input;
param.Value = 0;
comm.Parameters.Add(param);

Notice the SqlDbType. How can I get that? If I get DataColumns from the DataSet, all I can get is System types like System.string.

Answer 1

why not just let ADO.NET detect it automatically:

SqlParameter param = new SqlParameter("parametername", value);

'course, you don't actually need Direction, either:

comm.Parameters.Add(new SqlParameter("parametername",value));

I'm kind of a fan of doing things in one line :)

Answer 2

For our project we query the INFORMATION_SCHEMA tables before we build our SQL statements. If you stick the value in DATA_TYPE from INFORMATION_SCHEMA.COLUMNS into an Enum.Parse that should give you the correct value.