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;

        }

    }

}

How to read data in .net from Oracle using Dapper

Following is an async C# method, that use the OracleConnection class to create a connection to the Oracle database. The QueryAsync method is called on the connection object to execute the query asynchronously and retrieve the Employee objects already mapped to the data returned by the SQL query. The AsList method is called on the result of the query to convert it to a list of Employee objects.

Note that you will need to add the reference to the Oracle.ManagedDataAccess.Client package.

 

using System.Collections.Generic;

using System.Data;

using System.Threading.Tasks;

using Dapper;

using Oracle.ManagedDataAccess.Client;

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Department { get; set; }

    public decimal Salary { get; set; }

}

public async Task<List<Employee>> GetEmployeeListFromOracleAsync()

{

    List<Employee> employees;

    string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=hostName)(PORT=portNumber))(CONNECT_DATA=(SERVER=DEDICATED)

(SERVICE_NAME=serviceName)));User Id=userName;Password=password;";

 

    using (IDbConnection connection = new OracleConnection(connectionString))

    {

        employees = (await connection.QueryAsync<Employee>("SELECT * FROM Employee")).AsList();

    }

   

    return employees;

}