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.

score 19 · Accepted Answer · edited May 30 '18 at 14:11

19

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"];

edited May 30 '18 at 14:11

Mitch Wheat

295,962
43
465
541

answered Jan 20 '09 at 00:30

Adam Ruth

3,575
1
21
20

This is really cool, but is it multi-thread safe? Is it possible that it gets interrupted with SET FMTONLY ON, and somebody else gets an empty recordset? – Karl Wenzel Apr 17 '13 at 15:57
1

This looks like a nice solution, but there is a warning to not use this feature as it's going to be removed in the future versions of SQL Server – Naomi Jul 11 '13 at 19:06
I didn't know this, honestly I have just always used SELECT TOP 0 * FROM Table. Good to know though. – dyslexicanaboko Oct 19 '21 at 03:09
to avoid changing query or plain using FMTONLY you can use `command.ExecuteReader(CommandBehavior.SchemaOnly).GetSchemaTable();` – mr R Aug 05 '22 at 08:35

score 3 · Answer 2 · answered Mar 27 '16 at 17:52

You can use enum System.Data.CommandBehavior as paramter for method SqlCommand.ExecuteReader(System.Data.CommandBehavior.KeyInfo):

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "select column from table";
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[0]["ProviderType"];

This example looks like the example with using sql: SET FMTONLY ON; SET FMTONLY OFF. But you haven't to use SET FMTONLY. And in this case you don't
receive data from the table. You receive only metadata.

score 2 · Answer 3 · answered Feb 13 '15 at 15:26

Old question, but if all you are trying to do is get from the string DATA_TYPE to the SqlDbType enum, the query presented in the original question combined with one line of code will do the trick:

string dataType = "nvarchar"; // result of the query in the original question
var sqlType = (SqlDbType)Enum.Parse(typeof(SqlDbType), dataType, true);

score 1 · Answer 4 · answered Jan 19 '09 at 23:23

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

Good answer, but as I don't know if the executing machine will have SMO installed, it's a non-starter I'm afraid. — WOPR, Jan 19 '09 at 23:28

score 1 · Answer 5 · answered Jan 20 '09 at 01:20

1

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"
    }
}

answered Jan 20 '09 at 01:20

Gorkem Pacaci

1,741
1
11
9

Please don't promote "SELECT *". – Micah Epps Dec 10 '19 at 13:59
@MicahEpps is that bad? – Gorkem Pacaci Apr 06 '20 at 16:18

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

5 Answers5

Linked