.NET MySQL (or any database): Getting Schema

  • Thread starter Thread starter h4xPace
  • Start date Start date
H

h4xPace

I am building a MySQL query application, and I have run into a small
snag. MySQL has released a set of classes that extend the .NET
framework base data classes (command, connection, etc), and I am using
them to interact with the MySQL server (on localhost). Everything
works great on that side of the aisle.

However, I have never worked with getting schema from a database
before, so I am fumbling around for a workable solution to doing this.
I can get the --entire-- schema from the database with :

DataTable1 = MySqlConnection.GetSchema("Tables")

But this fills the data table with a ton of usless (to me) schema
info. I can cludge my way through selecting only the tables that I
want, but I feel like there has to be a better way.

What I want to accomplish in the end:

A treeview control that lists fields in the selected MySQL database
schema:
EX:

Table1
----Field1
----Field2
----Field3
Table2
----Field1
.....

Just like the treeview on the Server Explorer in VS. You get the
point. The treeview reference is just to illustrate the scope of the
data I need. Its a strategy for getting individual databas / table
schema from the server that I am struggling with.

Any help, a link, whatever would be greatly appreciated.

-Chris
 
Chris,

What about using the MySQLDataReader's GetSchemaTable method?

Here is an example using an OleDbDataReader. This should work the same with
the MySQL* objects:

Dim cn As New OleDbConnection(connectionString)
Dim cmd As New OleDbCommand
Dim rdr As 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()

Now you can assign the datatable, tbl, to a datagrid to see what the schema
data looks like.

Kerry Moorman
 
Chris,

What about using the MySQLDataReader's GetSchemaTable method?

Here is an example using an OleDbDataReader. This should work the same with
the MySQL* objects:

Dim cn As New OleDbConnection(connectionString)
Dim cmd As New OleDbCommand
Dim rdr As 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()

Now you can assign the datatable, tbl, to a datagrid to see what the schema
data looks like.

Kerry Moorman
Click to expand...

Thanks a million Kerry. That worked like a charm.
Just for the other searchers out there, here is my (Kerry's) working
code:

'Notes:
'Gets a connection string stored in settings
'and binds to a DataGridView named SchemaView at the end of this block


Dim cn As New MySqlConnection(My.Settings.MYSQLConnection)
Dim cmd As New MySqlCommand
Dim rdr As MySqlDataReader
Dim tbl As DataTable

cmd.CommandText = "Select * From customer"
cn.Open()
cmd.Connection = cn
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or
CommandBehavior.KeyInfo)
tbl = rdr.GetSchemaTable
rdr.Close()
SchemaView.DataSource = tbl.DefaultView

Thanks again.
-Chris
 
Back
Top