473,398 Members | 2,393 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,398 software developers and data experts.

GetSchema to retrieve column properties

Ken
I'm trying to run a loop to capture column property information from a
table in my datasource. Can anybody see where this is going wrong?

Dim tbl As New DataTable
Dim col As DataColumn
Dim x As Integer
Dim colName(99) As String
Dim colType(99) As String
cn.Open()
tbl = cn.GetSchema("Orders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName.ToString()
colType(x) = col.DataType.ToString()
Next
cn.Close()

If it's not obvious, my intended result are two variables for each
column from the original datatable that will capture a) the name of
the column, and b) the datatype of the column. I will then use these
variables to drive subsequent logic.

I'm getting an error on the GetSchema line: The requested collection
(Orders) is not defined.

I have little experience with the GetSchema method, so any advice on
how to accomplish this is appreciated.

Thx,
Ken

Apr 25 '07 #1
5 25502
What is your data source? Is it SQLServer?

Robin S.
-----------------------
"Ken" <fk****@yahoo.comwrote in message
news:11**********************@t38g2000prd.googlegr oups.com...
I'm trying to run a loop to capture column property information from a
table in my datasource. Can anybody see where this is going wrong?

Dim tbl As New DataTable
Dim col As DataColumn
Dim x As Integer
Dim colName(99) As String
Dim colType(99) As String
cn.Open()
tbl = cn.GetSchema("Orders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName.ToString()
colType(x) = col.DataType.ToString()
Next
cn.Close()

If it's not obvious, my intended result are two variables for each
column from the original datatable that will capture a) the name of
the column, and b) the datatype of the column. I will then use these
variables to drive subsequent logic.

I'm getting an error on the GetSchema line: The requested collection
(Orders) is not defined.

I have little experience with the GetSchema method, so any advice on
how to accomplish this is appreciated.

Thx,
Ken

Apr 25 '07 #2
Yes.

Apr 26 '07 #3

"Randy" <ra************@gmail.comwrote in message
news:11**********************@r30g2000prh.googlegr oups.com...
Yes.
Try this:
Here are all of the properties (and their types) you can retrieve for the
data columns using a DataReader.

col name = ColumnName, type = System.String
col name = ColumnOrdinal, type = System.Int32
col name = ColumnSize, type = System.Int32
col name = NumericPrecision, type = System.Int16
col name = NumericScale, type = System.Int16
col name = IsUnique, type = System.Boolean
col name = IsKey, type = System.Boolean
col name = BaseServerName, type = System.String
col name = BaseCatalogName, type = System.String
col name = BaseColumnName, type = System.String
col name = BaseSchemaName, type = System.String
col name = BaseTableName, type = System.String
col name = DataType, type = System.Type
col name = AllowDBNull, type = System.Boolean
col name = ProviderType, type = System.Int32
col name = IsAliased, type = System.Boolean
col name = IsExpression, type = System.Boolean
col name = IsIdentity, type = System.Boolean
col name = IsAutoIncrement, type = System.Boolean
col name = IsRowVersion, type = System.Boolean
col name = IsHidden, type = System.Boolean
col name = IsLong, type = System.Boolean
col name = IsReadOnly, type = System.Boolean
col name = ProviderSpecificDataType, type = System.Type
col name = DataTypeName, type = System.String
col name = XmlSchemaCollectionDatabase, type = System.String
col name = XmlSchemaCollectionOwningSchema, type = System.String
col name = XmlSchemaCollectionName, type = System.String
col name = UdtAssemblyQualifiedName, type = System.String
col name = NonVersionedProviderType, type = System.Int32

