Best practices for IDbConnection in ASP.NET MVC application

[Daniel Lo Nigro]

In an ASP.NET MVC application, is the best practice with OrmLite to pass around an IDbConnection, or an IDbConnectionFactory? I've seen some tutorials that show IDbConnectionFactory, but it doesn't seem very DRY to have using (IDbConnection db = dbFactory.OpenDbConnection()) in a bunch of different places.

Does anyone have a list of best practices for using ServiceStack in an ASP.NET (either MVC or WebForms) application?

[Demis Bellot]

Hi Daniel,

You pass around a IDbConnectionFactory (which is itself a singleton) because it itself doesn't hold a db connection open, so it's safe to reference forever. Whenever you need to use it you just open the DB Connection.

One way we can make this more DRY is to have a Lazy property in an IDisposable base class, see the implementation of ServiceStack's New API Service as an example:

    private IDbConnection db;

    public virtual IDbConnection Db

    {

        get { return db ?? (db = TryResolve<IDbConnectionFactory>().Open()); }

    }

Then in your Dispose method you can just close it if it's been opened: 

    public virtual void Dispose()

    {

        if (db != null)

            db.Dispose();

    }

Which lets you avoid the using statement, e.g:

public HelloService : Service 

{

   public object Get(Customer request)

   {

    return Db.Id<Customer>(request.Id);

   }

}

This works as by default ServiceStack will automatically dispose of any IDisposable services just after your service has been executed. 

Cheers,

On Fri, Dec 7, 2012 at 8:03 AM, Daniel Lo Nigro <dan...@d15.biz> wrote:

In an ASP.NET MVC application, is the best practice with OrmLite to pass around an IDbConnection, or an IDbConnectionFactory? I've seen some tutorials that show IDbConnectionFactory, but it doesn't seem very DRY to have using (IDbConnection db = dbFactory.OpenDbConnection()) in a bunch of different places.

Does anyone have a list of best practices for using ServiceStack in an ASP.NET (either MVC or WebForms) application?

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[Daniel Lo Nigro]

Thanks for your reply, I appreciate it :)

If I pass an IDbConnectionFactory around, won't that create multiple connections every time I open a new connection? Isn't this bad for performance? Or does it reuse the same connection? Say I have 10 separate methods that access the database and they're all called on one web page. Is there a performance difference between calling IDbConnectionFactory.OpenDbConnection() 10 times versus using the same IDbConnection 10 times?

This is what I originally had in my IoC configuration (I'm using Simple Injector):

// Connections

container.RegisterPerWebRequest<IDbConnectionFactory>(() =>

new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["Database"].ConnectionString,

                            MySqlDialect.Provider));

container.RegisterPerWebRequest<IDbConnection>(() => container.GetInstance<IDbConnectionFactory>().OpenDbConnection());

// Repositories

container.RegisterPerWebRequest<IBlogRepository, Daniel15.Web.Repositories.OrmLite.BlogRepository>();

And the repository class takes the IDbConnection in its constructor. Simple Injector handles injecting the IDbConnection into the constructor automatically, and also handles disposing the IDbConnection and IDbConnectionFactory at the end of the request. Is there a particular reason for passing the IDbConnectionFactory instead of just passing the IDbConnection?

Thanks for your help!

[Demis Bellot]

Yes, every time you call IDbConnectionFactory.OpenDbConnection() it will open and return a new ADO.NET DB connection. But what actually happens under the hood though is up to the Connection string and the ADO.NET provider. SQL Server for example by default will use connection pooling unless you explicitly disable it, see: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

But the lazy property example I show above doesn't open it up multiple times, it uses the open connection for the entire class and disposes it after the service has executed.

I personally will never auto wire an IDbConnection for the reasons I mention above, I don't want my services to reference an open connection or for the behavior to be dependent on the outside context, e.g. configuration of an IOC.