Get schema?

[Russell Verdun]

I hope someone can advise on a solution....how can I obtain schema
information, column names to be exact, from a SQL, Oracle table via ado.net.
I need to gather the column names from a given table.

Thank you.

 

[William \(Bill\) Vaughn]

It's easy in ADO.NET 2.0--use the GetSchema method on the Connection object.
In 1.1 you can use the DataReader.GetSchemaTable method like this:

Dim cmd as New SqlCommand("SELECT * FROM TargetTable", cn)
Dim dr as New SqlDataReader =
cmd.ExecuteReader(CommandBehavior.SchemaOnly Or CommandBehavior.KeyInfo)
Dim dt as DataTable = dr.GetSchemaTable

Wala... your schema for the selected table including all of the columns.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Scott M.]

Create a connection to your data source.
Prepare a dataAdapter with a select command.
Use dataAdapter.FillSchema(dataSet) to create a DataTable in a DataSet that
has the same structure of the original but no data.

 

[Ben Wallace \(3\)]

Thanks, for the replys. My main goal is to contruct an SQL statement baseD
on the schema, using the fields IN an UPDATE statement, how can I get the
field names from the schema table, and creat a comma seperated list
(field1,field2,field3 ....etc...), a loop possibly?

Thanks

 

[Guest]

Ben,

Here is an example that displays all the column names. You can modify it to
create a comma-separated list:

Private Sub DisplayColumns(ByVal ConnectionString As String, ByVal
TableName As String)

Dim cn As New OleDb.OleDbConnection(ConnectionString)
Dim cmd As New OleDb.OleDbCommand
Dim rdr As OleDb.OleDbDataReader
Dim tbl As DataTable

cmd.CommandText = "Select * From " & TableName
cn.Open()
cmd.Connection = cn
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or
CommandBehavior.KeyInfo)
tbl = rdr.GetSchemaTable
rdr.Close()

Dim row As DataRow
For Each row In tbl.Rows
MsgBox(row("ColumnName"))
Next

End Sub

Kerry Moorman

 

[Paul Clement]

¤ I hope someone can advise on a solution....how can I obtain schema
¤ information, column names to be exact, from a SQL, Oracle table via ado.net.
¤ I need to gather the column names from a given table.
¤

Another method is to use GetOleDbSchemaTable:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

'Use appropriate OLEDB connection string
DatabaseConnection.ConnectionString = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"

DatabaseConnection.Open()

'Retrieve schema information about columns in Customers table.
SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Customers"})

Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString)
Next RowCount

'Shows what schema info is available
DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)