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.

score 36 · Answer 1 · edited May 23 '17 at 12:03

36

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

edited May 23 '17 at 12:03

Community

1
1

answered Mar 15 '12 at 20:35

infografnet

3,749
1
35
35

3

Unfortunately, there is a note in SQL Server 2012 documentation to not use this feature. See http://msdn.microsoft.com/en-us/library/ms173839.aspx – Naomi Jul 11 '13 at 19:05
2

There's also just `reader.GetSchemaTable().Rows[x]["ProviderSpecificDataType"]` – Nacht Sep 21 '15 at 02:41
1

To avoid "SET FMTONLY ON" you can use "TOP 0" in the select statement – Piotr Palka Apr 02 '20 at 14:29
TOP 0 doesn't work for stored procedures you can't (and shouldn't) modify. The alternatives don't work for stored procedures that contain temp tables. You are better off getting all the values. But still using reader.GetSchemaTable()... – TamusJRoyce Jul 01 '22 at 03:29
to avoid changing query or plain using FMTONLY you can use `command.ExecuteReader(CommandBehavior.SchemaOnly).GetSchemaTable();` – mr R Aug 05 '22 at 08:08

score 11 · Accepted Answer · edited Sep 23 '18 at 04:12

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.

score 9 · Answer 3 · edited Oct 19 '21 at 03:14

9

SqlConnection SqlCon = new SqlConnection("Data Source=(local);Database=dbname;Integrated Security=SSPI;");

SqlCon.Open();

SqlCmd = SqlCon.CreateCommand();
SqlCmd.CommandText = "select * from Tablename";

SqlDataReader SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();

int i = 0;

while (i < SqlDr.FieldCount)
{ 
   MessageBox.Show(SqlDr.GetDataTypeName(i));
   i++;
}

edited Oct 19 '21 at 03:14

dyslexicanaboko

4,215
2
37
43

answered Oct 14 '12 at 16:03

Madhukar Krishna

91
1
1

Hello Madhukar and welcome to StackOverflow. Consider adding some verbal description what your code does. This will increase quality of your answer, better help others who will read it in the future, and give you more upvotes. See [How to Answer](http://stackoverflow.com/questions/how-to-answer) for more details. – Be Brave Be Like Ukraine Oct 14 '12 at 16:13
Using SqlDataReader.GetDataTypeName() returns the native SQL Server type. SqlDataReader.GetFieldType() returns the .NET Framework type. I've found the former useful when I've needed to build a T-SQL statement to alter database column types or sizes. – Paul J Mar 22 '13 at 22:00

score 4 · Answer 4 · edited May 30 '18 at 10:37

Another approach is to let SQL do the work for you:

SqlConnection rConn = connectToSQL(); //returns sql connection
SqlCommand SqlCmd = new SqlCommand();
SqlCmd = rConn.CreateCommand();
SqlCmd.CommandText = "SELECT ORDINAL_POSITION, " +
                         "COLUMN_NAME, " +
                         "DATA_TYPE, " +
                         "CHARACTER_MAXIMUM_LENGTH, " +
                         "IS_NULLABLE " +
                    "FROM INFORMATION_SCHEMA.COLUMNS " +
                    "WHERE TABLE_NAME = 'TableName'";
SqlDataReader SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();
while (SqlDr.Read()) { 
    var OrdPos = SqlDr.GetValue(0);
    var ColName = SqlDr.GetValue(1);
    var DataType = SqlDr.GetValue(2);
    var CharMaxLen = SqlDr.GetValue(3);
    var IsNullable = SqlDr.GetValue(4);
    Console.WriteLine("ColName - " + ColName + " DataType - " + DataType + " CharMaxLen - " + CharMaxLen);
}

This will just return the string version of the SqlDataTypes. The OP is looking for System.Data.SqlDbType enum. — JBond, Oct 04 '16 at 10:11

score 2 · Answer 5 · answered Sep 19 '11 at 11:48

As David says ... you are in .NET so the types will be .NET types. This is a listing of type mappings from SQL Server to .Net that shows you what .NET type you will end up with for a given Sql column type .. hope this helps ..

http://msdn.microsoft.com/en-us/library/ms131092.aspx

score 1 · Answer 6 · answered Apr 07 '18 at 16:15

Building upon Madhukar Krishna's answer, if you have a SQLDataReader or a MySQLDataReader object you can obtain the SQL type metadata for a given column (in the code, we obtain the metadata of column with index 1) using the following code (example working for MySQLDataReader object):

...
MySqlDataReader dr = ...
Console.WriteLine("dr.GetFieldType(1) = {0}, dr.GetName(1) = {1}, dr.GetValue(1) = {2}, dr.GetDataTypeName(1) = {3}", 
                          dr.GetFieldType(1), dr.GetName(1), dr.GetValue(1), dr.GetDataTypeName(1));
        bool b = Enum.TryParse(dr.GetDataTypeName(1), true, out System.Data.SqlDbType mySqlDbTypeEnum);
        Console.WriteLine("mySqlDbTypeEnum = {0}, b = {1}", mySqlDbTypeEnum, b);

The line:

bool b = Enum.TryParse(dr.GetDataTypeName(1), true, out System.Data.SqlDbType mySqlDbTypeEnum);

is used to obtain the System.Data.SqlDbType from a String, and ignoring the letter case, e.g. if dr.GetDataTypeName(1) returns "VARCHAR" then the System.Data.SqlDbType enum value is System.Data.SqlDbType.VarChar.

Then, you can get get the size of the data type (for instance VARCHAR(15)) by inspecting the SQL columns metadata with the following code (source MSDN):

... (continuation)
DataTable schemaTable;
// Retrieve column schema into a DataTable.
schemaTable = dr.GetSchemaTable();
// For each field in the table...
foreach (DataRow myField in schemaTable.Rows)
{
   // For each property of the field...
   foreach (DataColumn myProperty in schemaTable.Columns)
   {
      // Display the field name and value.
      Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
   }
   Console.WriteLine();
   // Pause.
   //Console.ReadLine();
}

The property ColumnSize gives the size information.

score 0 · Answer 7 · answered Aug 18 '15 at 21:41

0

If you are using DataReader -

SqlDataReader reader = cmd.ExecuteReader(); reader.GetDataTypeName(int ordinal)

should work if you want the SQL data type of a column

answered Aug 18 '15 at 21:41

ManJan

3,939
3
20
22

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

7 Answers7

Linked