Ad:

C# Discussion Thread
Marked Solved
Views: 4274

Nov 3rd, 2009

How to CREATE a mySQL database in C# code

Expand Post »

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.

Similar Threads

Resize Image + retain quality + upload to mysql database in PHP
problem to insert data in database in mysql in PHP
how to write to a mysql database in VB.NET
Pasing texte file to create mysql database using java in Java
Authenticate users for mysql database using C in C

lukeser

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 3rd, 2009

Re: How to CREATE a mySQL database in C# code

Click to Expand / Collapse Quote originally posted by lukeser ...

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.

Quote originally posted by lukeser ...

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:

C# Syntax (Toggle Plain Text)
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();
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?

gusano79

Reputation Points: 182

Solved Threads: 72

Posting Pro in Training

Offline

476 posts
since May 2004

Permalink

Nov 4th, 2009

Re: How to CREATE a mySQL database in C# code

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:

C# Syntax (Toggle Plain Text)
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();
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?[/QUOTE]

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.

lukeser

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 4th, 2009

Re: How to CREATE a mySQL database in C# code

Click to Expand / Collapse Quote originally posted by lukeser ...

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?

gusano79

Reputation Points: 182

Solved Threads: 72

Posting Pro in Training

Offline

476 posts
since May 2004

Permalink

Nov 5th, 2009

Re: How to CREATE a mySQL database in C# code

Click to Expand / Collapse Quote originally posted by gusano79 ...

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.

lukeser

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 5th, 2009

Re: How to CREATE a mySQL database in C# code

Quote ...

...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.

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Nov 6th, 2009

Re: How to CREATE a mySQL database in C# code

Click to Expand / Collapse Quote originally posted by DdoubleD ...

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!

lukeser

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Matrix problem

Next Thread in C# Forum Timeline: Hi, I dont get what Iv done wrong

DaniWeb Message

Cancel Changes

User Name
Password