How do I get the SqlDbType of a column in a table using ADO.NET?

Question

I'm trying to determine at runtime what the SqlDbType of a sql server table column is.

is there a class that can do that in System.Data.SqlClient or should I do the mapping myself? I can get a string representation back from

SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_CATALOG = '{0}' AND TABLE_SCHEMA = '{1}' 
   AND TABLE_NAME = '{2}' AND COLUMN_NAME = '{3}'

EDIT: I can't use SMO as I have no control over the executing machine so I can't guarantee it will be installed. (Sorry for not making that clear rp).

EDIT: In answer to Joel, I'm trying to make a function that I can call that will return me a SqlDBType when passed a SqlConnection, a table name, and a column name.

Answer 1

In SQL Server you can use the FMTONLY option. It allows you to run a query without getting any data, just returning the columns.

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SET FMTONLY ON; select column from table; SET FMTONLY OFF";
SqlDataReader reader = cmd.ExecuteReader();
SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[0]["ProviderType"];