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

1
2
3

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

CSharp

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

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.