this may seem trivial but it's really bothering me. I have started today using Dapper within an MVC project and created a very simple POCO object; when I run this project thought, i get the following error message:
Cannot access a disposed object
Object name: 'OracleConnection'.
Here is the code:
public class MyController : Controller
{
readonly IDbConnection sqlConn = new OracleConnection(ConfigurationManager.ConnectionStrings["LogDbContext"].ConnectionString);
readonly string selectLog = "select * from LOG";
readonly string insertLog = "insert into LOG (ID, Address) values (:ID, :Address)";
// GET: Log
public ActionResult Index()
{
using (sqlConn)
{
sqlConn.Open();
//IEnumerable log = sqlConn.Query(selectLog);
IEnumerable<Log> log = sqlConn.Query<Log>(selectLog);
foreach (var item in log)
{
Console.WriteLine(item.ToString());
}
}
return View();
}
public ActionResult Create()
{
using (sqlConn)
{
sqlConn.Open();
var log = new Log()
{
ID = 1,
Address = "test"
};
sqlConn.Execute(insertLog, log);
}
return View();
}
}
it seems that placing the "sqlConn" into the using statement is making it dispose automatically so when the function is running it again it can't work on the connection.
How can I prevent this? I'd prefer not to open and close the connection manually each time I need it.
UPDATE
Using all the help provided from the answers below (all correct) I ended up using a constructor for the class to instantiate a new connection every time the class had to be used.
//removed the wrong static attribute and the instantiation
readonly IDbConnection sqlConn;
readonly string selectLog = "select * from LOG";
readonly string insertLog = "insert into LOG (ID, Address) values (:ID, :Address)";
// Created a constructor to instantiate the connection everytime the controller gets called
public LogController()
{
sqlConn = new OracleConnection(ConfigurationManager.ConnectionStrings["LogDbContext"].ConnectionString);
}
it seems that placing the "sqlConn" into the using statement is making it dispose automatically
Yes, that's what the using
statement is for.
How can I prevent this? I'd prefer not to open and close the connection manually each time I need it.
I'd strongly recommend that you do open and close the connection each time you need it - but use a local variable for it, not a field. That way each operation gets a separate logical connection, so you don't need to worry about threading issues etc. Let the connection pool take care of making this efficient. I suspect you're concerned about opening a "physical" connection (making a new TCP/IP connection or whatever) on each call - but the connection pool is there to make sure that that doesn't happen more than it needs to.
Just create a new connection within the method:
using (var connection = new OracleConnection(...))
{
...
}
... and measure performance to check that it's satisfactory. Don't start guessing about whether you'll have a problem (and resorting to poor workarounds which cause more problems than they solve).
As an alternative to using new
, the controller constructor could take a connection provider and ask that for a new connection, but fundamentally it's about creating a new disposable connection each time.
If for some reason you really, really don't want to dispose of the connection, just remove the using
statement - but be very aware that you'll need to handle concurrency yourself. You almost certainly don't want to do that.
See more on this question at Stackoverflow