Get schema?

  • Thread starter Thread starter Russell Verdun
  • Start date Start date
R

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.
 
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.
__________________________________
 
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.
 
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
 
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
 
¤ 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)
 
Back
Top