Oracle Stored Procedure Output Parameters with Dapper in ASP.NET Core

Introduction

Oracle databases are robust and widely used in enterprise environments. When working with Oracle stored procedures in ASP.NET Core applications, handling output parameters can be challenging. This tutorial will guide you through the process of unlocking Oracle stored procedure output parameters using Dapper, a lightweight ORM (Object-Relational Mapper) for . NET.

Prerequisites

Before we begin, ensure you have the following.

Visual Studio 2019 or later
.NET Core SDK 3.1 or later
Oracle Database (You can use Oracle Database XE for development purposes)
Basic knowledge of C#, ASP.NET Core, and SQL

Source Code

This source code is publicly available on GitHub link.

Setting up the Project

Create an ASP.NET core project

Open Visual Studio and create a new ASP.NET Core Console App project.

ASP.NET

Install NuGet packages

You need to install the Dapper and Oracle.ManagedDataAccess.Core packages.

Dapper

NuGet Packages

Creating the Oracle stored procedure

Let's create a simple stored procedure in Oracle that accepts an input parameter and returns an output parameter.

Connect to the Oracle database

Use SQL*Plus, SQL Developer, Toad, or any other Oracle client tool to connect to your Oracle database.

Create the Stored procedure

CREATE OR REPLACE PROCEDURE DEMO.SAVE_EMPLOYEE_DETAILS (
   V_NAME        IN     VARCHAR2,
   V_ADDRESS     IN     VARCHAR2,
   V_DEPARTMENT  IN     VARCHAR2,
   V_POSITION    IN     VARCHAR2,
   O_EMP_ID      OUT    VARCHAR2,
   O_ERROR       OUT    VARCHAR2
) 
AS
   V_EMP_ID VARCHAR2 (10);
BEGIN
   -- GENERATE EMPLOYEE ID
   SELECT DEMO.EMP_SEQ.NEXTVAL INTO V_EMP_ID FROM DUAL;
      
   -- INSERT EMPLOYEE DETAILS
   INSERT INTO DEMO.EMPLOYEE_DETAILS (
      EMP_ID,
      NAME,
      ADDRESS,
      DEPARTMENT,
      POSITION
   ) VALUES (
      V_EMP_ID,
      V_NAME,
      V_ADDRESS,
      V_DEPARTMENT,
      V_POSITION
   );   
   -- SET OUTPUT EMPLOYEE ID
   O_EMP_ID := V_EMP_ID;
      
   -- RESET ERROR OUTPUT
   O_ERROR := NULL;
EXCEPTION
   WHEN OTHERS THEN
      O_ERROR := 'FAILED TO SAVE EMPLOYEE DETAILS. ' || SQLERRM;
      ROLLBACK;
      RETURN;
END SAVE_EMPLOYEE_DETAILS;
/

Implementing the ASP.NET core application

Add OracleDynamicParameters class

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace DapperOracleDemo
{
    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 item = ((!size.HasValue) ? new OracleParameter(name, oracleDbType, value, direction) : new OracleParameter(name, oracleDbType, size.Value, value, direction));
            oracleParameters.Add(item);
        }
        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            OracleParameter item = new OracleParameter(name, oracleDbType, direction);
            oracleParameters.Add(item);
        }
        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);
            (command as OracleCommand)?.Parameters.AddRange(oracleParameters.ToArray());
        }
        public T Get<T>(string name)
        {
            var parameter = oracleParameters.SingleOrDefault(t => t.ParameterName == name);
            object val = parameter?.Value;
            if (val == DBNull.Value)
            {
                if (default(T) != null)
                {
                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type! Note that out/return parameters will not have updated values until the data stream completes (after the 'foreach' for Query(..., buffered: false), or after the GridReader has been disposed for QueryMultiple)");
                }
                return default;
            }
            return (T)val;
        }
        public T Get<T>(int index)
        {
            var parameter = oracleParameters[index];
            object val = parameter?.Value;
            if (val == DBNull.Value)
            {
                if (default(T) != null)
                {
                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type! Note that out/return parameters will not have updated values until the data stream completes (after the 'foreach' for Query(..., buffered: false), or after the GridReader has been disposed for QueryMultiple)");
                }
                return default;
            }
            return (T)val;
        }
    }
}

Add OracleDataAccessObject class

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Data;
namespace DapperOracleDemo
{
    public class OracleDataAccessObject
    {
        private readonly string _ConnectionString;

