Reading Database Schema

  • Thread starter Thread starter sourabh
  • Start date Start date
S

sourabh

Hi
I want to read a Database using ADO.Net and get the list of all Tables in it
and all the columns along with the Data Type of each from each table.
Using GetOleDbSchemaTable i am able to get the tables and columns but the
datatype is displayed as an integer. Any idea how i can get the Data Type
for the columns.
Here's what i am doing.

//Query for getting only the user tables

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[]{null,null,null,"TABLE"} );

//for each table get the columns

for( int index = 0 ; index < dt.Rows.Count ; index ++)

{

Console.WriteLine( "{0}", dt.Rows[index]["TABLE_NAME"].ToString() );//,
dt.Rows[index][0].ToString() , dt.Rows[index][1].ToString()
,dt.Rows[index][3].ToString() );

restricts = new object[] {null, null, dt.Rows[index]["TABLE_NAME"], null};

dbFields = conn.GetOleDbSchemaTable ( OleDbSchemaGuid.Columns, restricts);

for( int indexCol = 0 ; indexCol < dbFields.Rows.Count ; indexCol ++)

{

Console.Write( "{0},{1} |",
dbFields.Rows[indexCol]["COLUMN_NAME"].ToString() ,
dbFields.Rows[indexCol]["DATA_TYPE"].ToString() );

}

Console.WriteLine("\n");

}


}







Thanks

Sourabh
 
In VB.NET

Dim dc As DataColumn
For Each dc In dt.Columns
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType )
Next dc

C#
DataColumn dc;

foreach dc in dt.Columns{
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType);
}
-----Original Message-----
Hi
I want to read a Database using ADO.Net and get the list of all Tables in it
and all the columns along with the Data Type of each from each table.
Using GetOleDbSchemaTable i am able to get the tables and columns but the
datatype is displayed as an integer. Any idea how i can get the Data Type
for the columns.
Here's what i am doing.

//Query for getting only the user tables

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[]{null,null,null,"TABLE"} );

//for each table get the columns

for( int index = 0 ; index < dt.Rows.Count ; index ++)

{

Console.WriteLine( "{0}", dt.Rows[index]
Click to expand...
["TABLE_NAME"].ToString() );//,
dt.Rows[index][0].ToString() , dt.Rows[index][1].ToString ()
,dt.Rows[index][3].ToString() );

restricts = new object[] {null, null, dt.Rows[index] ["TABLE_NAME"], null};

dbFields = conn.GetOleDbSchemaTable (
Click to expand...
OleDbSchemaGuid.Columns, restricts);
for( int indexCol = 0 ; indexCol < dbFields.Rows.Count ; indexCol ++)

{

Console.Write( "{0},{1} |",
dbFields.Rows[indexCol]["COLUMN_NAME"].ToString() ,
dbFields.Rows[indexCol]["DATA_TYPE"].ToString() );

}

Console.WriteLine("\n");

}


}







Thanks

Sourabh


.
Click to expand...
 
Doing so would give me the name and data type of the columns of the
DataTable. But what I need is the DataType of the Column in the DataBase,
which we get here as Int. I'll try to make this little more clear.

First I get all the Tables form a DB. This is the first DataTable i have.
Say this DTTable.
Now, i loop thru each actual DataBase table in this DTTable. Effectively I
am reading each Row. That means Info about a table is in a Row.
So for each Actual DB Table ( i.e. a Row in DTTable) I get another DataTable
, say DTCol which is Collection of Columns in the DB Table.
I loop thru this new DataTable, DTCol, to get info about each Column in this
Table in the DB.
I get the Column name by doing
dbFields.Rows[indexCol]["COLUMN_NAME"]

and Column DataType by doing.
dbFields.Rows[indexCol]["DATA_TYPE"].
This is stored as Int in the DataTable. Actually this is the .Net Data Type
mapping of the SQL data type. And i need the SQL Data Type here.

Hope this makes the whole picture a little clear.





William Ryan said:
In VB.NET

Dim dc As DataColumn
For Each dc In dt.Columns
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType )
Next dc

C#
DataColumn dc;

foreach dc in dt.Columns{
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType);
}
-----Original Message-----
Hi
I want to read a Database using ADO.Net and get the list of all Tables in it
and all the columns along with the Data Type of each from each table.
Using GetOleDbSchemaTable i am able to get the tables and columns but the
datatype is displayed as an integer. Any idea how i can get the Data Type
for the columns.
Here's what i am doing.

