Getting SQL data types using C#

[eaguilar]

Hello,

I'm trying to get column information directly by querying the tables on a
SQL Server DB and asking for name, uniqueness, DataType, etc. I'm using a
DataColumn object, called myDataColumn, and then I ask for:

myDataColumn.ColumnName
myDataColumn.DataType
myDataColumn.Unique
etc.

The problem is I'm getting .NET data types, instead of SQL data types, which
is too generic for what I need.

For instance, if a column is of nvarchar(50) type in sql server, my app in
c# reports it as being simply String.

Can anyone help me trying to get full SQL data type info from an app in c#?

Many thanks in advance.

 

[Jeff Johnson]

Can anyone help me trying to get full SQL data type info from an app in
c#?

Switch to a query window in SSMS (or Query Analyzer) and run this query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Take a look at its output. Now you just need to run that query from your
code and examine the results you get back. Of course, you can apply a WHERE
clause to filter out a specific table, etc.

 

[Jeroen Mostert]

I'm trying to get column information directly by querying the tables on a
SQL Server DB and asking for name, uniqueness, DataType, etc. I'm using a
DataColumn object, called myDataColumn, and then I ask for:

myDataColumn.ColumnName
myDataColumn.DataType
myDataColumn.Unique
etc.

The problem is I'm getting .NET data types, instead of SQL data types, which
is too generic for what I need.

Don't use DataColumn, ask for a schema table instead. Use
SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly |
CommandBehavior.KeyInfo).GetSchemaTable(). This will give you full metadata
for your query results in a convenient package.

 

[Jeff Johnson]

eaguilar wrote:

Don't use DataColumn, ask for a schema table instead. Use
SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly |
CommandBehavior.KeyInfo).GetSchemaTable(). This will give you full
metadata for your query results in a convenient package.

Heh, I can't believe I forgot about that, seeing as how I probably used it
no more than 3 months ago....

 

[eaguilar]

Awesome help! Thanks a lot to both of you!

Any ideas on how I can find out Primary Key info out of the set of columns I
get? Either using "ExecuteReader" or "INFORMATION_SCHEMA.COLUMNS", I get
everything except Primary Key info.

I'm guessing that type of info is implemented by the DBMS by constraints and
might not be available at this level.

Thanks in advance,

</edwin>

 

[Jeff Johnson]

Awesome help! Thanks a lot to both of you!

Any ideas on how I can find out Primary Key info out of the set of columns
I
get? Either using "ExecuteReader" or "INFORMATION_SCHEMA.COLUMNS", I get
everything except Primary Key info.

I'm guessing that type of info is implemented by the DBMS by constraints
and
might not be available at this level.

Going the INFORMATION_SCHEMA route for SQL Server 2005, a combination of

INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.KEY_COLUMN_USAGE