accessing column types without doing a query

  • Thread starter Thread starter headware
  • Start date Start date
H

headware

Is there a convinient way to access column types without having to
actually perform a query? I know about the GetOleDbSchemaTable()
function but I don't see anything about column types there.

Thanks,
Dave
 
David Sceppa's book ADO.Net contains a sample app for returning all of this
data in a convenient layout.
Includes the code needed to build the tables in your client app.

I thought it used GetOleDbSchemaTable but I could be wrong.
 
On 25 Jul 2003 11:21:36 -0700, (e-mail address removed) (headware) wrote:

¤ Is there a convinient way to access column types without having to
¤ actually perform a query? I know about the GetOleDbSchemaTable()
¤ function but I don't see anything about column types there.
¤

Yes, GetOleDbSchemaTable will do this:

Sub ListTableSchema()

Dim AccessConnection As New System.Data.OleDb.OleDbConnection()
Dim SchemaTable As DataTable

AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"
AccessConnection.Open()

'Retrieve schema information about Table1.
SchemaTable =
AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table1"})

DataGrid1.DataSource = SchemaTable

AccessConnection.Close()

End Sub

You will need to match up DATA_TYPE with values in the System.Data.OleDb.OleDbType enum.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Each DataReader object exposes a GetSchemaTable method that
returns a DataTable of schema information about the resultset.
Each row in the schema table corresponds to a column in the
resultset. You can use this schema information to get column
names, data types, etc. The .NET data type (System.String) is
available in the DataType column and the .NET provider-specific
data type (OleDbType.VarWChar) is available in the ProviderType
column, though the data is returned as simply an integer.

You can call Command.ExecuteQuery and ask for schema
information without actually executing the query by supplying a
CommandBehavior of SchemaOnly.

Supplying a CommandBehavior of KeyInfo will include
additional schema information such as base column and table
names, key column information, etc.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Each DataReader object exposes a GetSchemaTable method that
returns a DataTable of schema information about the resultset.
Each row in the schema table corresponds to a column in the
resultset. You can use this schema information to get column
names, data types, etc. The .NET data type (System.String) is
available in the DataType column and the .NET provider-specific
data type (OleDbType.VarWChar) is available in the ProviderType
column, though the data is returned as simply an integer.

You can call Command.ExecuteQuery and ask for schema
information without actually executing the query by supplying a
CommandBehavior of SchemaOnly.

Supplying a CommandBehavior of KeyInfo will include
additional schema information such as base column and table
names, key column information, etc.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
Click to expand...

The only problem I found with using the
OleDbDataReader.GetSchemaTable() method is that you can't find the
type for a specific field by name like this:

DataTable schemTbl = dataReader.GetSchemaTable();
DataRow row = schemTbl.Rows["age"]; //can't do this
Console.WriteLine(row["DataType"]);

you have to use the integer index of the row since you can't index the
Rows collection with a string, so you end up having to do this:

DataTable schemTbl = dataReader.GetSchemaTable();
DataRow row = schemTbl.Rows[0]; //can't use the column name here
Console.WriteLine(row["DataType"]);

and I really would like to be able to get the field type based on the
field name and not on its index in the table. Fortunately, while
looking into your suggestion, I found that I can get the schema info I
want from the OleDbDataReader object itself by using the
GetFieldType() and GetOrdinal() methods like this:

Console.WriteLine(dr.GetFieldType(dr.GetOrdinal("age")));

It's too bad that they decided to provide so many ways of getting
schema info (through GetSchemaTable(), GetOleDbSchemaTable(), and the
GetFieldType() methods to name a few) because it gets a little
confusing. Let me know if I'm wrong about not being able to use the
field name with the GetSchemaTable() function.

Dave
 
Dave,
The only problem I found with using the
OleDbDataReader.GetSchemaTable() method is that you can't
find the type for a specific field by name like this:

DataTable schemTbl = dataReader.GetSchemaTable();
DataRow row = schemTbl.Rows["age"]; //can't do this
Console.WriteLine(row["DataType"]);
Click to expand...

Actually, with a slight change to the code, there are a
couple different ways you can do this.

You can locate a row in the schema table based on the
resultset's column name by setting the schema table's primary key:

schemTbl.PrimaryKey = new DataColumn[]
{schemTbl.Columns["ColumnName"]};
DataRow row = schemTbl.Rows.Find(strColumnName);
Console.WriteLine(row["DataType"]);


Or you could ask the DataReader for a column's ordinal in
the resultset, and use that information to locate the row in the
schema table:

int intColumnIndex = dataReader.GetOrdinal(strColumnName);
DataRow row = schemTbl.Rows[intColumnIndex];
Console.WriteLine(row["DataType"]);


David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Both good suggestions. Thanks for the help.

Dave

Dave,
The only problem I found with using the
OleDbDataReader.GetSchemaTable() method is that you can't
find the type for a specific field by name like this:

DataTable schemTbl = dataReader.GetSchemaTable();
DataRow row = schemTbl.Rows["age"]; //can't do this
Console.WriteLine(row["DataType"]);
Click to expand...

Actually, with a slight change to the code, there are a
couple different ways you can do this.

You can locate a row in the schema table based on the
resultset's column name by setting the schema table's primary key:

schemTbl.PrimaryKey = new DataColumn[]
{schemTbl.Columns["ColumnName"]};
DataRow row = schemTbl.Rows.Find(strColumnName);
Console.WriteLine(row["DataType"]);


Or you could ask the DataReader for a column's ordinal in
the resultset, and use that information to locate the row in the
schema table:

int intColumnIndex = dataReader.GetOrdinal(strColumnName);
DataRow row = schemTbl.Rows[intColumnIndex];
Console.WriteLine(row["DataType"]);


David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
Click to expand...
 
Back
Top