Post
Ask Question

ASP.NET Core Web API With Oracle Database And Dapper

This article will focus on how to create ASP.NET Core Web API to get the data from an Oracle database using Dapper ORM. First things first, here, we are not using SQL because there already are so many articles available on the internet that mostly use SQL Server for demonstration. So, I thought of writing an article where we will use Oracle as a database. To reduce the complexity of the database access logic, we are using Dapper ORM. So, let's move to the practical demonstration.

Create ASP.NET Core Web API Project

To create a new project in ASP.NET Core Web API, just open Visual Studio 2017 version 15.3 and we have to follow the below steps.

  1. Go to the File menu and click New >> Project.
  2. From the New Project window, first, you have to choose .NET Framework 4.6 or above versions and then, from the left panel, choose Visual C# and then .NET Core.
  3. From the right panel, choose “.NET Core Web Application” provide the Save location where you want to save the project, and click OK.
  4. From the next window which will provide you with different kinds of templates, you have to choose Web API.

Now, click OK. It will take a few minutes to configure the ASP.NET Core Web API project.

Setup Oracle Table and Stored Procedures

To create a database and tables for this demonstration, we are using Oracle Developer Tools. It is very lightweight and flexible which helps us to work with databases smoothly.

As per Oracle

Oracle SQL Developer is a free, integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

Create a database name call it "TEST_DB" and inside that create a table name as "EMPLOYEE". You can use the following syntax to create the table inside the "TEST_DB" database.

CREATE TABLE "TEST_DB"."EMPLOYEE" 
(
    "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY 
        MINVALUE 1 
        MAXVALUE 9999999999999999999999999999 
        INCREMENT BY 1 
        START WITH 100 
        CACHE 20 
        NOORDER 
        NOCYCLE,
    "NAME" VARCHAR2(255 BYTE),
    "SALARY" NUMBER(10,0),
    "ADDRESS" VARCHAR2(500 BYTE)
) 
SEGMENT CREATION IMMEDIATE
PCTFREE 10 
PCTUSED 40 
INITRANS 1 
MAXTRANS 255 
NOCOMPRESS 
LOGGING 
STORAGE(
    INITIAL 65536 
    NEXT 1048576 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    PCTINCREASE 0 
    FREELISTS 1 
    FREELIST GROUPS 1 
    BUFFER_POOL DEFAULT 
    FLASH_CACHE DEFAULT 
    CELL_FLASH_CACHE DEFAULT
) 
TABLESPACE "TEST_DATA";

We need to add some dummy records inside the tables so that we can directly get the data from PostMan. So, we are adding four records here as follows.

INSERT INTO TEST_DB.EMPLOYEE (ID, NAME, SALARY, ADDRESS) VALUES (100, 'Mukesh', 20000, 'India');
INSERT INTO TEST_DB.EMPLOYEE (ID, NAME, SALARY, ADDRESS) VALUES (101, 'Rion', 28000, 'US');
INSERT INTO TEST_DB.EMPLOYEE (ID, NAME, SALARY, ADDRESS) VALUES (102, 'Mahesh', 10000, 'India');
INSERT INTO TEST_DB.EMPLOYEE (ID, NAME, SALARY, ADDRESS) VALUES (103, 'Banky', 20000, 'India');

Now it's time to create one SP which will bring the list of employees' records. Here we are using Cursor for returning list of data as an output parameter.

CREATE OR REPLACE PROCEDURE "TEST_DB"."USP_GETEMPLOYEES" 
(
    EMPCURSOR OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN EMPCURSOR FOR
    SELECT ID, NAME, SALARY, ADDRESS
    FROM Employee;
END;

Now we're going to create one SP which will get the individual record for an employee based on their employee ID.

CREATE OR REPLACE PROCEDURE "TEST_DB"."USP_GETEMPLOYEEDETAILS"
(
    EMP_ID IN INT,
    EMP_DETAIL_CURSOR OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN EMP_DETAIL_CURSOR FOR
    SELECT ID, NAME, SALARY, ADDRESS
    FROM Employee
    WHERE ID = EMP_ID;
END;

Install Dapper ORM

Open "Package Manager Console" from the "NuGet Package Manager" of the Tools menu type the following command and press enter to install Dapper and its dependencies if have one.

Install-Package Dapper -Version 1.50.5

After installation, you can check with references section of the project. One reference as "Dapper" has been added inside that.

Install Oracle Manage Data Access for Core

We are using the Asp.Net Core Web API application with Oracle and need to access the Oracle database from the Core application. To use the Oracle database with the .Net Core application, we have the Oracle library which will help us to manage the logic of database access. So, we have to install the following package that is in the beta version.

Install-Package Oracle.ManagedDataAccess.Core -Version 2.12.0-beta2
 

Add Oracle Connection

Now we have everything related to the database like the database, tables, SPs, etc. To access the database from Web API, we have to create a connection string as usual inside the "appsettings.json" file.

{
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  },
  "ConnectionStrings": {
    "EmployeeConnection": "data source=mukesh:1531;password=**********;user id=mukesh;Incr Pool Size=5;Decr Pool Size=2;"
  }
}

Create Repositories

To keep the separation of concern in mind, we are using a Repository here. Create a new folder as "Repositories" inside the Web API project and create an interface as "IEmployeeRepository" and a class as "EmployeeRepository" which will be implemented to IEmployeeRepository.

namespace Core2API.Repositories
{
    public interface IEmployeeRepository
    {
        object GetEmployeeList();
        object GetEmployeeDetails(int empId);
    }
}

Following is the EmployeeRepository class which is implementing IEmployeeRepository. To access configuration, we are injecting IConfiguration in the constructor. So, we have a configuration object that is ready to use. Apart from that, we have the GetConnection() method which will get the connection string from the appsettings.json and provide it to OracleConnection to create a connection and finally return the connection. We have implemented "IEmployeeRepository" which has two methods GetEmployeeDetails and GetEmployeeList.

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.

SEND button

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.

URL

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.


Similar Articles