Why do I need to use odbcconnection.open inside a using block

Question

I'm creating an application where I connect to an Access database and do several updates. Since I'm not a database programmer this is also a learning experience. I found the following code online but it didn't work until I added the connection.open() line.

So here's my question. I thought that like in other using cases like creating a file it would automatically open the connection and then dispose at the last }. Why did I have to explicitly call out the open command after creating a new connection?

private static void GetAllTableAndColumnNames(string connectionString)
{
    using (OdbcConnection connection =
        new OdbcConnection(connectionString))
    {
        connection.Open();
        DataTable tables = connection.GetSchema("Tables");
        DataTable columns = connection.GetSchema("Columns");

        foreach (DataRow row in columns.Rows)
        {
            Console.WriteLine(row["COLUMN_NAME"].ToString());
            Console.WriteLine(row["TABLE_NAME"].ToString());
        }
        Console.Read();
    }
}

Here's the error I got on runtime without the open command.

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: Invalid operation. The connection is closed.

http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception — Anant Dabhi, Jan 29 '14 at 15:07

score 1 · Accepted Answer · answered Jan 29 '14 at 15:56

When the code reaches the end of a using block and it knows that the Connection object is about to be destroyed it "does you a favour" by ensuring that the connection is properly closed. That is because simply destroying the Connection object without notifying the server would be inconsiderate: server connections are often precious commodities and leaving an orphaned connection "open" on the server would be a Bad Thing.

On the other hand, automatically opening a connection when the Connection object is created (i.e., the using statement itself) would not necessarily be a Good Thing. Perhaps you want to create your Connection object, and then create a whole bunch of other objects that depend on the Connection object (e.g., Command, DataAdapter, etc.). Does the connection actually need to be open while that is taking place? If not, then having the connection open for the whole time might also be inconsiderate if you are connecting to a busy server.

Or, to put it another way, there are very legitimate reasons why a Connection object might switch states between "open" and "closed" while it exists, but the only state it should be in at the moment of its demise is "closed". That's why there is the "auto-close" behaviour but not a corresponding "auto-open".

score 0 · Answer 2 · answered Jan 29 '14 at 15:55

Using clause is designed to close automaticlly resources when the variable is out of scope. The behaviour in the constructor depends on the implementation: DbConnection descendants don't open the conection in the constructor.

I think that this is because constructor overloads: some overloads doesn't take arguments, so can't open the database.

Why do I need to use odbcconnection.open inside a using block

2 Answers2