GetSchema to retrieve column properties

chrisbarber1

GetSchema to retrieve column properties

Visual Studio329
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

-

RobinS

Registered User

Tue Apr 24 23:16:03 CDT 2007

Re:GetSchema to retrieve column properties

What is your data source? Is it SQLServer?

Robin S.

-----------------------

"Ken" <fkml99@yahoo.com>wrote in message

Quote

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

Randy

Registered User

Wed Apr 25 21:55:50 CDT 2007

Re:GetSchema to retrieve column properties

Yes.

-

RobinS

Registered User

Fri Apr 27 00:10:50 CDT 2007

Re:GetSchema to retrieve column properties

"Randy" <randy.eastland@gmail.com>wrote in message

Quote

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.

-

Luqman

Registered User

Wed May 16 09:11:30 CDT 2007

Re:GetSchema to retrieve column properties

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01C797EE.028DDA50

Content-Type: text/plain;

charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable

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 =3D My.Settings.AccDataConnectionString

cn.Open()

'Retrieve records from the Employees table into a DataReader.

cmd.Connection =3D cn

''I used 1=3D0 because I don't want to waste time in retrieving the =

records, just a Schema

cmd.CommandText =3D "SELECT * FROM Customers Where 1=3D0"=20

myReader =3D cmd.ExecuteReader(CommandBehavior.KeyInfo)

'Retrieve column schema into a DataTable.

schemaTable =3D 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 & " =3D " & =

myField(myProperty).ToString())

Next

Console.WriteLine()

' 'Pause.

Console.ReadLine()

Next

Best Regards,

Luqman

"Ken" <fkml99@yahoo.com>wrote in message =

Quote

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?

=20

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 =3D cn.GetSchema("Orders") 'Orders is a table in the

datasource

For Each col In tbl.Columns

x +=3D 1

colName(x) =3D col.ColumnName.ToString()

colType(x) =3D col.DataType.ToString()

Next

cn.Close()

=20

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.

=20

I'm getting an error on the GetSchema line: The requested collection

(Orders) is not defined.

=20

I have little experience with the GetSchema method, so any advice on

how to accomplish this is appreciated.

=20

Thx,

Ken

------=_NextPart_000_000B_01C797EE.028DDA50

Content-Type: text/html;

charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD>

<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 6.00.6000.16441" name=3DGENERATOR>

<STYLE></STYLE>

</HEAD>

<BODY>

<DIV>

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 =3D=20

My.Settings.AccDataConnectionString

cn.Open()

'Retrieve records from the Employees =

table into a=20

DataReader.

cmd.Connection =3D cn

''I used 1=3D0 because I don't want to =

waste time in=20

retrieving the records, just a Schema

cmd.CommandText =3D "SELECT * FROM =

Customers Where 1=3D0"=20



myReader =3D=20

cmd.ExecuteReader(CommandBehavior.KeyInfo)

'Retrieve column schema into a =

DataTable.

schemaTable =3D =

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 & "=20

=3D " & myField(myProperty).ToString())

 Next

 Console.WriteLine()

' 'Pause.

 Console.ReadLine()

Next

Best Regards,

Luqman

 

 </DIV>

<DIV>"Ken" <<A=20

href=3D"mailto:fkml99@yahoo.com">fkml99@yahoo.com</A>> =

wrote in message=20

<A=20

href=3D"news:1177466970.811703.125840@t38g2000prd.googlegroups.com"><FONT=

=20

face=3DArial=20

size=3D2>news:1177466970.811703.125840@t38g2000prd.googlegroups.com</FONT=

Quote

</A><FONT=20

face=3DArial size=3D2>...</DIV>> =

I'm trying to run=20

a loop to capture column property information from a > table in my =

datasource.  Can anybody see where this is going wrong? >=20

 >        Dim tbl As New=20

DataTable >        Dim col As=20

DataColumn >        Dim x As=20

Integer >        Dim =

colName(99) As=20

String >        Dim colType(99) =

As=20

String >       =20

cn.Open() >        tbl =3D=20

cn.GetSchema("Orders") 'Orders is a table in the >=20

datasource >        For Each =

col In=20

tbl.Columns >         =

  =20

x +=3D =

1 >          &nbs=

p;=20

colName(x) =3D=20

col.ColumnName.ToString() >      &nbs=

p;    =20

colType(x) =3D=20

col.DataType.ToString() >       =

=20

Next >        =

cn.Close() >=20

 > If it's not obvious, my intended result are two variables for=20

each > column from the original datatable that will capture a) the =

name=20

of > the column, and b) the datatype of the column.  I will =

then use=20

these > variables to drive subsequent logic. > > I'm =

getting=20

an error on the GetSchema line:  The requested collection > =

(Orders)=20

is not defined. > > I have little experience with the =

GetSchema=20

method, so any advice on > how to accomplish this is =

appreciated. >=20

 > Thx, > Ken ></BODY></HTML>

------=_NextPart_000_000B_01C797EE.028DDA50--

-

Tom

Registered User

Thu May 17 02:13:52 CDT 2007

Re:GetSchema to retrieve column properties

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<FieldInfo>GetTableFieldList ( string table,

string connectionString )

{

List<FieldInfo>fieldList = 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<FieldInfo>GetProcedureFieldList ( string

procedure, string connectionString )

{

List<FieldInfo>fieldList = 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" <pearlsoft@cyber.net.pk>wrote in message

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" <fkml99@yahoo.com>wrote in message

Quote

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

-