.NET MySQL (or any database): Getting Schema

[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

 

[Guest]

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

 

[h4xPace]

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

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

 

[Cor Ligthert [MVP]]

Hi,

Ken wrote something about mySQL in the area of your problem. If this does
not fix your problem, have than a look at our website under the ADONET
General tab at the bottom after scrolling down.

http://www.vb-tips.com/dbpages.aspx?ID=c6b62715-d07e-4e48-92da-e7603e957de2

Cor