1. Computing

How to programmatically create a MySQL Database table in CSharp

By

In this C# programming how to, I'll show you how to connect to a MySql database programmatically, create a table there and then insert a row in that table. You can build this and run it with Microsoft Visual C# 2010 Express or even the full Visual Studio 2010 if you have it. It will probably work in Microsoft C# 2008 but it hasn't been tested and there may be a question mark over the optional parameters in the DbTable.AddColumn and DbColumn constructor methods that might need changing.

Difficulty: Easy
Time Required: 10 Minutes

Here's How:

  1. Before You Start

    You need to download and install the MySQL Connector/Net. If you have already installed a much earlier version you have to uninstall that and then reinstall the latest. At the time of writing the latest was version 6.3.6.

    Once this is done, fire up Visual Studio and create a new project. Or you can just download the code! If MySql.Data is not in the references (in the Solution Explorer), do right click on References and click Add Reference. Under the .NET tab you should find MySql.Data so select it.

  2. There are four classes of interest:
    1. db
    2. dbBuilder
    3. DbColumn
    4. TableBuilder

    The db class manages the connection, creating it in a lazy way when Connection is accessed. The string conn is very important. On my network, I have a Linux box accessed by the name fred, it has a database called gv and the sa user has a password xxxx. Your values will no doubt differ so enter them in this string.

    For executing sql queries that return a dataset call db.GetData(sql), but to execute non data queries (e.g. insert, update or Create Table, etc) use db.Exec(sql).

  3. The DbBuilder class does the donkey work of creating a table and is used in the TableBuilder class. Call DbBuilder.StartTable(string TableName) pasing in your desired table. Next call DbBuilder.AddColumn with appropriate data such as column type and name and then other parameters as needed. By default all columns are nullable.

    I've only defined three column types (int, varchar and datetime) but you are free to add others.

    After adding all of the columns you need, call dbBuilder.BuildSql() to generate a complete create table sql statement. Then execute it to build the table.

  4. This is the code for building a table with two ints (indexnum is the primary key) DbBuilder dbb = new DbBuilder() ;
    dbb.StartTable(Tablename) ;
    dbb.AddColumn(DbBuilder.ColTypes.ctint, "indexnum", isPrimary: true, isnull: false) ;
    dbb.AddColumn(DbBuilder.ColTypes.ctint, "status") ;
    dbb.AddColumn(DbBuilder.ColTypes.ctdatetime, "datecreated") ;
    sql = dbb.BuildSQL() ;

  5. The DbColumn class is where the column management work is done. Each column is managed here (name, type, null, primary etc) and you if you want to add new types (double etc), add them to the ColTypes enum and and modify the ColType property which returns the sql needed to generate that column.

    The DbBuilder.BuildSQL uses a StringBuilder class (fastest for building strings in memory) and loops through the DbBuilder.columns to assemble the sql. The crlf const (copied from Environment.NewLine) is added to each line and is not neeeded except to make the sql more readable by breaking up the lines.

  6. This is the exact sql generated by this example code.

    create table stats(
    indexnum int(11) null,
    status int(11) null,
    datecreated datetime null ,
    primary key (indexnum)
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

    When executed it creates the table.

  7. TableBuilder.BuildTable is called once for each table and creates that table. BuildAllTables is there to build all the tables needed in one go, in this case just the one table stats. After creating it, one row is inserted.

    Other Databases

    By default the System.Data has a provider for SqlClient that supports Sql Server. Add using System.Data.SqlClient; to the top of the program and remove the using MySql.data.MySqlClient line. Also remove My from MySqlConnection etc and have it connect to a SQL Server database with an appropriate connection string. It will need the sql syntax changed as well.

  8. Generating Connection Strings

    The website Connectionstrings.com has plenty of examples. Plus in Windows you can create a connection strings, see Using MySQL Database with Visual C# 2008 which is still relevant with Visual C# 2010 but uses a different way to connect (via ODBC). It demonstrates how to create connection strings with a .udl file

©2014 About.com. All rights reserved.