//Query for getting only the user tables

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[]{null,null,null,"TABLE"} );

//for each table get the columns

for( int index = 0 ; index < dt.Rows.Count ; index ++)

{

Console.WriteLine( "{0}", dt.Rows[index]
Click to expand...
["TABLE_NAME"].ToString() );//,
dt.Rows[index][0].ToString() , dt.Rows[index][1].ToString ()
,dt.Rows[index][3].ToString() );

restricts = new object[] {null, null, dt.Rows[index] ["TABLE_NAME"], null};

dbFields = conn.GetOleDbSchemaTable (
Click to expand...
OleDbSchemaGuid.Columns, restricts);
for( int indexCol = 0 ; indexCol < dbFields.Rows.Count ; indexCol ++)

{

Console.Write( "{0},{1} |",
dbFields.Rows[indexCol]["COLUMN_NAME"].ToString() ,
dbFields.Rows[indexCol]["DATA_TYPE"].ToString() );

}

Console.WriteLine("\n");

}


}







Thanks

Sourabh


.
Click to expand...
Click to expand...
 
I got a link which talks about Column Schema
http://support.microsoft.com/default.aspx?scid=kb;EN-US;310107
But this does a query of type Select * from table. which i dont want to do.

sourabh said:
Doing so would give me the name and data type of the columns of the
DataTable. But what I need is the DataType of the Column in the DataBase,
which we get here as Int. I'll try to make this little more clear.

First I get all the Tables form a DB. This is the first DataTable i have.
Say this DTTable.
Now, i loop thru each actual DataBase table in this DTTable. Effectively I
am reading each Row. That means Info about a table is in a Row.
So for each Actual DB Table ( i.e. a Row in DTTable) I get another DataTable
, say DTCol which is Collection of Columns in the DB Table.
I loop thru this new DataTable, DTCol, to get info about each Column in this
Table in the DB.
I get the Column name by doing
dbFields.Rows[indexCol]["COLUMN_NAME"]

and Column DataType by doing.
dbFields.Rows[indexCol]["DATA_TYPE"].
This is stored as Int in the DataTable. Actually this is the .Net Data Type
mapping of the SQL data type. And i need the SQL Data Type here.

Hope this makes the whole picture a little clear.





William Ryan said:
In VB.NET

Dim dc As DataColumn
For Each dc In dt.Columns
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType )
Next dc

C#
DataColumn dc;

foreach dc in dt.Columns{
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType);
}
-----Original Message-----
Hi
I want to read a Database using ADO.Net and get the list of all Tables in it
and all the columns along with the Data Type of each from each table.
Using GetOleDbSchemaTable i am able to get the tables and columns but the
datatype is displayed as an integer. Any idea how i can get the Data Type
for the columns.
Here's what i am doing.

//Query for getting only the user tables

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[]{null,null,null,"TABLE"} );

//for each table get the columns

for( int index = 0 ; index < dt.Rows.Count ; index ++)

{

Console.WriteLine( "{0}", dt.Rows[index]
Click to expand...
["TABLE_NAME"].ToString() );//,
dt.Rows[index][0].ToString() , dt.Rows[index][1].ToString ()
,dt.Rows[index][3].ToString() );

restricts = new object[] {null, null, dt.Rows[index] ["TABLE_NAME"], null};

dbFields = conn.GetOleDbSchemaTable (
Click to expand...
OleDbSchemaGuid.Columns, restricts);
for( int indexCol = 0 ; indexCol < dbFields.Rows.Count ; indexCol ++)

{

Console.Write( "{0},{1} |",
dbFields.Rows[indexCol]["COLUMN_NAME"].ToString() ,
dbFields.Rows[indexCol]["DATA_TYPE"].ToString() );

}

Console.WriteLine("\n");

}


}







Thanks

Sourabh


.
Click to expand...
Click to expand...
Click to expand...
 
Look at the information schema views in BOL



sourabh said:
I got a link which talks about Column Schema
http://support.microsoft.com/default.aspx?scid=kb;EN-US;310107
But this does a query of type Select * from table. which i dont want to do.

sourabh said:
Doing so would give me the name and data type of the columns of the
DataTable. But what I need is the DataType of the Column in the DataBase,
which we get here as Int. I'll try to make this little more clear.

