How to Connect to a MySQL Database with C#
Preparing a C# Application for Running SQL Queries
May 28, 2009
Databases are an integral part of many C# applications. They are used to store data that can be used in any desktop or web based program. For example the database may contain:
- Customer details such as name, email and postal address
- Product details such as price, postage and quantity in stock
- Order details such as customer and product ids and dispatch dates
Of course, these are only useful if the C# application can access the data. Fortunately that's not a problem if the information is stored in a MySQL database, and that's because MySQL provide a .NET connector. If a programmer uses the MySQL .NET connector then any of their C# applications (or, in fact, any of their .NET based applications) can connect to a MySQL database with very little effort.
Downloading and Installing the MySQL .NET Connector
The MySQL .NET connector installer can be downloaded from the MySQL Connector/Net web page. Once that's been done then the installer can be run (by double clicking on it in Windows Explorer). When the installer has finished its task then the connector will be ready for use.
Adding the MySQL .NET Connector as a Project Reference
The programmer must next add the MySQL .NET Connector as a project reference before they can use it in an IDE (Integrated Design Environment), such as SharpDevelop, where they do can this by:
- Opening a new or existing C# project
- Clicking on Project and then “Add References”
- Selecting the MySQL.Data reference (as shown in figure 1 and the bottom of this article)
They will now be able to use the connector in their application.
Loading the MySQL Connection Library
With MySQL.Data set up as a reference the correct library will need to be loaded. In this case that is the MySqlClient library:
The MySQL connector can now be used in the application.
Creating the MySQL Database Connection Object with C#
Now the programmer can create the connection object itself:
This new connection cannot be used yet. For that to happen the connection must be given a connection string.
Setting the Connection String
The connection string needs to contain all of the information required to connect to a database. These are:
- the server - by default this is always “localhost”
- the database
- the user’s id
- the user’s password
Each of the elements must have a semicolon between them. For example:
Here the connection will be made on the localhost to the aec database via the aec_user account.
Opening the MySQL Database Connection
The connection has not actually been made yet, but that is a very simple step:
And now the connection is ready to receive SQL (Structured Query Language) statements such as:
- Insert
- Select
- Update
- Delete
This enables the programmer to carry out all of the database operations that they need.
Closing the MySQL Database Connection
The database connection should close when the user exits from the application. However, it is always good practice to close the connection neatly in the code:
This will ensure that any memory uses by the connection will be freed up, and will stop the application from hogging too many of the computer’s resources.
Summary
Any C# programmer wishing to connect to a MySQL database simply has to:
- Download and install the MySQL .NET Connector
- Add the MySQL.Data reference to a C# project
- Load the MySQLClient library
- Create the connection object
- Set the database connection string
- Open the connection
And they can then produce an application that connects seamlessly with any MySQL database.
 |
 |
