Read database column properties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a C# WinApp how do I display column properties?
For example, I want to display from pubs.employee all columns and properties
ColumnName DataType Length Precision Scale Default Value Collation
emp_id char 9 0 0
fname varchar 20 0 0
hire_date datetime 8 0 0 (getdate())

Probably very simple but have not found any examples.
 
you need to read the DB schema - plenty examples on goolge

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director
 
mwn said:
In a C# WinApp how do I display column properties?
For example, I want to display from pubs.employee all columns and
properties
ColumnName DataType Length Precision Scale Default Value Collation
emp_id char 9 0 0
fname varchar 20 0 0
hire_date datetime 8 0 0 (getdate())

Probably very simple but have not found any examples.
Click to expand...

OleDbConnection cn = new OleDbConnection(myConnectionString);
DataTable dtColumn;
cn.Open();

All columns, all tables
dtColumn = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]
{null, null, null, null});

All coluns, table "Tabe1"
dtColumn = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]
{null, null, "Tabe1", null});

Column "myCol", All Tables
dtColumn = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new Object[]
{null, null, null, "myCol"});
 
mwn said:
In a C# WinApp how do I display column properties?
Click to expand...

If this is SQL Server, you can retrieve metadata with a query:

SELECT column_name, data_type, column_default,
character_maximum_length
FROM information_schema.columns
WHERE table_name = 'SomeTable'

P.
 
P

This is helpful in one sense. Using this in a Stored Procedure would be neat.
However, I was seeking a generic solution that would work with Informix,
Oracle, MSSQL and other ODBC sources. All the many GetOleDbSchema examples
(simple) seem to get a far as the column name but never go that final step to
reveal the likes of a column's max length, scale, precision and other
attributes.

Thank you for taking the time to offer a solution.
 
SOLUTION FOUND
Essence of it is:
OleDbDataReader reader = comm.ExecuteReader (CommandBehavior.SchemaOnly);
 
Back
Top