        public OracleDataAccessObject(string ConnectionString)
        {
            _ConnectionString = ConnectionString;
        }
        private IDbConnection GetOracleConnection()
        {
            return new OracleConnection(_ConnectionString);
        }
        public dynamic OracleSProcWithParam(string sql, OracleDynamicParameters param)
        {
            using IDbConnection cnn = GetOracleConnection();
            CommandType? commandType = CommandType.StoredProcedure;
            return cnn.Execute(sql, param, null, null, commandType);
        }
    }
}

Add Employee Input and Output Dto

namespace DapperOracleDemo
{
    public class EmployeeInputDto
    {
        public string NAME { get; set; }
        public string ADDRESS { get; set; }
        public string DEPARTMENT { get; set; }
        public string POSITION { get; set; }
    }
}

namespace DapperOracleDemo
{
    public class EmployeeOutputDto
    {
        public string EMP_ID { get; set; }
        public string ERROR { get; set; }
    }
}

using Oracle.ManagedDataAccess.Types;
namespace DapperOracleDemo
{
    public class SpReturnModel
    {
        public dynamic ID { get; set; }
        public OracleString ErrorMsg { get; set; }
    }
}

Configure the Connection String

static class Config
{
    public static string OracleDBConnectionString => "Data Source=192.168.29.1:1521/DEMODB;User Id=DEMO;Password=demo@123;";
    public static string SP_SAVE_EMPLOYEE_DETAILS = "DEMO.SAVE_EMPLOYEE_DETAILS";
}

Add SaveEmployeeDetails() Method

static SpReturnModel SaveEmployeeDetails(EmployeeInputDto dto)
{
    var oraDao = new OracleDataAccessObject(Config.OracleDBConnectionString);
    var oracleParam = new OracleDynamicParameters();
    oracleParam.Add("V_NAME", OracleDbType.Varchar2, ParameterDirection.Input, dto.NAME);
    oracleParam.Add("V_ADDRESS", OracleDbType.Varchar2, ParameterDirection.Input, dto.ADDRESS);
    oracleParam.Add("V_DEPARTMENT", OracleDbType.Varchar2, ParameterDirection.Input, dto.DEPARTMENT);
    oracleParam.Add("V_POSITION", OracleDbType.Varchar2, ParameterDirection.Input, dto.POSITION);
    oracleParam.Add("O_EMP_ID", OracleDbType.Varchar2, ParameterDirection.Output, size: 20);
    oracleParam.Add("O_ERROR", OracleDbType.Varchar2, ParameterDirection.Output, size: 2000);
    var oracleQuery = Config.SP_SAVE_EMPLOYEE_DETAILS;
    oraDao.OracleSProcWithParam(oracleQuery, oracleParam);
    return GetSpOutParamResult(oracleParam, "O_EMP_ID");
}
static SpReturnModel GetSpOutParamResult(OracleDynamicParameters param, string idParam = "")
{
    SpReturnModel retMdl = new SpReturnModel()
    {
        ID = !string.IsNullOrEmpty(idParam) ? param.Get<dynamic>(idParam) : 0,
        ErrorMsg = param.Get<OracleString>("O_ERROR")
    };

    return retMdl;
}

Testing the Application

Call the SaveEmployeeDetails() Method

static void Main(string[] args)
{
    var inDto = new EmployeeInputDto
    {
        NAME = "SURYA RAJ GHIMIRE",
        ADDRESS = "KATHMANDU, NEPAL",
        DEPARTMENT = "RESEARCH & DEVELOPMENT",
        POSITION = "SOFTWARE ENGINEER"
    };
    var resp = SaveEmployeeDetails(inDto);
    var outDto = new EmployeeOutputDto
    {
        EMP_ID = resp.ID is null ? null : (string)resp.ID,
        ERROR = resp.ErrorMsg.ToString()
    };
    Console.WriteLine("Employee id: " + outDto.EMP_ID);
    Console.WriteLine("Error: " + outDto.ERROR);
    Console.ReadLine();
}

Run the Application

Run your ASP.NET Core application and see the output on the console.

Conclusion

In this tutorial, we covered how to handle output parameters from Oracle stored procedures in an ASP.NET Core application using Dapper. By following these steps, you can efficiently integrate Oracle stored procedures into your .NET applications, leveraging the power and simplicity of Dapper for database operations. Happy coding!