Returning Data From an Oracle Stored Procedure Using Dapper
Happy Sunday folks! It has been a while since my last post, as my family and I started house hunting immediately after the holiday season ended. I am happy to say we have found a new house and a buyer for our current one, and I should be able to push 1 to 2 articles a month after we settle in to the new place.
Speaking of moving, you may have noticed that this post is about Dapper despite previous ones focusing on Entity Framework. The reason for this is that I am working on rewriting a legacy application from ASP pages to .NET Core Web API/Angular and the Entity Framework Core driver needed to get the app working with our Oracle database still has not been released at the time of this rewriting. We need this app to run as a .NET Core application, so utilizing EF6 and targeting the app to the .NET Framework is not an option. To ensure development efforts are not hampered, I have decided to use Dapper in its place. As many of you know, Dapper is a database-agnostic micro-ORM that allows developers to quickly map SQL queries to models and perform typical CRUD operations against the database without requiring excess code.
I took to Steve Michelotti’s Pluralsight course on .NET Micro ORMs to learn how to use this tool, and quickly realized that some of the operations he was having it execute against his SQL Server DB did not behave the same way with my work’s Oracle DB. Most notably, I was unable to return data from a stored procedure in Oracle the same way he could with SQL Server. This is because Oracle needed an Out parameter of type sys_refcursor defined so it had a place to store the data to return, and the existing tools in Dapper did not allow me to capture it. So, like every frustrated developer, I took to Google to see if there was a quick workaround available. After 2 days of executing work arounds and toying around with additional libraries, I came across this Nuget package called Dapper.Oracle. I found this to be the best solution, as it is actively being supported (last update was 2 days ago at the time of this writing). So how can we use this to get data out of an Oracle Stored Procedure?
You can watch me implement the solution in the video below, however if you would like to run the code yourself please clone the GitHub repo associated with this blog and feel free to follow along (you will need to setup the databases yourself).
Let’s start by first comparing two stored procedures used to select games from a database (one from SQL Server, the other from Oracle).
In our SQL Server stored procedure, you can see we are simply defining the procedure name and them selecting all items from table GamesCatalog.
Create procedure [dbo].[GetAllGames] as
select * from dbo.GameCatalog
go;
However, in our Oracle Stored Procedure defines an out parameter of type sys_refcursor called Games, and then assigns the values from the select statement to it.
create or replace procedure AtlantisGames.GetAllGames (Games out sys_refcursor)
is
begin
open Games for select * from AtlantisGames.GAMECATALOG;
end GetAllGames;
If we look at the base code I wrote for getting the data from SQL Server, all I am doing is defining a list of type Games, assigning the values returned from stored procedure to it and then returning it.
GameCatalogRepository.cs
public List<GameCatalog> GetGameCatalog()
{
List<GameCatalog> games;
games = this._dbConnection.Query<GameCatalog>(sql:
"GetAllGames", commandType:
CommandType.StoredProcedure).ToList();
return games;
}
Using an integration test called TestGetAllGames I call the GetGameCatalog method from my repository and assert that the result is not null. The test passes.
GamesTests.cs
[TestClass]
public class GamesTests
{
[TestMethod]
public void TestGetAllGames()
{
// Arrange
IGameCatalogRepository repo = CreateRepository();
// Act
var games = repo.GetGameCatalog();
// Assert
games.Should().NotBeNull();
}
}
If I modified the connection string to execute the test against my Oracle database, I would get the below error.
Result Message:
Test method OracleWithDapperTests.GamesTests.TestGetAllGames threw exception:
Oracle.ManagedDataAccess.Client.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETALLGAMES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Remember how I said earlier that the only way to get data back from an Oracle stored procedure was to create a parameter of type sys_refcursor and then assign the values of the query to it? This has not been defined in our code yet. We need a dbtype of RefCursor, which is not available to us with vanilla Dapper. The fix is to download the Dapper.Oracle Nuget package to our project, call it to our repository, and then create an OracleDynamicParameters object where the out parameter can be defined. We then assign the object to param in our Query method. The results of the method get assigned to our games list, which is once again returned.
public List<GameCatalog> GetGameCatalog()
{
List<GameCatalog> games;
OracleDynamicParameters dynamicParameters = new
OracleDynamicParameters();
dynamicParameters.Add(name: ":Games", dbType:
OracleMappingType.RefCursor, direction:
ParameterDirection.Output);
games = this._dbConnection.Query<GameCatalog
("atlantisgames.getallgames", param: dynamicParameters,
commandType: CommandType.StoredProcedure).ToList();
//games = this._dbConnection.Query<GameCatalog>(sql:
//"atlantisgames.GetAllGames", commandType:
//CommandType.StoredProcedure).ToList();
return games;
}
The test will then work when run. If we put a break point at the return and then run our code in the debugger, we see our values being returned
There you have it! With an extra Nuget package, we can make Dapper work just as well with Oracle as it does with SQL Server.
Very nice explanation about using Dapper with sprocs. I noticed in your Add code you return the number of rows affected instead of the commonly autoincremented Id column that many tables use.
int rowsAffected = this._dbConnection.Execute(sql, dynamicParameters);
Often the C# code needs the Id value returned, but I’ve never seen a working Dapper example of this code. Do you have a working example of this? In my own code I just make a 2nd call to the Oracle sequence currval, which is of course two calls which seems wasteful.
this.db.Execute(“insert statement here”, myPersonObject);
var id = this.db.Query(“SELECT \”ISEQ$$_123456\”.currval FROM dual “).Single();
Do