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

Question

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 1

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

Answer 2

You cannot because System.Data.DataTable (or DataColumn, or DataSet, or DataRow...) is a generic .NET data container which works the same way regardless on the specific database engine you loaded your data from.

this means that provided you used a .NET Connector for SQL Server, MySQL, Access, PostgreSQL or anything else, the DataTable and DataColumn classes are always the same and being ADO.NET objects are generic to work with any db engine, so the columns are typed with the .NET types as you have found out.