Click here to Skip to main content
13,447,284 members (39,409 online)
Click here to Skip to main content
Add your own
alternative version


67 bookmarked
Posted 18 Jan 2004

MySQL Schema in C#

, 18 Jan 2004
Rate this:
Please Sign up or sign in to vote.
An article on HOW-TO grab MySQL schema in C#. This program utilizes ODBC API.

Main Screen

Figure 1.0: Screenshot of MySQL Utility.


This article covers how to talk to MySQL database and extract the schema from it.


As many of you know, MySQL is an open source database. It is free for non-commercial purposes. This article would be helpful for anybody doing development in MySQL and C#. What is the motivation behind this utility? Well, an application could crash in the middle of database activities. Often, these databases may not reside on the same site as your development/testing machine. We need a utility that would help us get a snapshot of the database at client site. If you have an utility that would capture the state of the client database, then, you can load it in your testing machine. The idea is to reproduce the situation your client faced. That way you can address any undiscovered issues.

Functionality Supported

  • Saving Schema as Text
  • Viewing Schema
  • Viewing Entire Database

Required Tools

  • ODBC.NET Data Provider from Microsoft
  • MySQL Database
  • MySQL ODBC Connector

Using the code

First, add a reference to Microsoft ODBC. Then use the using microsoft.odbc statement to tell it that you want to you MS ODBC. In short, OdbcConnection will be used to open connection, OdbcCommand to execute queries, and OdbcDataReader to read the resulting row set. The code shown below documents each step. You will notice, it runs the MySQL specific command SHOW TABLES to get the list of tables. Then it runs another query based on that particular table, SHOW COLUMNS IN CURRENT_TABLE. This is all the code does.

The Code

@@  Function:
@f    ::PrepareSchema
@@  Description:
@d    when this is called the widget is updated and everything
//    about this database and tables are displayed  
@@  Type:
@t    public 
@@  Arguments:
//    none.
@@  Returns:
@r    void
@@  Preconditions:
@c    Provided that the GUI is running and DB Connection is made.
@@  Postconditions:
@o    DB schema displayed  
@@  References:
@e    <A href="" target=_blank>Query MySql with C#</A>. 

