Create table dynamically with MySQL command in c#

Question

I want to create table dynamically and fill it with random numbers, but when execute code, MySQL throw an error about syntax.

 for (int i = 0; i < 100; i++)
        {
            //gameSession is int, i tried use Parameters.AddWithValue but still have same problem.
            MySqlCommand dice = new MySqlCommand("INSERT INTO "+gameSession.ToString()+" (Dice1,Dice2) Values (@dice1,@dice2)", myConnection);


            Random dice1 = new Random();
            dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));
            Random dice2 = new Random();
            dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));
            if (myConnection.State == ConnectionState.Closed)
            {
                myConnection.Open();
            }
            dice.ExecuteNonQuery();
        }

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near INSERT INTO '7571877 ...

Answer 1

One major reason you have an error in your syntax is that the table is not quoted. Check out the MySQL manual for identifiers (which includes table names).

Just like you can't really use numbers by default to represent variables, you can't do the same for tables without escaping the identifiers. A couple solutions for you would be to prefix the table name with a letter or to use a single table.

Answering the question in your title:

You can create a table if it doesn't exist using the "IF NOT EXISTS" clause. The SQL would look like this:

CREATE TABLE table_name IF NOT EXISTS (
    dice1 INTEGER,
    dice2 INTEGER
);

If you insist on going down this route, you should create your table names with a standard prefix letter:

"s" + gameSession.ToString()

Be warned, having a separate table for each session does complicate your database maintenance needs, particularly for dealing with abandoned sessions. It's a lot easier to remove rows in a single table than find all tables that are abandoned.

Another serious concern has to do with database security. When building an application against a database, it is far better to only grant insert and update privileges to a user than it is to grant them the ability to create and drop any table in your database. Since the table names are dynamically created, you can't really maintain per-table privileges easily. If this is web facing you are opening yourself up to some serious liability for very little gain.

Answering the need:

A better design is to create a single table with a session id. Otherwise, you will have any number of tables with one record each. You would create the table once like this:

CREATE TABLE DiceRolls IF NOT EXISTS (
    session INTEGER,
    dice1 INTEGER,
    dice2 INTEGER
);

Your inserts would only need to change a little bit:

 for (int i = 0; i < 100; i++)
    {
        //gameSession is int, i tried use Parameters.AddWithValue but still have same problem.
        MySqlCommand dice = new MySqlCommand("INSERT INTO DiceRolls (Session, Dice1,Dice2) Values (@session,@dice1,@dice2)", myConnection);

        dice.Parameters.AddWithValue("@session", gameSession);
        Random dice1 = new Random();
        dice.Parameters.AddWithValue("@dice1", dice1.Next(1, 7));
        Random dice2 = new Random();
        dice.Parameters.AddWithValue("@dice2", dice2.Next(1, 7));
        if (myConnection.State == ConnectionState.Closed)
        {
            myConnection.Open();
        }
        dice.ExecuteNonQuery();
    }

This makes clean up a lot easier as you can batch job something that deletes everything that isn't a current session.