15

I need to create a DAL and repositories using petapoco. The difficulty that comes in, is that I dont know how it manages its connections.

If I was using dapper I know how the connection process flows because I control it. I don't know what are the best practices in creating a DAL with petapoco.

 public class UserRepository
    {
        public IEnumerable<User> All()
        {
            var db = new PetaPoco.Database("Sqlite_Connection");//this line
            var s = db.Query<User>("SELECT * FROM Users");
            return s.ToList();
        }
    }

I would like to place var db = new PetaPoco.Database("Sqlite_Connection");//this line in my DALHelper class as a static property but I'm worried about scalability

David
  • 5,403
  • 15
  • 42
  • 72

2 Answers2

12

I don't recommend using a static since you can get errors like "There is already an open DataReader associated with this Command" because the same connection is used by different request accesing the same resource.

Two options:

1. Create the connection in a controller base class

public class BaseController : Controller 
{
  protected DatabaseWithMVCMiniProfiler _database;

  protected override void OnActionExecuting(ActionExecutingContext filterCon ) 
  {
    base.OnActionExecuting( filterCon );

    _database = new DatabaseWithMVCMiniProfiler( "MainConnectionString");

  }
}

2. Static method creating one connection per request

public static class DbHelper {
  public static Database CurrentDb() {
    if (HttpContext.Current.Items["CurrentDb"] == null) {
       var retval = new DatabaseWithMVCMiniProfiler("MainConnectionString");
       HttpContext.Current.Items["CurrentDb"] = retval;
       return retval;
    }
    return (Database)HttpContext.Current.Items["CurrentDb"];
  }
}
Eduardo Molteni
  • 38,786
  • 23
  • 141
  • 206
  • 1
    Thanks Eduardo, makes me confident with PetaPoco again, also just found this SO with suggestion to use 1 petapoco.database instance per request: http://stackoverflow.com/questions/7126205/how-can-integrate-mvcminiprofiler-with-petapoco-without-changing-the-sources – joeriks Apr 03 '12 at 16:24
  • 1
    PetaPoco is fabulous. I'm using it in several projects now and works very well in all kinds of scenarios. – Eduardo Molteni Apr 03 '12 at 16:37
3

A static property will be fine for the Initialization. PetaPoco will open and close the connection each time, unless you are using a transaction. This isn't usually an issue due to connection pooling.

If you are using this in a web application then you should instantiate one PetaPoco database per request.

Schotime
  • 15,707
  • 10
  • 46
  • 75