C# RSS C# RSS

How to CREATE a mySQL database in C# code

Please support our C# advertiser: Intel Parallel Studio Home
Thread Solved

•
•
Join Date: Nov 2009
Posts: 6
Reputation: lukeser is an unknown quantity at this point 
Solved Threads: 0
lukeser lukeser is offline Offline
Newbie Poster

How to CREATE a mySQL database in C# code

 
0
  #1
23 Days Ago
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2004
Posts: 80
Reputation: gusano79 is on a distinguished road 
Solved Threads: 5
gusano79 gusano79 is offline Offline
Junior Poster in Training
 
0
  #2
23 Days Ago
•
•
•
•
Originally Posted by lukeser View Post
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.

•
•
•
•
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) 
  1. MySqlConnection connection = new MySqlConnection("Data Source=serverName;UserId=rootOrOtherAdminAccount;PWD=topSecretPassword;");
  2. MySqlCommand command = new MySqlCommand("CREATE DATABASE FancyDatabase;", connection);
  3. connection.Open();
  4. command.ExecuteNonQuery();
  5. connection.Close();

Is that different than what your code is doing?
--smg
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2009
Posts: 6
Reputation: lukeser is an unknown quantity at this point 
Solved Threads: 0
lukeser lukeser is offline Offline
Newbie Poster
 
0
  #3
22 Days Ago
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) 
  1. MySqlConnection connection = new MySqlConnection("Data Source=serverName;UserId=rootOrOtherAdminAccount;PWD=topSecretPassword;");
  2. MySqlCommand command = new MySqlCommand("CREATE DATABASE FancyDatabase;", connection);
  3. connection.Open();
  4. command.ExecuteNonQuery();
  5. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2004
Posts: 80
Reputation: gusano79 is on a distinguished road 
Solved Threads: 5
gusano79 gusano79 is offline Offline
Junior Poster in Training
 
0
  #4
22 Days Ago
•
•
•
•
Originally Posted by lukeser View Post
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?
--smg
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2009
Posts: 6
Reputation: lukeser is an unknown quantity at this point 
Solved Threads: 0
lukeser lukeser is offline Offline
Newbie Poster
 
0
  #5
21 Days Ago
•
•
•
•
Originally Posted by gusano79 View Post
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2009
Posts: 901
Reputation: DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough 
Solved Threads: 142
DdoubleD DdoubleD is offline Offline
Posting Shark
 
0
  #6
21 Days Ago
•
•
•
•
...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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2009
Posts: 6
Reputation: lukeser is an unknown quantity at this point 
Solved Threads: 0
lukeser lukeser is offline Offline
Newbie Poster
 
0
  #7
20 Days Ago
•
•
•
•
Originally Posted by DdoubleD View Post
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!
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the C# Forum
Thread Tools Search this Thread



.net access algorithm array barchart bitmap box broadcast c# check checkbox client combobox control conversion csharp custom cyclethruopenforms data database datagrid datagridview dataset date/time datetime degrees development dll draganddrop drawing encryption enum event excel file finalyearproject form format forms function gdi+ getoutlookcontactusinfcsvfile globalization httpwebrequest image index input install installer java label list listbox mandelbrot math mono mouseclick mysql operator panel path photoshop picturebox pixelinversion post programming radians regex remote remoting richtextbox save server silverlight sleep socket sql sql-server statistics stream string table text textbox thread time timer timespan update usercontrol users validate validation visualstudio webbrowser wia windows winforms wpf xml
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC