473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

.NET MySQL (or any database): Getting Schema

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

Apr 13 '07 #1
3 5958
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" wrote:
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

Apr 13 '07 #2
On Apr 12, 9:26 pm, Kerry Moorman
<KerryMoor...@discussions.microsoft.comwrote:
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

Apr 13 '07 #3
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?...a-e7603e957de2

Cor

"h4xPace" <cs*******@gmail.comschreef in bericht
news:11**********************@y80g2000hsf.googlegr oups.com...
>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

Apr 13 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Konrad | last post by:
Maybe it's not a proper place to ask this question, but maybe someone could provide me the way to load file with schema and data of mysql database (created by phpMyAdmin on Windows system), to...
4
by: Dave Moore | last post by:
Hi All, I'm looking at using PHP and MySQL for a particular website application. However, I was a bit concerned that if a PHP script went wrong then it would be possible to accidentally delete or...
2
by: Developer98115 | last post by:
I need help getting schema information from an existing SQL Server database. My thought was that you could use ADOX via InterOp. Has anyone done this successfully and how? I have created a...
10
by: Krakatioison | last post by:
Hi everyone, can someone point me to download of an example for saving and retrieving to/from MYSQL database. Or did anyone of you tried this and could share your code with me. I've got some data...
5
by: lawrence k | last post by:
I'm a little weak on my basic I/O. Help me out please. Is it right to say that I can just open any file with file(), get it as a string, and then store in a MySql database, in, say, a MediumText...
8
by: menmysql | last post by:
i am not bale to solve this problem since two weeks i am trying to access records from mysql database using jsp. inside this jsp program i wrote all my JDBC code. it is working very nicely and...
9
by: christopher_board | last post by:
Hi all. I am trying to write a php page which connects to a MySQL Database which is supposed to get the results from a table within a database and display the results in a table. Below is the...
1
by: jarremw | last post by:
hello all, i am making a database to hold some scanned pdf's so i was wondering how would i go about saving them in the mysql database? would i put the file in the database or just link to it? so if...
1
by: parimi | last post by:
hi this is pawanparimi doing mca working on a project "conducting online examination",i have a table in mysql database, the table schema is RANKS=student_id, section1, section2, section3, totalmarks,...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.