How to Get Data from MySql using C#

In this tutorial, we are going to learn how to get data from MySql using C#. In order to access MySQL and get data from it, we use MySql ADO.NET Data Access Client.

Note*: I assume that you have a basic knowledge of C# programming, relational database management and MySql.

In this tutorial, we use Visual Studio Express Edition to build a sample project that get data from MySQL.

If you don’t have Visual Studio Express Edition, you can download it from Microsoft website.

Now open Visual Studio and create a new console project call “DataAccessSample“.  Next we need to download MySql Client library from Nuget package. Now right click on the solution project on the right side and then select “Manage Nuget Packages for Solution“.

 

Type “MySql Data” in the search box, you’ll see a list of search result. Select “MySql.Data” from the search result and check the box on the right side. Then click “Install” to install “MySql.Data” package to our solution project. You will see a popup windows appears. Just click “OK” and “I Access” to finish the installation.

Next, we’re going to create a sample MySql database with only one table for this tutorial.

In this tutorial, we’ll create a MySql database “sample_db” and a table “employees“.

First, you need to create a database “sample_db” and then execute the script below to create a table “employees“. This script includes sample data.

-- ----------------------------
-- Table structure for `employees`
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(255) DEFAULT NULL,
  `employee_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('1', 'James', 'Emp001');
INSERT INTO `employees` VALUES ('2', 'Jonh', 'Emp002');

Next, we’ll write C# code to get data from employee table.

We can access and get data from MySql using C# code below.

                string connectionString = "Server=localhost;Port=3306;User Id=root;Password=Chamnan1;Database=sample_db;pooling=true;Min Pool Size=0;Max Pool Size=4;";
                using (MySqlConnection mySqlConnection = new MySqlConnection(connectionString))
                {
                    mySqlConnection.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = mySqlConnection;
                        cmd.CommandText = "SELECT * FROM employees";
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while(reader.Read())
                            {
                                Employee employee = new Employee();
                                employee.EmployeeID = Convert.ToInt32(reader["employee_id"]);
                                employee.EmployeeName = (reader["employee_name"] != DBNull.Value) ? reader["employee_name"].ToString() : null;
                                employee.EmployeeCode = (reader["employee_code"] != DBNull.Value) ? reader["employee_code"].ToString() : null;
                                employees.Add(employee);
                            }
                        }
                    }
                }

Here is a full sample code for this tutorial.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace DataAccessSample
{
    class Program
    {
        static void Main(string[] args)
        {

            List<Employee> employees = GetEmployees();
            foreach (Employee employee in employees)
            {
                Console.WriteLine(employee.EmployeeID + "," + employee.EmployeeName + "," + employee.EmployeeCode);
            }
        }
        static List<Employee> GetEmployees()
        {
            try
            {
                List<Employee> employees = new List<Employee>();
                string connectionString = "Server=localhost;Port=3306;User Id=root;Password=Chamnan1;Database=sample_db;pooling=true;Min Pool Size=0;Max Pool Size=4;";
                using (MySqlConnection mySqlConnection = new MySqlConnection(connectionString))
                {
                    mySqlConnection.Open();
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = mySqlConnection;
                        cmd.CommandText = "SELECT * FROM employees";
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            while(reader.Read())
                            {
                                Employee employee = new Employee();
                                employee.EmployeeID = Convert.ToInt32(reader["employee_id"]);
                                employee.EmployeeName = (reader["employee_name"] != DBNull.Value) ? reader["employee_name"].ToString() : null;
                                employee.EmployeeCode = (reader["employee_code"] != DBNull.Value) ? reader["employee_code"].ToString() : null;
                                employees.Add(employee);
                            }
                        }
                    }
                }
                return employees;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            
        }
    }
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string EmployeeName { get; set; }
        public string EmployeeCode { get; set; }
    }
}

In this tutorial, we learned how to access MySql and get data from it using C#.