Here's how I got this list; tableName is passed in as a String. This shows
the columns you can get, and then shows selected values for each column
defined in the table.
Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'This shows all of the information you can access about each
column.
For Each col As DataColumn In tbl.Columns
Debug.Print("col name = " & col.ColumnName & _
", type = " & col.DataType.ToString)
Next
For Each row As DataRow In tbl.Rows
'DataTypeName actually gives the same
' data type name as is displayed in SQLServer
Debug.Print("{0}, ColumnSize = {1}, DataType = {2},
DataTypeName = {3}, IsExpression = {4} ", _
row("ColumnName"), row("ColumnSize"), row("DataType"), _
row("DataTypeName"), row("IsExpression"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try

I also know how to get a list of tables if you're interested.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
Apr 27 '07 #4
Hi,

Try following:-

'Retrieve Dataset Schema

Dim cn As New SqlConnection()

Dim cmd As New SqlCommand()

Dim schemaTable As DataTable

Dim myReader As SqlDataReader

'Dim myField As DataRow

'Dim myProperty As DataColumn

cn.connectionstring = My.Settings.AccDataConnectionString

cn.Open()

'Retrieve records from the Employees table into a DataReader.

cmd.Connection = cn

''I used 1=0 because I don't want to waste time in retrieving the records, just a Schema

cmd.CommandText = "SELECT * FROM Customers Where 1=0"

myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)

'Retrieve column schema into a DataTable.

schemaTable = myReader.GetSchemaTable()

''For each field in the table...

For Each myField In schemaTable.Rows

'For each property of the field...

For Each myProperty In schemaTable.Columns

' 'Display the field name and value.

Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())

Next

Console.WriteLine()

' 'Pause.

Console.ReadLine()

Next

Best Regards,

Luqman

"Ken" <fk****@yahoo.comwrote in message news:11**********************@t38g2000prd.googlegr oups.com...
I'm trying to run a loop to capture column property information from a
table in my datasource. Can anybody see where this is going wrong?

Dim tbl As New DataTable
Dim col As DataColumn
Dim x As Integer
Dim colName(99) As String
Dim colType(99) As String
cn.Open()
tbl = cn.GetSchema("Orders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName.ToString()
colType(x) = col.DataType.ToString()
Next
cn.Close()

If it's not obvious, my intended result are two variables for each
column from the original datatable that will capture a) the name of
the column, and b) the datatype of the column. I will then use these
variables to drive subsequent logic.

I'm getting an error on the GetSchema line: The requested collection
(Orders) is not defined.

I have little experience with the GetSchema method, so any advice on
how to accomplish this is appreciated.

Thx,
Ken
May 16 '07 #5
Or, as an alternative you can use the SqlDataAdapter.FillSchema method like
so:

cmd.CommandText = "SELECT * FROM Customers"
Dim schema As New DataTable
Dim adapter As New SqlDataAdapter(cmd)
adapter.FillSchema (schema, SchemaType.Source)

