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

Deirdredeism

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

Parliamentarianism

Thanks Eduardo, makes me confident with PetaPoco again, also just found this SO with suggestion to use 1 petapoco.database instance per request: #7126705 – Thersathersites

PetaPoco is fabulous. I'm using it in several projects now and works very well in all kinds of scenarios. – Parliamentarianism

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.

Particle

I am using it this way in production to handle nested transaction between DAL operations and it is working fine. – Tutorial

I (and others) have found that using a static (as in per application) have problems. See my answer. – Parliamentarianism