First I get all the Tables form a DB. This is the first DataTable i have.
Say this DTTable.
Now, i loop thru each actual DataBase table in this DTTable. Effectively I
am reading each Row. That means Info about a table is in a Row.
So for each Actual DB Table ( i.e. a Row in DTTable) I get another DataTable
, say DTCol which is Collection of Columns in the DB Table.
I loop thru this new DataTable, DTCol, to get info about each Column in this
Table in the DB.
I get the Column name by doing
dbFields.Rows[indexCol]["COLUMN_NAME"]

and Column DataType by doing.
dbFields.Rows[indexCol]["DATA_TYPE"].
This is stored as Int in the DataTable. Actually this is the .Net Data Type
mapping of the SQL data type. And i need the SQL Data Type here.

Hope this makes the whole picture a little clear.





William Ryan said:
In VB.NET

Dim dc As DataColumn
For Each dc In dt.Columns
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType )
Next dc

C#
DataColumn dc;

foreach dc in dt.Columns{
Debug.WriteLine(dc.ColumnName + " , " + dc.DataType);
}
-----Original Message-----
Hi
I want to read a Database using ADO.Net and get the list
of all Tables in it
and all the columns along with the Data Type of each
from each table.
Using GetOleDbSchemaTable i am able to get the tables
and columns but the
datatype is displayed as an integer. Any idea how i can
get the Data Type
for the columns.
Here's what i am doing.

//Query for getting only the user tables

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[]{null,null,null,"TABLE"} );

//for each table get the columns

for( int index = 0 ; index < dt.Rows.Count ; index ++)

{

Console.WriteLine( "{0}", dt.Rows[index]
["TABLE_NAME"].ToString() );//,
dt.Rows[index][0].ToString() , dt.Rows[index][1].ToString
()
,dt.Rows[index][3].ToString() );

restricts = new object[] {null, null, dt.Rows[index]
["TABLE_NAME"], null};

dbFields = conn.GetOleDbSchemaTable (
OleDbSchemaGuid.Columns, restricts);

for( int indexCol = 0 ; indexCol < dbFields.Rows.Count ;
indexCol ++)

{

Console.Write( "{0},{1} |",
dbFields.Rows[indexCol]["COLUMN_NAME"].ToString() ,
dbFields.Rows[indexCol]["DATA_TYPE"].ToString() );

}

Console.WriteLine("\n");

}


}







Thanks

Sourabh


.
Click to expand...
Click to expand...
Click to expand...
 
¤ Hi
¤ I want to read a Database using ADO.Net and get the list of all Tables in it
¤ and all the columns along with the Data Type of each from each table.
¤ Using GetOleDbSchemaTable i am able to get the tables and columns but the
¤ datatype is displayed as an integer. Any idea how i can get the Data Type
¤ for the columns.

It might help if you could identify the type of database you are working with?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
¤ I am working on SQL 2000
¤ ¤ > On Tue, 19 Aug 2003 19:12:25 -0400, "sourabh"
¤ >
¤ > ¤ Hi
¤ > ¤ I want to read a Database using ADO.Net and get the list of all Tables
¤ in it
¤ > ¤ and all the columns along with the Data Type of each from each table.
¤ > ¤ Using GetOleDbSchemaTable i am able to get the tables and columns but
¤ the
¤ > ¤ datatype is displayed as an integer. Any idea how i can get the Data
¤ Type
¤ > ¤ for the columns.
¤ >
¤ > It might help if you could identify the type of database you are working
¤ with?
¤ >
¤ >
¤ > Paul ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤

The value of the data type returned is equal to one of the values in the System.Data.OleDb.OleDbType
enum.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Thanks Paul,
Now I have a few more issues here, basically I compare 2 tables and figure
out there's one column missing in the second, so now want to copy this in
the second table. So a create a new column and set all the properties of
this new column and add this to the table. But now I need to copy all the
Relation ships on this also. I am using SQDMO for this. Is there any other
easier way to do this.


Thanks again
Sourabh
 
¤ Thanks Paul,
¤ Now I have a few more issues here, basically I compare 2 tables and figure
¤ out there's one column missing in the second, so now want to copy this in
¤ the second table. So a create a new column and set all the properties of
¤ this new column and add this to the table. But now I need to copy all the
¤ Relation ships on this also. I am using SQDMO for this. Is there any other
¤ easier way to do this.

I'm not a SQL Server expert but I have to think you can do this with SQL DDL (Data Definition
Language) using the ALTER and CONSTRAINT keywords.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top