public void PrepareSchema()
    // create the connection object by setting the DSN                
    OdbcConnection ocConnection = new OdbcConnection("DSN="+ strDSN);
    // second connection is created so we could make 
    // queries while executing one
    OdbcConnection ocConnection2 = new OdbcConnection("DSN="+ strDSN);
    // this will open up both connections
    // declare the commands for each table and column            
    OdbcCommand ocTableCommand;
    OdbcCommand ocColumnCommand;

    // create a command object. this will execute SHOW TABLES
    // query. In mysql, it shows all of the tables contained in
    // the database in use.
    ocTableCommand = new OdbcCommand("SHOW TABLES", ocConnection);
    // declare reader objects for tables and columns
    OdbcDataReader odrTableReader;
    OdbcDataReader odrColumnReader;
    // queries that return result set are executed by ExecuteReader()
    // If you are to run queries like insert, update, delete then
    // you would invoke them by using ExecuteNonQuery() 
    odrTableReader = ocTableCommand.ExecuteReader();
    // place create db statement in rich text box
    rchtxtSchema.Text += "CREATE DATABASE ";
    rchtxtSchema.Text += ocConnection.Database;
    rchtxtSchema.Text += ";\r\n\r\n";
    rchtxtSchema.Text += "USE DATABASE ";
    rchtxtSchema.Text += ocConnection.Database;
    rchtxtSchema.Text += ";\r\n\r\n";
    string strTable      = ""; 
    string strColumnName = "";
    string strColumnType = "";  
    string strColumnNull = "";   
    string strColumnPKey = "";  
    string strColumnDflt = "";  
    string strColumnExtr = "";  
    // reader the set of tables
        // here we are expecting rows with only 1 column 
        // containing the table name. that's why explcity
        // call GetString() at 0th index  
        strTable = odrTableReader.GetString(0);  
        rchtxtSchema.Text += "CREATE TABLE "; 
        rchtxtSchema.Text += strTable; 
        rchtxtSchema.Text += "\r\n(\r\n";
        // build up the command for each table
        ocColumnCommand = new OdbcCommand("SHOW COLUMNS IN " + 
          strTable, ocConnection2);
        // run the query
        odrColumnReader = ocColumnCommand.ExecuteReader();
        // reading the set of columsn
            // This query returns the name of column, Type,
            // wherther it's Null, whether it's primary Key,
            // the default value, and extra info such as 
            // whether it's autoincrement or not           
            strColumnName = odrColumnReader.GetString(0);
            strColumnType = odrColumnReader.GetString(1); 
            strColumnNull = odrColumnReader.GetString(2);  
            strColumnPKey = odrColumnReader.GetString(3);
            //strColumnDflt = odrColumnReader.GetString(4);
            strColumnExtr = odrColumnReader.GetString(5);
            if (!strColumnNull.Equals("YES"))
                strColumnNull = " NOT NULL ";
                strColumnNull = "";
            if (strColumnPKey.Equals("PRI"))
                strColumnPKey = " PRIMARY KEY ";
            //this.rchtxtSchema.Text += "\n";
            rchtxtSchema.Text += " ";
            rchtxtSchema.Text += strColumnName;
            rchtxtSchema.Text += " ";         
            rchtxtSchema.Text += strColumnType; 
            rchtxtSchema.Text += strColumnPKey; 
            rchtxtSchema.Text += strColumnNull;
            rchtxtSchema.Text += ",";                                 
            rchtxtSchema.Text += "\r\n";   
        rchtxtSchema.Text = this.rchtxtSchema.Text.Substring(0, 
        rchtxtSchema.Text += "\r\n);\r\n\r\n";   
        // free up the reader object
    // close the reader    
    // disconnect

Points of Interest

Initially, I kept going back and forth from ODBC, ADODB and OLEDB to implement this. According to MySQL, it is not safe to use OLEDB. There was no mention of how to utilize OLEDB to perform simple database tasks. At the end, it was decided doing this would be very simple in ODBC. You have probably noticed that I used built-in commands (i.e. show tables) that the specific DB provider uses. I am definitely open to any suggestions or working examples of standards that work with MySQL.

HOW-TO use this Demo

Saving Schema

First, click on Select the Target Database. This should produce the dialog box showing the list of system as well as user DSNs.

DSN Dialog

Figure 2.0 - Depicts the DSN dialog form.

Now, click on Save Schema File As and select where you wish to save the file.

File Save Dialog

Figure 3.0 - Depicts the File Save dialog.

Next, make sure Save Schema is checked. Then click on Run.

Operation Status

Figure 4.0 - Depicts the status of operation. In this example, the program successfully wrote C:\s01user38_schema.txt.

Viewing Schema

First, uncheck Save Schema and then check View Schema. It should produce the output as depicted in figure 5.0

Schema View

Figure 5.0 - Depicts the schema of s01user38.

Viewing Database

Now, click on View Database in Grid and press Run.

Database in Grid

Figure 6.0 - Depicts the result of the query. It will show + initially. You have to click on it to expand all. Then it will show the tables as shown in this graphic. Then click on each blue link to see the rowset they contain.



This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Rajib Bahar
United States United States
Over the course of his 16+year career, Rajib Bahar has been a creative problem solver, finding innovative solutions to clients’ data questions, issues and challenges. He works primarily in Business Intelligence, and Data analytics practice with experience in BigData, DataScience, & Cloud computing environments. His career initially started in the unix world submitting perl and fortran jobs to super-computers back in 2000. This was before Big Data and distributed computing got Big. Later on, he moved on to .NET application development roles, and worked with variety of Database systems such as MS Sql Server, MySQL, PostgresSQL, DB2, & Oracle. Around 2008, he started working in Business Intelligence and/or Datawarehousing practice utilizing both Ralph Kimball and Inmon methodologies. Currently, he is working in Big Data platforms and connecting it with SQL Server 2016, R, Python, and building POCs on Data Science for BI projects. He also enjoys working with visualization tools such as Power BI, Tablue, and QlikView. His recent venture in the Data world includes a podcast on BigData, Data Science, DBMS, analytics, and cloud computing. The podcast is also syndicated across iTunes and YouTube. The link to podcast is

He has also served as a Board of Members of directors at KFAI radio, PASSMN, and various other non-profits. His expertise in data have led to savings at KFAI radio on expensive software license costs, reduced paper expense via online voting. Currently, he spends time contributing to the Data Visualization challenge the station faces.

Feel free to connect with Rajib @rajib2k5 or

You may also be interested in...

Comments and Discussions

GeneralSHOW CREATE TABLE [modified] Pin
elbow_26-Oct-07 1:51
memberelbow_26-Oct-07 1:51 
QuestionMySQL table schema UNIQUE KEY? Pin
nobl9-Apr-07 22:59
membernobl9-Apr-07 22:59 
Questionfiles are corrupted? Pin
hunter1444-Jan-06 7:37
memberhunter1444-Jan-06 7:37 
AnswerRe: files are corrupted? Pin
Abdul (Rajib) Bahar11-Jan-06 13:57
memberAbdul (Rajib) Bahar11-Jan-06 13:57 
Generalneed help in .net with oracle database Pin
ydderf222-Sep-04 21:06
memberydderf222-Sep-04 21:06 
GeneralOLEDB connectivity with mySql. Pin
Extremist20042-Apr-04 5:07
sussExtremist20042-Apr-04 5:07 
GeneralRe: OLEDB connectivity with mySql. Pin
Abdul (Rajib) Bahar2-Apr-04 6:46
memberAbdul (Rajib) Bahar2-Apr-04 6:46 
GeneralUpdate changes in the grid to the MySQL DB Pin
ms++29-Jan-04 6:54
memberms++29-Jan-04 6:54 
GeneralRe: Update changes in the grid to the MySQL DB Pin
Abdul (Rajib) Bahar29-Jan-04 11:34
memberAbdul (Rajib) Bahar29-Jan-04 11:34 
GeneralRe: Update changes in the grid to the MySQL DB Pin
M Saad++29-Jan-04 14:50
memberM Saad++29-Jan-04 14:50 
GeneralRe: Update changes in the grid to the MySQL DB Pin
denise8384058-Sep-04 15:42
memberdenise8384058-Sep-04 15:42 
GeneralComments Pin
Daniel Fisher (lennybacon)22-Jan-04 4:10
memberDaniel Fisher (lennybacon)22-Jan-04 4:10 
GeneralRe: Comments Pin
Abdul (Rajib) Bahar22-Jan-04 5:37
memberAbdul (Rajib) Bahar22-Jan-04 5:37 
GeneralRe: Comments Pin
Daniel Fisher (lennybacon)22-Jan-04 7:19
memberDaniel Fisher (lennybacon)22-Jan-04 7:19 
Generalwhy not make the dsn from the program too Pin
Prabhdeep Singh21-Jan-04 3:49
memberPrabhdeep Singh21-Jan-04 3:49 
GeneralRe: why not make the dsn from the program too Pin
Abdul (Rajib) Bahar21-Jan-04 8:17
memberAbdul (Rajib) Bahar21-Jan-04 8:17 
General.Net MySQL ADO.Net Pin
FruitBatInShades19-Jan-04 0:53
memberFruitBatInShades19-Jan-04 0:53 
Generalbytefx + suggestion Pin
dog_spawn19-Jan-04 6:37
memberdog_spawn19-Jan-04 6:37 
GeneralRe: bytefx + suggestion Pin
Abdul (Rajib) Bahar19-Jan-04 7:00
memberAbdul (Rajib) Bahar19-Jan-04 7:00 
GeneralRe: bytefx + suggestion Pin
ThatsAlok18-Mar-08 2:43
member ThatsAlok18-Mar-08 2:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02-2016 | 2.8.180318.3 | Last Updated 19 Jan 2004
Article Copyright 2004 by Rajib Bahar
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid