Create mysql database using mysql command

Question

I am having a problem when trying to create a database using mysql command. The code i am using is;

using (MySqlConnection con = connect_db())
{
    con.Open();

    MySqlCommand cmd = new MySqlCommand("CREATE DATABASE @name;", con);

    cmd.Parameters.AddWithValue("@name", "fancydb");

    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception exc)
    {                   
        return;
    }
    cmd.Dispose();
    con.Close();
    con.Dispose();
}

When I try to run this code I always get an error saying that I have an

error in mysql syntax near "fancydb"

but when I put the name in the command like: "CREATE DATABASE facnydb;" it works. Can anyone explain to me why is the error only happening when I try and use parameters?

Tomtom · Accepted Answer · 2013-05-27 10:25:35Z

3

The create-statement is not a data-manipulation-statement. Parameters are only allowed in such kind of operations. The create-statement ist a data-definition-statement.

answered May 27, 2013 at 10:25

Tomtom

9,4287 gold badges58 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salem · Accepted Answer · 2013-05-27 10:24:55Z

1

I would guess that query is executed as

CREATE DATABASE "fancydb";

which is wrong.

answered May 27, 2013 at 10:24

Salem

13k4 gold badges37 silver badges56 bronze badges

1 Comment

Soner Gönül Over a year ago

No! It will execude as CREATE DATABASE fancydb;

Soner Gönül · Accepted Answer · 2013-05-27 10:37:07Z

1

SqlCommand Parameters are supported for Data manipulation language operations not Data definition language operations.

Data manipulation language =

SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

Data definition language =

CREATE TABLE ... 
DROP TABLE ... ;
ALTER TABLE ... ADD ... INTEGER;

edited May 27, 2013 at 10:37

answered May 27, 2013 at 10:27

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

1 Comment

Noctis Over a year ago

So far the simplest answer I've seen on the topic of what you can and can't do. Cheers

Bugs · Accepted Answer · 2017-07-12 16:36:04Z

0

This works:

cmd.CommandText = "exec ('CREATE DATABASE ' + @database)";
cmd.Parameters.AddWithValue("@database", restoreDB);
cmd.ExecuteNonQuery();

edited Jul 12, 2017 at 16:36

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Jul 12, 2017 at 16:18

Sinev Viktor

4887 silver badges8 bronze badges

Collectives™ on Stack Overflow

Create mysql database using mysql command

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related