Should I create a PetaPoco database class once per query or every time I need it?

Question

With PetaPoco, how should I handle creating a Database class? My application is likely to use various repositories (not exactly the DDD repository, more like a gateway repository) to encapsulate requests.

Since I will have several repositories that need to access the same database connection, I was thinking of creating a base class that created the database class in the constructor and calls Dispose in its destructor and just calls this object in all the derived classes, therefore I think I won’t need to use the block in this scenario (because when the class goes out of scope, it closes the database itself).

I also considered just creating a database object in global.axax using the Application_BeginRequest method, so it is available on every page, but I'm not quite sure how it works (I think I need some kind of DatabaseManager that runs it? I just saw this approach used with things like NHibernate and RavenDB), and this seems to lead to using the database object on the ASPX page itself, rather than through a storage / data-class class that seems smelly.

Any suggestions, which of these approaches, if any, will work best? I also saw that PetaPoco supports a “shared connection”, so I also want to take a look at minimizing the number of open connections to the database?

Answer 1

Usually I create one Database for each query. This is a lightweight object. Or in ActionFilter use the base class Controller .