In fact, this is the method I use in a small code generation utility I wrote
(Warning C# code!):
private static List<FieldInfoGetTableFieldList ( string table,
string connectionString )
{
List<FieldInfofieldList = new List<FieldInfo();
string statement = string.Format ( "SELECT * FROM {0}", table );

using ( SqlConnection connection = new SqlConnection (
connectionString ) )
{
using ( SqlCommand command = new SqlCommand ( statement,
connection ) )
{
using ( SqlDataAdapter adapter = new SqlDataAdapter (
command ) )
{
DataTable schema = new DataTable ();
adapter.FillSchema ( schema, SchemaType.Source );
foreach ( DataColumn column in schema.Columns )
{
// TODO: Refactor FieldInfo to accept a
DataColumn as a parameter in it's constructor
fieldList.Add ( new FieldInfo ( table,
column.ColumnName, column.DataType, column.AllowDBNull, column.Ordinal ) );
}
}
}
}

fieldList.Sort ( FieldInfoComparer.Instance );
return fieldList;
}

I do this for Stored Procs:

private static List<FieldInfoGetProcedureFieldList ( string
procedure, string connectionString )
{
List<FieldInfofieldList = new List<FieldInfo();
using ( SqlConnection connection = new SqlConnection (
connectionString ) )
{
using ( SqlCommand command = new SqlCommand ( procedure,
connection ) )
{
command.CommandType = CommandType.StoredProcedure;
using ( SqlDataAdapter adapter = new SqlDataAdapter (
command ) )
{
command.Connection.Open ();
SqlCommandBuilder.DeriveParameters ( command );
DataTable schema = new DataTable ();
adapter.FillSchema ( schema, SchemaType.Source );
foreach ( DataColumn column in schema.Columns )
{
// TODO: Refactor FieldInfo to accept a
DataColumn as a parameter in it's constructor
fieldList.Add ( new FieldInfo ( procedure,
column.ColumnName, column.DataType, column.AllowDBNull, column.Ordinal ) );
}
}
}
}

fieldList.Sort ( FieldInfoComparer.Instance );
return fieldList;
}

--
Tom Shelton

"Luqman" <pe*******@cyber.net.pkwrote in message
news:ep**************@TK2MSFTNGP03.phx.gbl...
Hi,

Try following:-

'Retrieve Dataset Schema
Dim cn As New SqlConnection()
Dim cmd As New SqlCommand()
Dim schemaTable As DataTable
Dim myReader As SqlDataReader
'Dim myField As DataRow
'Dim myProperty As DataColumn
cn.connectionstring = My.Settings.AccDataConnectionString
cn.Open()
'Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn
''I used 1=0 because I don't want to waste time in retrieving the records,
just a Schema
cmd.CommandText = "SELECT * FROM Customers Where 1=0"
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
'Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable()
''For each field in the table...
For Each myField In schemaTable.Rows
'For each property of the field...
For Each myProperty In schemaTable.Columns
' 'Display the field name and value.
Console.WriteLine(myProperty.ColumnName & " = " &
myField(myProperty).ToString())
Next
Console.WriteLine()
' 'Pause.
Console.ReadLine()
Next
Best Regards,
Luqman
"Ken" <fk****@yahoo.comwrote in message
news:11**********************@t38g2000prd.googlegr oups.com...
I'm trying to run a loop to capture column property information from a
table in my datasource. Can anybody see where this is going wrong?

Dim tbl As New DataTable
Dim col As DataColumn
Dim x As Integer
Dim colName(99) As String
Dim colType(99) As String
cn.Open()
tbl = cn.GetSchema("Orders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName.ToString()
colType(x) = col.DataType.ToString()
Next
cn.Close()

If it's not obvious, my intended result are two variables for each
column from the original datatable that will capture a) the name of
the column, and b) the datatype of the column. I will then use these
variables to drive subsequent logic.

I'm getting an error on the GetSchema line: The requested collection
(Orders) is not defined.

I have little experience with the GetSchema method, so any advice on
how to accomplish this is appreciated.

Thx,
Ken
May 17 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Dimitris Katsikas | last post by:
Hi everybody, I am trying to find a way to export automatically an entire database schema into an .xsd A graphical way is to drag & drop the tables from Server Connection into an empty .xsd...
1
by: Leepe | last post by:
example: I have a skill object that implements the XmlSerializable interface Implementing the getschema gives me some unwanted generations towards the wsdl.exe Reference.cs generation. skill is...
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
1
by: nathan.gomez | last post by:
Thanks for your time. I've been trying to get a list of databases available on a mySQL server (local) using the OdbcConnection.GetSchema() function. Thus far I have only been able to get...
0
by: Hexman | last post by:
Hello All, I am wanting to make a small utility that allows the user (me) to select a local database (currently Access 2000), build a list of non-system tables, then select a table which will...
1
by: Mel | last post by:
Anyone know how I would retrieve the MaxLength property of a column in my Access Database table? I know how to retrieve table data, for example the "Quote #" field in my example code below, but I...
0
by: NETCODE | last post by:
I am developing an add-in. I need to retrieve the path of .msi file, located in under project properties, from the deployment project (setup). I was able retieve all project properties of all...
0
by: Sebastijan | last post by:
Hi I am interested in DbConnection.GetSchema method of ODP.NET oracle library. When I call (to get primary keys metadata) DataTable pkeys = DbConnection.GetSchema("PrimaryKeys"... foreach...
3
by: Jaydeep Ramavat | last post by:
Dear Friends, how can we get the default value of the columns using getschema functionality. is there any alternative is available? Regards Jay.
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.