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
SQL

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

CSharp

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.

Performance

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.

12 thoughts on “Dapper – Micro ORM for Oracle and Microsoft .NET

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

  2. 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?

  3. 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?

  4. 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?

Leave a Reply