How to connect to a MySQL database and retrieve data in C#
Connecting to a MySQL database is fairly simple and straight forward. The first thing you will need to do is to add a reference to MySQL.Data in your project.
This can be done in a few ways:
- Right click on references in your project -> click Manage NuGet packages -> Search and install MySQL.Data from Oracle
- Open Package Manager Console from Tools -> NuGet Package Manager -> Paste in the console the following line:
Install-Package MySql.Data - If you have installed the .NET Connector from MySQL you can navigate to where you have installed the connector and add a reference to the MySQL.Data.dll file
After you have added a reference to MySQL.Data you can tailor the snippet below to suit your requirements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | string serverIp = "<Server IP or Hostname>"; string username = "<DB username>"; string password = "<DB password>"; string databaseName = "<DB name>"; string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName); string query = "SELECT * FROM YourTable"; var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString); conn.Open(); var cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn); var reader = cmd.ExecuteReader(); while (reader.Read()) { var someValue = reader["SomeColumnName"]; // Do something with someValue } |
If you are interested in other type of databases you can check my Microsoft Access Database and SQLite database tutorials.
How would I convert my result to a string?
if (DBnull.value == reader[“SomeColumnName”])
return “”;
return Convert.ToString(reader[“SomeColumnName”]);