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("O rders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName. ToString()
colType(x) = col.DataType.To String()
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

[RobinS]

What is your data source? Is it SQLServer?

Robin S.
-----------------------
"Ken" <fk****@yahoo.c omwrote in message
news:11******** **************@ t38g2000prd.goo glegroups.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("O rders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName. ToString()
colType(x) = col.DataType.To String()
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

[Randy]

Yes.

[RobinS]

"Randy" <ra************ @gmail.comwrote in message
news:11******** **************@ r30g2000prh.goo glegroups.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 = NumericPrecisio n, 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 = ProviderSpecifi cDataType, type = System.Type
col name = DataTypeName, type = System.String
col name = XmlSchemaCollec tionDatabase, type = System.String
col name = XmlSchemaCollec tionOwningSchem a, type = System.String
col name = XmlSchemaCollec tionName, type = System.String
col name = UdtAssemblyQual ifiedName, type = System.String
col name = NonVersionedPro viderType, 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(M y.Settings.DBCo nnString)
'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(SQLS tring, cn)
Dim rdr As SqlDataReader =
cmd.ExecuteRead er(CommandBehav ior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTa ble
'This shows all of the information you can access about each
column.
For Each col As DataColumn In tbl.Columns
Debug.Print("co l name = " & col.ColumnName & _
", type = " & col.DataType.To String)
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("DataTypeNa me"), row("IsExpressi on"))
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.

[Luqman]

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.connectionst ring = My.Settings.Acc DataConnectionS tring

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.ExecuteRead er(CommandBehav ior.KeyInfo)

'Retrieve column schema into a DataTable.

schemaTable = myReader.GetSch emaTable()

''For each field in the table...

For Each myField In schemaTable.Row s

'For each property of the field...

For Each myProperty In schemaTable.Col umns

' 'Display the field name and value.

Console.WriteLi ne(myProperty.C olumnName & " = " & myField(myPrope rty).ToString() )

Next

Console.WriteLi ne()

' 'Pause.

Console.ReadLin e()

Next

Best Regards,

Luqman

"Ken" <fk****@yahoo.c omwrote in message news:11******** **************@ t38g2000prd.goo glegroups.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("O rders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName. ToString()
colType(x) = col.DataType.To String()
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

[Tom Shelton]

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.FillSch ema (schema, SchemaType.Sour ce)

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

using ( SqlConnection connection = new SqlConnection (
connectionStrin g ) )
{
using ( SqlCommand command = new SqlCommand ( statement,
connection ) )
{
using ( SqlDataAdapter adapter = new SqlDataAdapter (
command ) )
{
DataTable schema = new DataTable ();
adapter.FillSch ema ( schema, SchemaType.Sour ce );
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.ColumnNa me, column.DataType , column.AllowDBN ull, column.Ordinal ) );
}
}
}
}

fieldList.Sort ( FieldInfoCompar er.Instance );
return fieldList;
}

I do this for Stored Procs:

private static List<FieldInfoG etProcedureFiel dList ( string
procedure, string connectionStrin g )
{
List<FieldInfof ieldList = new List<FieldInfo( );
using ( SqlConnection connection = new SqlConnection (
connectionStrin g ) )
{
using ( SqlCommand command = new SqlCommand ( procedure,
connection ) )
{
command.Command Type = CommandType.Sto redProcedure;
using ( SqlDataAdapter adapter = new SqlDataAdapter (
command ) )
{
command.Connect ion.Open ();
SqlCommandBuild er.DeriveParame ters ( command );
DataTable schema = new DataTable ();
adapter.FillSch ema ( schema, SchemaType.Sour ce );
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.ColumnNa me, column.DataType , column.AllowDBN ull, column.Ordinal ) );
}
}
}
}

fieldList.Sort ( FieldInfoCompar er.Instance );
return fieldList;
}

--
Tom Shelton

"Luqman" <pe*******@cybe r.net.pkwrote in message
news:ep******** ******@TK2MSFTN GP03.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.connectionst ring = My.Settings.Acc DataConnectionS tring
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.ExecuteRead er(CommandBehav ior.KeyInfo)
'Retrieve column schema into a DataTable.
schemaTable = myReader.GetSch emaTable()
''For each field in the table...
For Each myField In schemaTable.Row s
'For each property of the field...
For Each myProperty In schemaTable.Col umns
' 'Display the field name and value.
Console.WriteLi ne(myProperty.C olumnName & " = " &
myField(myPrope rty).ToString() )
Next
Console.WriteLi ne()
' 'Pause.
Console.ReadLin e()
Next
Best Regards,
Luqman
"Ken" <fk****@yahoo.c omwrote in message
news:11******** **************@ t38g2000prd.goo glegroups.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("O rders") 'Orders is a table in the
datasource
For Each col In tbl.Columns
x += 1
colName(x) = col.ColumnName. ToString()
colType(x) = col.DataType.To String()
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