Creating a mysql database using the mysql command

Question

I had a problem trying to create a database using the mysql command. The code I'm 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 the error message that I have

error in mysql syntax near "fancydb"

but when I enter the name in the command: "CREATE DATABASE facnydb;" , it works. Can someone explain to me why the error only occurs when trying to use parameters?

Answer 1

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

Answer 2

I would suggest that the query is executed as

 CREATE DATABASE "fancydb"; 

which is wrong.

Answer 3

SqlCommand parameters are supported for Data Manipulation Operations Operations Data Definition Language .

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; 

Answer 4

It works:

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