What is recommended lifetime of Database object in a web application?

Question

I consider using PetaPoco in a conventional web app. Saying conventional I mean

• handling requests in separate threads from a pool
• requests are quick: no long polling, streaming, etc
• but not necessarily ASP.NET, it may be for example Nancy

What should be the lifetime of the Database object, and that of the underlying DbConnection?

• globally static (I guess no, due to this answer)
• per-thread static, [ThreadStatic]
• per-request
• unit of work style: create and dispose as soon as the task is finished

I especially appreciate answers from those who run production apps with PetaPoco.

Answer 1

Per request is the way to go

• globally static: you guess right. Will bring problems accessing the same recordset and others. Definitely no.
• per-thread static. You should avoid [ThreadStatic] in Asp.net
• per-request: works great
• unit of work style: It works great also (not my preference)

Answer 2

Database is a cheap object to create, so I just create it as needed. I use PetaPoco (or more specifically AsyncPoco so I'm not tying up threads during I/O bound work) in a busy web app and this works great for me. Others do prefer per-request though, and that should also work well.

Answer 3

You need one object per thread, but you shouldn't associate the object with the thread itself, because handling a request can start in one thread and finish in another thread.

Using one object per request would normally be the best, at least for quick request handling. However that is not always simple. If you risk leaving objects without disposing them (for example if there is an exception), it's better to use one object for a limited task where you can make sure that it is disposed properly.