ASP.NET Core Web API With Oracle Database And Dapper

using Core2API.Oracle;
using Dapper;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;

namespace Core2API.Repositories
{
    public class EmployeeRepository : IEmployeeRepository
    {
        IConfiguration configuration;

        public EmployeeRepository(IConfiguration _configuration)
        {
            configuration = _configuration;
        }

        public object GetEmployeeDetails(int empId)
        {
            object result = null;

            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("EMP_ID", OracleDbType.Int32, ParameterDirection.Input, empId);
                dyParam.Add("EMP_DETAIL_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var conn = this.GetConnection();

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "USP_GETEMPLOYEEDETAILS";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public object GetEmployeeList()
        {
            object result = null;

            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var conn = this.GetConnection();

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "USP_GETEMPLOYEES";
                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public IDbConnection GetConnection()
        {
            var connectionString = configuration.GetSection("ConnectionStrings")
                .GetSection("EmployeeConnection").Value;
            var conn = new OracleConnection(connectionString);
            return conn;
        }
    }
}

To use Oracle datatypes with .Net Core, we are using the OracleDyamicParameters class which will provide the list of functions to manage Oracle parameters behaviors.

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data;
namespace Core2API.Oracle
{
    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        private readonly DynamicParameters dynamicParameters = new DynamicParameters();
        private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)
        {
            OracleParameter oracleParameter;

            if (size.HasValue)
            {
                oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);
            }
            else
            {
                oracleParameter = new OracleParameter(name, oracleDbType, value, direction);
            }

            oracleParameters.Add(oracleParameter);
        }

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            var oracleParameter = new OracleParameter(name, oracleDbType, direction);
            oracleParameters.Add(oracleParameter);
        }

        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

            var oracleCommand = command as OracleCommand;

            if (oracleCommand != null)
            {
                oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
            }
        }
    }
}

Configure Dependencies in Startup.cs

To access the dependencies on the controller or repository classes, we have to configure or we can say register our dependency classes with interfaces inside the ConfigureServices method of the Startup class.

using Core2API.Repositories;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace Core2API
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddTransient<IEmployeeRepository, EmployeeRepository>();
            services.AddSingleton<IConfiguration>(Configuration);
            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }
}

Add EmployeeController

Now it's time to finally create an API call in EmployeeControler. First, we have added IEmployeeRepository inside the constructor to use dependencies. Secondly, we have to create an API call with Route attributes for both methods.

using Core2API.Repositories;
using Microsoft.AspNetCore.Mvc;
namespace CoreAPI.Controllers
{
    [Produces("application/json")]
    public class EmployeeController : Controller
    {
        private readonly IEmployeeRepository employeeRepository;

        public EmployeeController(IEmployeeRepository _employeeRepository)
        {
            employeeRepository = _employeeRepository;
        }

        [Route("api/GetEmployeeList")]
        public ActionResult GetEmployeeList()
        {
            var result = employeeRepository.GetEmployeeList();
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }

        [Route("api/GetEmployeeDetails/{empId}")]
        public ActionResult GetEmployeeDetails(int empId)
        {
            var result = employeeRepository.GetEmployeeDetails(empId);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }
    }
}

Now we have everything ready, like the repository is ready, the connection with the Oracle database is ready and finally, the API call is also ready inside the controller. So, it's time to run the API and see the result in PostMan. Just press F5 to run the Web API and open PostMan to test the result.

To test in PostMan, first, choose "Get" as a method and provide the URL to get the list of employee records and click on to SEND button which will make a request to our API and get the list of employees that we have added at the beginning while creating the database scripts.

To get the single employee record, just pass the following URL as you can see in the image. You can see here, we want to see the record for employee id 103. Once you send the request, you can see the output something like below.

Conclusion

So, today, we have learned how to create an ASP.NET Core Web API project and use Dapper with Oracle database.

I hope this post will help you. Please put your feedback using comments which helps me to improve myself for the next post. If you have any doubts, please ask your doubts or query in the comment section and if you like this post, please share it with your friends.