Clever Geek Handbook

Programmatically extract data types for MS SQL table columns for later insertion

Rails has an awesome way of finding column names and expected data types from the database, making a lot of programming easier.

I am trying to create something like this in C # .NET because we have large tables that are changing. I will add parameters like this:

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

Pay attention to the SqlDbType. How can I get it? If I get DataColumns from a DataSet, all I can get are system types like System.string.

+1


source to share


2 answers


why not just enable ADO.NET automatically:

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

', you don't really need Direction, either:



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

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

+2


source


For our project, we query tables INFORMATION_SCHEMA

before we build our SQL queries. If you enter a value DATA_TYPE

from INFORMATION_SCHEMA.COLUMNS

to Enum.Parse

that should give you the correct value.



+1


source






More articles:


All Articles