Installing and Configuring MySql/Connecting with C#
Recently, I was asked to do some C# development with MySQL as the backend.
Having C# with SQL Server 2005 development under my belt setting up a connection to a MySQL Server was pretty easy, however, I will walk you through the setup.
Two packages you will need are the MySQL Essentials Installer(this installs the server)
and the MySQL Connecter/.NET Installer(this installs the bridge between MySQL and .NET)
The third package, the GUI tools, is optional, but it is a lot easier to work with. (Think of it as a Management Studio Express, for those of you who have used SQL 2005)
MySQL Essentials Package: http://dev.mysql.com/downloads/mysql/5.1.html#downloads
MySqlConnector/NET: http://www.mysql.com/products/connector/net/
MySQL Gui Tools: http://dev.mysql.com/downloads/gui-tools/5.0.html
Run the Essentials Package first, this package actually contains the server instance.
Follow the prompts, they are pretty much straight forward.
When you receive the prompt about your root password, pick a password.
Next install the MySQL Connector.
Follow the prompts, they are pretty much straight forward as well, and should install pretty quickly.
From here, you can use the command line client located in Start>Program Files>MySql Version>MySql Command Line Client, however, I choose to use the GUI tools instead.
If you have not already, launch the GUI tools installer.
Once this is done, go to Start>Program Files>MySql Version#>MySQL query browser
Click the ... button next to Stored Connection.
Click New Connection.
Fill out the properties
Connection: Name the connection
Username: root
Password: password used during setup
Hostname: localhost
Port: default is 3306
Schema: specify the default database, you can leave this blank.
Click apply and close.
In the connection screen, from the dropdown, pick the connection we just created.
Type your password.
If given a warning about a default schema, type test or default in the Default Schema field, this will create
a database named test or default. You can always delete this dummy database afterwards.
To create tables right click your database in the Schemata and click Create New Table.
Most of the GUI is intuitive and should be picked up quite easy.
Connecting to MySQL using C#
Start a C# Application project (I'm using a Console App in my example)
Right click your project and click Add Reference.
Navigate to the Connector DLL.
By default this dll is located at C:/Program Files/MySql/MySql Connector .NET Version#/Binaries/.NET 2.0/MySql.Data.dll
Once this is added add the line:
using MySql.Data.MySqlClient;
using System.Data; //for the ConnectionState enum
to the top of your code.
Add this code to your main/load method:
//sets up the connection string
string connString = "Server = localhost; Database = databaseName; Uid = root; Pwd = pass;";
//creates a new MySqlConnection object
MySqlConnection conn = new MySqlConnection(connString);
try{
//try to open the connection, catch any exceptions
conn.Open();
//if the connection succeeds, print a msg
Console.WriteLine("Connection Succeeded");
}catch(Exception ex){
//print that the connection failed, and the associated Message
Console.WriteLine("Connection Failed: "+ex.Message);
}finally{
//if the connection is currently open, close it
if(conn.State==ConnectionState.Open)
conn.Close();
}
Troubleshooting:
- If you are having trouble connecting, check your username and password.
- Double check the connection string.
- If you are sure the above is correct, make sure the MySql server instance is running. You can do this by
- going to Start>Run. Type services.msc and hit Ok. Scroll down the list to MySql, it should say started. If it does not, right click and hit Start.
- Try to connect with the MySql GUI tools.
Just about everything you can do with SQL Server you can do with MySql using the familiarly named classes:
MySqlConnection, MySqlCommand, MySqlDataReader
Hope this gets you started on using MySql and C#.
Any questions or comments, feel free to ask.