Dapper and OrmLite IDBConnectionFactory in ServiceStack

dapper oracle ormlite-servicestack servicestack

Question

I am impressed with the speed that I can create services using ServiceStack, but for a while now I have not been able to grasp the concept of using OrmLite and Dapper simultaneously in my project. I am registering a IDbConnectionFactory like this in my Global.asax.cs

    public override void Configure(Funq.Container container)
        {
            var dbConnectionFactory =
                new OrmLiteConnectionFactory(ConfigUtils.GetConnectionString("test"), true, OracleDialect.Provider);

            container.Register<IDbConnectionFactory>(dbConnectionFactory);          
            container.Register<ISubscriberRepository>(
                c => new SubscriberRepository(c.Resolve<IDbConnectionFactory>()));
        }

That works fine for OrmLite but it is a not as simple for Dapper. Maybe I am just thinking this should be more convenient than it really is. In my repository I am trying to call a Oracle stored procedure. That is my main reason for using Dapper and not OrmLite for this process. This is my repository:

    public class SubscriberRepository : ISubscriberRepository {
    public SubscriberRepository(IDbConnectionFactory conn) {
        _conn = conn;
    }

    public IDbConnectionFactory _conn { get; set; }

    public SubscriberResponse GetSubscriber(SubscriberRequest request) {
        using (IDbConnection db = _conn.OpenDbConnection()) {
            var resp = new SubscriberResponse();

            List<Subscriber> s = db.Select<Subscriber>(
                q => q.Subscribernum == request.Subscribernum &&
                     q.Personcode == request.Personcode &&
                     q.Clientcode == request.Clientcode);

            resp.Subscriber = s[0];

            return resp;
        }
    }


    public SubscribersResponse SearchSubscribers(SubscribersRequest request) {
        var response = new SubscribersResponse();

        using (var cnn = new OracleConnection("this is my conneciton string")) {
            cnn.Open();

            var p = new OracleDynamicParameters();
            p.Add("@username", "uname", OracleDbType.Varchar2);
            p.Add("@Subscribernum", "", OracleDbType.Varchar2);
            p.Add("@Personcode", "", OracleDbType.Varchar2);
            p.Add("@Lastname", "TEST", OracleDbType.Varchar2);
            p.Add("@Firstname", "HA", OracleDbType.Varchar2);
            p.Add("@Mi", "", OracleDbType.Varchar2);
            p.Add("@Dob", null, OracleDbType.Date);
            p.Add("@MaxResults", 200, OracleDbType.Int32);
            p.Add("@Curs", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

            using (SqlMapper.GridReader multi = cnn.QueryMultiple("SEARCHSUBSCRIBER", p,
                                                                  commandType: CommandType.StoredProcedure)) {
                List<SearchSubscriberResults> r = multi.Read<SearchSubscriberResults>().ToList();
                response.Results = r;
            }
        }
        return response;
    }
}

This works. But it isn't really using the IDbConnectionFactory at all in the SearchSubscribers function. I don't want to look at connection strings in my repository really since I could really register them all up front in the service itself.

I tried to use ServiceStack.Razor.Dapper.SqlMapper.QueryMultiple() but that doesn't work because I can't map the Oracle sys_refcursor back to anything without using the OracleDynamicParamaters workaround.

So, my question is can I create a connection for Dapper from the IDBConnectionFactory?

Thanks!

Popular Answer

I haven't used Oracle in .NET (not since my Perl days), but OracleConnection implements the interface IDbConnection.

You should be able to cast the db connection you grab from:

IDbConnection db = _conn.OpenDbConnection()

and cast it to OracleConnection (assuming the OrmLite provider creates that same instance).

var cnn = db as OracleConnection;

... then you can try calling all that Oracle-specific stuff, like OracleDynamicParameters.

You may have to set this in AppHost.Configure() or somewhere:

OrmLiteConfig.DialectProvider = new OracleOrmLiteDialectProvider();


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why