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

For SQL Server, use the SMO (SQL Server Management Objects).

http://www.yukonxml.com/articles/smo/

For example, you can use this code to traverse over all of the columns of a table.

Server server = new Server();
Database database = new Database( "MyDB" );
Table table = new Table( database, "MyTable" );

foreach ( Column column in table.Columns )
{
        WriteLine( column.Name ); 
}

Here are all of the column properties available to you: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column_members.aspx

Answer 2

What is the context for this? It makes a huge difference. For example, if you're already reading records from the db you can get the information from that result set, but the method varies if you have a datareader vs a dataset/table vs a dataview. If you have to go out and make a fresh request there are a number of different ways to do it, and the best choice will depend on the context.

Answer 3

If you are eventually going to read the data, you can do this:

SqlCommand comm = new SqlCommand("SELECT * FROM Products", connection);
using (SqlDataReader reader = comm.ExecuteReader())
{
    while (reader.Read())
    {
        Type type = reader.GetSqlValue(0).GetType();
        // OR Type type = reader.GetSqlValue("name").GetType();
        // yields type "System.Data.SqlTypes.SqlInt32"
    }
}