Populate ParameterCollection w/ sp_columns (or INFORMATION_SCHEMA.Columns) Results

Question

I'd like to build a ParameterCollection object based on the results of either execute sp_columns MyTableName or SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'MyTableName'.

The problem I'm having is assigning the appropriate data type to each Parameter ... I'm unsure of how to gather that information from either of the above two queries and convert it into a System.Data.DbType or System.TypeCode.

Any help is greatly appreciated.

Links: MSDN: sp_columns, MSDN: Information Schema.Columns

Edit: I guess what I'm looking for is functionality similar to Type.GetType("TypeName") that would accept a SQL data type. For example, DbType.GetType("int") or DbType.GetType("varchar").

Edit: Links from Answer: MSDN: Enum.Parse Method

Answer 1

It's actually really easy to do :-) SqlDbType is an enum, and there's a nice static function on the Enum class which accomplishes just what your looking for:

 private  SqlDbType ConvertType(string typeName)
 {
      return (SqlDbType)Enum.Parse(typeof(SqlDbType), typeName, true);
 }

With this, you should have no trouble converting the output from your INFORMATION_SCHEMA query into a collection of SqlParameters.

Cheers! Marc