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!