How to implement async CRUD methods for an Employee model using Dapper with MySQL

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using MySql.Data.MySqlClient;

using Dapper;

public class EmployeeRepository

{

    private readonly string _connectionString;

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public async Task<int> CreateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +

                           "VALUES (@FirstName, @LastName, @Email, @PhoneNumber);" +

                           "SELECT LAST_INSERT_ID()";

            return await connection.ExecuteScalarAsync<int>(query, employee);

        }

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

            return await connection.QueryAsync<Employee>(query);

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees WHERE Id = @id";

            return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });

        }

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, " +

                           "Email = @Email, PhoneNumber = @PhoneNumber WHERE Id = @Id";

            int rowsAffected = await connection.ExecuteAsync(query, employee);

            return rowsAffected > 0;

        }

    }

    public async Task<bool> DeleteEmployeeAsync(int id)

    {

        using (IDbConnection connection = new MySqlConnection(_connectionString))

        {

            string query = "DELETE FROM Employees WHERE Id = @id";

            int rowsAffected = await connection.ExecuteAsync(query, new { id });

            return rowsAffected > 0;

        }

    }

}