how to create a DAL using petapoco [closed]

Question

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

Answer 1

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"];
  }
}

Answer 2

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.