How to CREATE a mySQL database in C# code

[lukeser]

My C# program is able to work with existing MySQL and Access databases. Additionally, it will also create a new Access database if none exists. I am not able to get it to create a new mySQL database though.

When the program attempts to run the ExecuteNonQuery command for CREATE DATABASE, it fails because it requires an open database connection (ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.) But how can you have an open connection to a database that is yet to exist?!

My code structure to create a new Access database is in this order:

1. Create the database
2. Open the database
3. Create a database table

Is this the same order that I should apply for creating a mySQL database?

Thank you to all responders.

[gusano79]

My code structure to create a new Access database is in this order:

1. Create the database
2. Open the database
3. Create a database table

Is this the same order that I should apply for creating a mySQL database?

It's not quite the same; Access is a file-based database system, where MySQL is server-based, so you'll need some sort of open connection to the server to do anything useful.

When the program attempts to run the ExecuteNonQuery command for CREATE DATABASE, it fails because it requires an open database connection (ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.)

I don't have a server handy to test this on, and I'm not sure what MySQL library you're using (ODBC, Connector/Net?)... but I think it would look something like this:

MySqlConnection connection = new MySqlConnection("Data Source=serverName;UserId=rootOrOtherAdminAccount;PWD=topSecretPassword;");
MySqlCommand command = new MySqlCommand("CREATE DATABASE FancyDatabase;", connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();

Is that different than what your code is doing?

[lukeser]

I don't have a server handy to test this on, and I'm not sure what MySQL library you're using (ODBC, Connector/Net?)... but I think it would look something like this:

MySqlConnection connection = new MySqlConnection("Data Source=serverName;UserId=rootOrOtherAdminAccount;PWD=topSecretPassword;");
MySqlCommand command = new MySqlCommand("CREATE DATABASE FancyDatabase;", connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();

Is that different than what your code is doing?

Thanks gusano79. I had the Open and ExecuteNonQuery statements reversed, but have put them in the order which you have illustrated above. I am still unable to Open. I went into SQL Server Management Studio Express and granted all privileges to the root user but still no luck.

[gusano79]

I am still unable to Open. I went into SQL Server Management Studio Express and granted all privileges to the root user but still no luck.

A more detailed description of the problem than "still no luck" would help... is it the same exception or a different one? Exception message and stack trace are both useful. Also, what version MySQL are you connecting to, and with what .NET data library?

[lukeser]

A more detailed description of the problem than "still no luck" would help... is it the same exception or a different one? Exception message and stack trace are both useful. Also, what version MySQL are you connecting to, and with what .NET data library?

I solved the problem. I was first attempting to open a DB with a nonexistent DB name (which is logically correct for the program), but when that failed (as it should), I never modified the connectionString to remove the Database argument and then reattempt the open. Once I did, the new database was created.

[DdoubleD]

...I went into SQL Server Management Studio Express and granted all privileges to the root user but still no luck.

I think you might be mixing apples and oranges because you indicate you checked your permissions with "SQL Server Management Studio", but you are programmatically trying to create a database using MySql class objects.

Go to the MySql.org and find the sourceforge link to the free MySQL Administrator download and try using that utiltiy to view and modify your source permissions.

[lukeser]

I think you might be mixing apples and oranges because you indicate you checked your permissions with "SQL Server Management Studio", but you are programmatically trying to create a database using MySql class objects.

Go to the MySql.org and find the sourceforge link to the free MySQL Administrator download and try using that utiltiy to view and modify your source permissions.

You're right on DD! I was doing that and realized it. After reading so many pages on the subject, I somehow made the switch from MySQL to SQL utilities, but it's all working now (in both SQL and MySQL). Thanks for the note though!