Getting SQL data types using C#

  • Thread starter Thread starter eaguilar
  • Start date Start date
E

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.
 
Can anyone help me trying to get full SQL data type info from an app in
c#?
Click to expand...

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.
 
eaguilar said:
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.
Click to expand...
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.
 
Jeroen Mostert said:
eaguilar wrote:
Click to expand...
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.
Click to expand...

Heh, I can't believe I forgot about that, seeing as how I probably used it
no more than 3 months ago....
 
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>
 
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.
Click to expand...

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
 
Back
Top