Dapper – Micro ORM for Oracle and Microsoft .NET

I have been on the hunt for an easier ORM tool to help with my day-to-day work. Till now the ones I have tested have fallen short of fulling one of criteria:

  1. Should work with Oracle
  2. Should support multiple REFCURSOR output parameters from Oracle Stored Procedures and OracleDbType’s
    We use stored procedures exclusively for our CURD operations, so this is the determining requirement and I do not like compromising having an abstract layer which does not support OracleDbType’s just because it has to work with other DB’s
  3. Should have decent performance for serving enterprise applications
    This need was the main reason I have stayed away from heavy weight ORM’s like NHibernate and Entity Framework
  4. Should be light weight and simple
  5. Preferable not a stale project
  6. Preferably open source

The 2 main micro ORMs I am taking into consideration are Dapper and PetaPoco.

I feel Dapper is more suited for my situation because it meets all of my above requirements and it works in the following scenarios

Integrating Oracle Stored Procedures

Getting the result out from an Oracle Stored Procedure with the help of Dapper seemed very easy after just adding a custom implementation of Dapper.SqlMapper.IDynamicParameters which supports Oracle.DataAccess.Client.OracleDbType.

Mapping Multiple RefCursors to Multiple Models

Dapper has a nice way to map multiple models to the procedure as shown below

PROCEDURE GetUserDetailsForPIDM (i_id	 IN	  NUMBER,
				o_user			 OUT SYS_REFCURSOR,
				o_roles			 OUT SYS_REFCURSOR);


public static User GetUserDetailsByID( int ID ) {
	User u = null;
	using ( OracleConnection cnn = new OracleConnection( ConnectionString ) ) {
		cnn.Open( );
		var p = new OracleDynamicParameters( );
		p.Add( "i_id", ID );
		p.Add( "o_user", dbType:OracleDbType.RefCursor, direction: ParameterDirection.Output );
		p.Add( "o_roles", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output );

		using ( var multi = cnn.QueryMultiple( "PKG_USERS.GetUserDetailsForID", param: p, commandType: CommandType.StoredProcedure ) ) {
			u = multi.Read<User>( ).Single( );
			u.Roles = multi.Read<UserRole>.ToList( );
	return u;

You can download the OracleDynamicParameters.cs file from Gist.


According to Dapper’s website and Steven Hollidge Dapper’s performs only next to hand written ADO.NET code. Apart from this Stackoverflow is using Dapper for their ORM needs. Considering their load I am convinced that Dapper is worth a try for our ORM needs as well.

  • asava samuel


    Here is an ORM that works with Oracle

    • http://blog.vijay.name Vijay

      Hi Asava,
      thanks for the link, but the product is not open source.

  • Aj

    Hi Vijay,
    Is OracleDynamicParameters production ready?
    i.e Has it been tested for memory leaks and performance?

    • http://blog.vijay.name Vijay

      Hi Ajay,
      It is tested, but it would better if you test it for yourself in your environment before deploying to production.

      • Aj

        will do. And I’ll keep you updated on my findings.

  • krishna

    Hi Vijay,

    I have a couple of questions.
    (1) How do you deal with Null values. In the following code, p_GPA is a double data type and I get an ORA-01722 (Invalid Number) error when I excecute the stored procedure.

    var p = new OracleDynamicParameters();

    p.Add(“:p_Gpa”, DBNull.Value);

    Also is there a way to populate a DataAdaptor?

  • http://none Rodolfo

    Thanks Man, you rule!

  • littleGreenDude

    Did you have to modify SQLmapper at all?

    I am getting an error – No overload for method ‘CreateParamInfoGenerator’ takes 2 arguments

    on the following line in the AddParameters method:

    appender = SqlMapper.CreateParamInfoGenerator(newIdent, false);

    Is there a specific version of dapper that I should be using?

    • Tim D

      Tim D.

      I got the same error. To correct it, just add another ‘false’ parameter. So it looks like this:

      appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, false);

  • littleGreenDude

    to get the dynamic parameters to add correctly, I had to switch from the Oracle.DataAccess.Client to the Oracle.ManagedDataAccess.Client. In doing this, it no longer connects to the database. I’m getting the following error: ORA-12154: TNS:could not resolve the connect identifier specified

    Does the connection string/configuration need to change?

  • caoyang

    Must I use ManagedDataAccess?

    • http://blog.vijay.name Vijay shankar ganesh K

      If you are developing Oracle based solutions using .NET framework it helps to keep the complete project stack in managed language including the database client libraries.

  • http://www.eka808.com Yoann

    Thanks for sharing this ! It will be very useful for me

  • http://gravatar.com/ivanbreslauer Bres

    Hello Vijay,
    how can I properly get the output parameters value out of the stored procedure? I’m getting null from the parameter, even though I’m sure that the SP is returning an out value.