accessing column types without doing a query

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

Joe
Fri Jul 25 22:07:33 CDT 2003

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.
--
Joe Fallon



"headware" <headware@aol.com> wrote in message
news:e3f4b0ae.0307251021.2fdb83b5@posting.google.com...
> 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

Paul
Mon Jul 28 12:46:26 CDT 2003

On 25 Jul 2003 11:21:36 -0700, headware@aol.com (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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

davidsc
Mon Jul 28 13:30:45 CDT 2003


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.

headware
Mon Jul 28 23:29:36 CDT 2003

davidsc@online.microsoft.com (David Sceppa) wrote in message news:<zdPufZTVDHA.2000@cpmsftngxa06.phx.gbl>...
> 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.

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

davidsc
Tue Jul 29 12:59:10 CDT 2003

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"]);

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.

headware
Tue Jul 29 15:59:37 CDT 2003

Both good suggestions. Thanks for the help.

Dave

davidsc@online.microsoft.com (David Sceppa) wrote in message news:<GOfmgsfVDHA.2000@cpmsftngxa06.phx.gbl>...
> 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"]);
>
> 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.