How to get the SqlType of a column in a DataTable?

DarthRoman picture DarthRoman · Sep 19, 2011 · Viewed 40.1k times · Source

I have a DataTable obtained from a SQL DataBase, like this:

using (SqlCommand cmd = new SqlCommand(query, _sqlserverDB))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        DataSet dataSet = new DataSet();
        adapter.Fill(dataSet);
        result = (dataSet != null && dataSet.Tables != null && dataSet.Tables.Count > 0) ? dataSet.Tables[0] : null;
    }
}

When I try to get the DataType of each column through dataColumn.DataType , I get the C# types (Int32, Int64, String, etc).

QUESTION: How can I access the native SQL data types (varchar, nvarchar, bigint...) instead of the C# types?

I have tried dataColumn.DataType.UnderlyingSystemType and the result is the same.

Answer

infografnet picture infografnet · Mar 15, 2012

Of course it is possible to take SqlDbType of a column, the answer is here on SO: link.

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