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

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using Oracle.ManagedDataAccess.Client;

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 OracleConnection(_connectionString))

        {

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

                           "VALUES (:FirstName, :LastName, :Email, :PhoneNumber) " +

                           "RETURNING Id INTO :id";

            var parameters = new DynamicParameters();

            parameters.Add(":FirstName", employee.FirstName);

            parameters.Add(":LastName", employee.LastName);

            parameters.Add(":Email", employee.Email);

            parameters.Add(":PhoneNumber", employee.PhoneNumber);

            parameters.Add(":id", DbType.Int32, direction: ParameterDirection.Output);

            await connection.ExecuteAsync(query, parameters);

            return parameters.Get<int>(":id");

        }

    }

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

    {

        using (IDbConnection connection = new OracleConnection(_connectionString))

        {

            string query = "SELECT * FROM Employees";

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

        }

    }

    public async Task<Employee> GetEmployeeByIdAsync(int id)

    {

        using (IDbConnection connection = new OracleConnection(_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 OracleConnection(_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 OracleConnection(_connectionString))

        {

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

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

            return rowsAffected > 0;

        }

    }

}