0

I've got a problem with the OdbcConnection in c#. I've written a class that wraps my connections and commands into a generic interface. The code looks basically like this (I've left out some unimportant parts)

//connection, command, _connectionType and _commandType are class members
private void getConnection(ref String commandText, out DbConnection connection, out DbCommand command, params DbParameter[] parameter)
{
    connection = Activator.CreateInstance(this._connectionType) as DbConnection;
    connection.ConnectionString = this._connectionString;
    connection.Open();
    command = Activator.CreateInstance(this._commandType) as DbCommand;
    command.Connection = connection;
    command.CommandText = commandText;
}

//Other methods use the DbCommand for SELECTS, UPDATES, etc...

private void disposeConnection()
{
    this._command.Dispose();
    this._connection.Close();
    this._connection.Dispose();

    this._command = null;
    this._connection = null;
}

I open a connection, execute my desired command and the call disposeConnection. But our Database (SAP Sybase SQL ver.11,16,17) still shows the connection in the "PREFETCH" state...

The disposeConnection is called inside a finally block after the SQL command is executed.

Why is the connection not closed correctly?

JoeJoe87577
  • 512
  • 3
  • 17
  • [Connection pooling](http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc39001.0700/html/prjdbc0700/CHDCAECF.htm)? – stuartd Nov 23 '15 at 12:37
  • Have you tried simpliest example without your wrapper? Just console app with 1. Create connection. 2. Create command. 3. Execute command. 4 Close/dispose everything. – Timur Mannapov Nov 23 '15 at 12:39
  • 1
    Make use of `using` block which will take care of closing and disposing the connection for you. – Izzy Nov 23 '15 at 12:40
  • @stuartd I'll review this with our db admin, thanks. @Izzy Thats not an option, I'm using the `getConnection` Method in many other methods... – JoeJoe87577 Nov 23 '15 at 14:09
  • Connection Pooling is not the reason, I've disabled it in my connection String: `Dsn={0};Uid={1};Pwd={2};Pooling=false;Max Pool Size=1;` – JoeJoe87577 Nov 23 '15 at 14:20

1 Answers1

1

I've finally found the solution. I've left the DbDataReader open. I had to wrap all the DbDataReaders inside a using block. Now my connections are closed when I call _connection.close().

So it doesn't matter if the connection is in the closed state, if any other object is acessing the database, the connection is not realy closed.

JoeJoe87577
  • 512
  • 3
  • 17