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;

}