CODEWITHSUNDEEP
c#datatablesqldatatypessql-view
steventnorris
steventnorris

Reputation: 5876

Get DataType from Column Name

I need to get the data type of a column using the column name. I am coding in C#. The situation is I have a view which converts some dates into varchar so I can better display them (without time to be exact). However, that means the data type for the columns are now incorrect. What I'd like to do is look up a column from a DataTable schema using the column name and then get the datatype of that column. I'd be gathering the actual data type from the main table rather than the view in this case. Ideas?

Upvotes: 1

Views: 9770

Answers (4)

Stefanos Kargas
Stefanos Kargas

Reputation: 11053

information_schema.columns table gives more clear information for what you need than sys.columns table. You could try the following:

SELECT data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'tableName' AND column_name = 'columnName'

Upvotes: 1

Saurabh R S
Saurabh R S

Reputation: 3177

If you are using SQL Server you can make use of SET FMTONLY. It returns only metadata to the client. It can be used to test the format of the response without actually running the query.

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

Courtesy: StackOverflow

Upvotes: 0

kol
kol

Reputation: 28678

The DataTable.Columns property is a DataColumnCollection, which can be indexed by column name to get a DataColumn, which has a DataType property.

Upvotes: 4

Mr. TA
Mr. TA

Reputation: 5359

I don't see how that's possible without inferring the type from the values. If you have the results from the view and the column name in the code, the information about the table that the view uses is lost at that point.

You can, of course, get the information about the table if you know its name; that can be accomplished either using a simple query like that:

SELECT TOP 0 * FROM <<tableName>>

or using DMVs:

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.

Upvotes: 0

Related Questions