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

datatable check column type
c datatable add column
convert datatable column to string c#
get type datatable c#
c# datatable change column format
c# get sqldbtype of column
c# get column datatype
sqldbtype numeric

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.

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.

SqlTypes and the DataSet, NET 2.0 introduced enhanced type support for the DataSet through the System.​Data. SqlTypes in the definition of a DataColumn eliminates the loss of "​SELECT TOP 5 SalesOrderID, UnitPrice, LineTotal, ModifiedDate " +  SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('<<tableName>>') In the first case, you use GetSchemaTable on the IDataReader and iterate over the rows to find your column by name; in the second case, you can iterate over the result set itself and find your column that way.

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

DataColumn.DataType Property (System.Data), We can pass the DataTable to the Stored Procedure using ADO. We need to provide SqlType rather then DbType. PhoneTable = new DataTable();; // Adding Columns; DataColumn COLUMN=new DataColumn();; COLUMN. I hope you have learned how to pass a table to a Stored Procedure as a  Before I get too far into what will probably take all day and be a huge pain to fully test, I'd like to call out to the SO community to see if anyone else has already written or found something in C# to accomplish this seemingly common and assuredly tedious task.

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

SqlDataReader SqlDr;

SqlCon.Open();
SqlCmd = SqlCon.CreateCommand();

SqlCmd.CommandText = "select * from Tablename";

SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();
int i=0;
while (i < SqlDr.FieldCount)
{ MessageBox.Show(SqlDr.GetDataTypeName(i)); i++;}'

Passing DataTable to StoredProcedure as Parameter in C#, StateChangeEventHandler BeginLoadData ( ) : DataTable BeginTransaction OleDbType , SqlDbType Binary _ Literal : OleDbLiteral BinarySort : SqlCompareOptions Check _ Constraints : OleDbSchemaGuid Columns : DataTable  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

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);
}

ADO.NET in a Nutshell, solution (continued) else sqlType = "[nvarchar] (" + maxLength + ")"; break; case It might be more appropriate in your situation to check if the table already The code iterates over the collection of the columns in the DataTable schema to  Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server To get the Table in information in SQL Server, we can use below Query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users' ORDER BY ORDINAL_POSITION

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

ADO.NET 3.5 Cookbook: Building Data-Centric .NET Applications, You get the information about the columns using the GetSchemaTable() method The GetSchemaTable() method returns a DataTable object with columns that  The columns in the your new row all contain an object of type System.DBNull, not of type System.Boolean or whatever. The right way to check the data type of the column is to check the DataColumn - that's the schema for the column - rather than the value of that column in the current DataRow.

Mastering C# Database Programming, Getting an Identity Column Value from SQL Server Problem When you add a row into a SQL Server You need to retrieve the new value to keep the DataTable synchronized with the database. Add(CATEGORYNAME_PARM, SqlDbType. Note. A column of data type Byte[] requires special treatment in certain cases since, unlike the base .NET Framework data types, it is a reference data type. If a column of data type Byte[] is used as a PrimaryKey, or as a Sort or RowFilter key for a DataView, any change to the column value must involve assigning the Byte[] column value to a separately instantiated Byte[] object.

4.2. Getting an Identity Column Value from SQL Server, You want to retrieve the default values of columns in a SQL Server table. Solution AppSettings["Sql_ConnectString"]); DataTable ordersTable = new  I need to iterate the columnname and column datatype from a specific row. All of the examples I have seen have iterated an entire datatable. I want to pass a single row to a function to do a bunch of conditional processing. I want to separate the conditional processing for ease of readability. This is what I have:

Retrieving Column Default Values from SQL Server, Data namespace-specifically DataColumn, DataRow, and Data Table. These classes allow An obvious question at this point is "How do I get the DataSet?" Under the corresponding CLR data types, the SqlTypes are optimized to work with  Yep, the column isn't in the DOM so you can't use jQuery to access those values. A hidden column is still available through the API so you can use the data functions to get the values back - there's a few based on table/column/row/cell - i.e.data() / column().data() / row().data() / cell().data() Cheers, Colin

Comments
  • Unfortunately, there is a note in SQL Server 2012 documentation to not use this feature. See msdn.microsoft.com/en-us/library/ms173839.aspx
  • There's also just reader.GetSchemaTable().Rows[x]["ProviderSpecificDataType"]
  • To avoid "SET FMTONLY ON" you can use "TOP 0" in the select statement
  • 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 for more details.
  • 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.
  • This will just return the string version of the SqlDataTypes. The OP is looking for System.Data.SqlDbType enum.