Connection is disposed automatically

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);
    }
Jon Skeet
people
quotationmark

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.

people

See more on this question at Stackoverflow