| Figure 1.0: Screenshot of MySQL Utility. | | | | ocConnection.Database;rchtxtSchema.Text += |
| Introduction | | | | ";\r\n\r\n";rchtxtSchema.Text += "USE DATABASE |
| This article covers how to talk to MySQL database | | | | ";rchtxtSchema.Text += |
| and extract the schema from it. | | | | ocConnection.Database;rchtxtSchema.Text += |
| Background | | | | ";\r\n\r\n";string strTable = "";string strColumnName = |
| As many of you know, MySQLÂ is an open source | | | | "";string strColumnType = "";string strColumnNull = |
| database. It is free for non-commercial purposes. This | | | | "";string strColumnPKey = "";string strColumnDflt = |
| article would be helpful for anybody doing development | | | | "";string strColumnExtr = ""; |
| in MySQL and C#. What is the motivation behind this | | | | // reader the set of |
| utility? Well, an application could crash in the middle of | | | | tableswhile(odrTableReader.Read()) |
| database activities. Often, these databases may not | | | | { |
| reside on the same site as your development/testing | | | | // here we are expecting rows with only 1 column |
| machine. We need a utility that would help us get a | | | | // containing the table name. that's why explcity |
| snapshot of the database at client site. If you have an | | | | // call GetString() at 0th indexstrTable = |
| utility that would capture the state of the client | | | | odrTableReader.GetString(0);rchtxtSchema.Text += |
| database, then, you can load it in your testing machine. | | | | "CREATE TABLE ";rchtxtSchema.Text += |
| The idea is to reproduce the situation your client faced. | | | | strTable;rchtxtSchema.Text += "\r\n(\r\n"; |
| That way you can address any undiscovered issues. | | | | // build up the command for each |
| Functionality Supported | | | | tableocColumnCommand = new |
| - Saving Schema as Text | | | | OdbcCommand("SHOW COLUMNS IN " +strTable, |
| - Viewing Schema | | | | ocConnection2); |
| - Viewing Entire Database | | | | // run the queryodrColumnReader = |
| Required Tools | | | | ocColumnCommand.ExecuteReader(); |
| - ODBC.NET Data Provider from Microsoft | | | | // reading the set of |
| - MySQL Database | | | | columsnwhile(odrColumnReader.Read()) |
| - MySQL ODBC Connector | | | | { |
| Using the code | | | | // This query returns the name of column, Type, |
| First, add a reference to Microsoft ODBC. Then use | | | | // wherther it's Null, whether it's primary Key, |
| the using microsoft.odbc statement to tell it that you | | | | // the default value, and extra info such as |
| want to you MS ODBC. In short, OdbcConnection will | | | | // whether it's autoincrement or notstrColumnName = |
| be used to open connection, OdbcCommand to | | | | odrColumnReader.GetString(0);strColumnType = |
| execute queries, and OdbcDataReader to read the | | | | odrColumnReader.GetString(1);strColumnNull = |
| resulting row set. The code shown below documents | | | | odrColumnReader.GetString(2);strColumnPKey = |
| each step. You will notice, it runs the MySQL specific | | | | odrColumnReader.GetString(3); |
| command SHOW TABLES to get the list of tables. | | | | //strColumnDflt = |
| Then it runs another query based on that particular | | | | odrColumnReader.GetString(4);strColumnExtr = |
| table, SHOW COLUMNS IN CURRENT_TABLE. This | | | | odrColumnReader.GetString(5);if |
| is all the code does. | | | | (!strColumnNull.Equals("YES"))strColumnNull = " NOT |
| The Code | | | | NULL ";elsestrColumnNull = "";if |
| Collapse /*//////////////////////////////////////////////////////// | | | | (strColumnPKey.Equals("PRI"))strColumnPKey = " |
| //////////////// | | | | PRIMARY KEY "; |
| // | | | | //this.rchtxtSchema.Text += "\n";rchtxtSchema.Text += |
| @@ Function: | | | | " ";rchtxtSchema.Text += |
| @f ::PrepareSchema | | | | strColumnName;rchtxtSchema.Text += " |
| // | | | | ";rchtxtSchema.Text += |
| @@ Description: | | | | strColumnType;rchtxtSchema.Text += |
| @d when this is called the widget is updated and | | | | strColumnPKey;rchtxtSchema.Text += |
| everything | | | | strColumnNull;rchtxtSchema.Text += |
| // about this database and tables are displayed | | | | ",";rchtxtSchema.Text += "\r\n"; |
| // | | | | }rchtxtSchema.Text = |
| @@ Type: | | | | t.Length-3);rchtxtSchema.Text += "\r\n);\r\n\r\n"; |
| @t public | | | | // free up the reader objectodrColumnReader.Close(); |
| // | | | | } |
| @@ Arguments: | | | | // close the readerodrTableReader.Close(); |
| // none. | | | | // |
| @@ Returns: | | | | disconnectocConnection.Close();ocConnection2.Close(); |
| @r void | | | | } |
| // | | | | Points of Interest |
| @@ Preconditions: | | | | Initially, I kept going back and forth from ODBC, |
| @c Provided that the GUI is running and DB | | | | ADODB and OLEDB to implement this. According to |
| Connection is made. | | | | MySQL, it is not safe to use OLEDB. There was no |
| // | | | | mention of how to utilize OLEDB to perform simple |
| @@ Postconditions: | | | | database tasks. At the end, it was decided doing this |
| @o DB schema displayed | | | | would be very simple in ODBC. You have probably |
| // | | | | noticed that I used built-in commands (i.e. show tables) |
| @@ References: | | | | that the specific DB provider uses. I am definitely open |
| @e Query MySql with C#. | | | | to any suggestions or working examples of standards |
| // | | | | that work with MySQL. |
| ////////////////////////////////////////////////////////////////////// | | | | HOW-TO use this Demo |
| //*/public void PrepareSchema() | | | | Saving Schema |
| { | | | | First, click on Select the Target Database. This should |
| // create the connection object by setting the DSN | | | | produce the dialog box showing the list of system as |
| OdbcConnection ocConnection = new | | | | well as user DSNs. |
| OdbcConnection("DSN="+ strDSN); | | | | |
| // second connection is created so we could make | | | | Figure 2.0 - Depicts the DSN dialog form. |
| // queries while executing one | | | | Now, click on Save Schema File As and select where |
| OdbcConnection ocConnection2 = new | | | | you wish to save the file. |
| OdbcConnection("DSN="+ strDSN); | | | | |
| // this will open up both | | | | Figure 3.0 - Depicts the File Save dialog. |
| connectionsocConnection.Open();ocConnection2.Open(); | | | | Next, make sure Save Schema is checked. Then click |
| // declare the commands for each table and column | | | | on Run. |
| OdbcCommand ocTableCommand; | | | | |
| OdbcCommand ocColumnCommand; | | | | Figure 4.0 - Depicts the status of operation. In this |
| // create a command object. this will execute SHOW | | | | example, the program successfully wrote |
| TABLES | | | | C:\s01user38_schema.txt. |
| // query. In mysql, it shows all of the tables contained in | | | | Viewing Schema |
| // the database in use.ocTableCommand = new | | | | First, uncheck Save Schema and then check View |
| OdbcCommand("SHOW TABLES", ocConnection); | | | | Schema. It should produce the output as depicted in |
| // declare reader objects for tables and columns | | | | figure 5.0 |
| OdbcDataReader odrTableReader; | | | | |
| OdbcDataReader odrColumnReader; | | | | Figure 5.0 - Depicts the schema of s01user38. |
| // queries that return result set are executed by | | | | Viewing Database |
| ExecuteReader() | | | | Now, click on View Database in Grid and press Run. |
| // If you are to run queries like insert, update, delete | | | | |
| then | | | | Figure 6.0 - Depicts the result of the query. It will show |
| // you would invoke them by using | | | | + initially. You have to click on it to expand all. Then it |
| ExecuteNonQuery()odrTableReader = | | | | will show the tables as shown in this graphic. Then click |
| ocTableCommand.ExecuteReader(); | | | | on each blue link to see the rowset they contain. |
| // place create db statement in rich text | | | | Credits |
| boxrchtxtSchema.Text += "CREATE DATABASE | | | | - Query MySQL with C#. |
| ";rchtxtSchema.Text += | | | | |