Connection is disposed automatically

Question

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);
    }

Answer 1

You declared your sqlConn as static readonly, which means there's only a single instance of it for the entire application. Wrapping it in using() means that after the very first request completes the sqlConn gets disposed of and subsequent requests fail with ObjectDisposedException.

To fix this, rewrite your code as follows:

var connectionString = 
    ConfigurationManager.ConnectionStrings["LogDbContext"].ConnectionString;
using(var sqlConn = new OracleConnection(connectionString))
{
    // ...
}

Now, as for you not wanting to open the connection each time, this is what you will have to do. Connections are precious, and have to be managed carefully: open as late as you need, close as soon as you don't need them anymore. You can always factor out your connection initialization logic into a separate method or go the Enterprise (r)(tm) way and inject it.

Answer 2

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.