How to close a .NET ODBC connection?

Question

I am using ODBC to connect to a Sybase database. The problem is that the connection does not close even after the region is completed, and I see about 200 connections open in the database when sp_who starts. I tried to enable the connection pool, but that does not help either.

  using(var connection = GetOdbcConnection()) { connection.Open(); using (var cmd = new OdbcCommand(query, connection)) { var reader = cmd.ExecuteReader(); if (reader.Read()) { long textLen = reader.GetChars(0, 0, null, 0, 0); } reader.Close(); } } 

The connection string used is value="Driver={Adaptive Server Enterprise};app=xxx;server=xxxx;port=xxxx; db=xxx;uid=xxx;pwd=xxxx;textsize=2097152" .

Update:

 public static OdbcConnection GetOdbcConnection() { string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); return new OdbcConnection(connectionString); } 

Answer 1

Have you tried connection.Close() ?

  using(var connection = GetOdbcConnection()) { connection.Open(); using (var cmd = new OdbcCommand(query, connection)) { var reader = cmd.ExecuteReader(); if (reader.Read()) { long textLen = reader.GetChars(0, 0, null, 0, 0); } reader.Close(); } // Close the connection connection.Close(); } 

Answer 2

Try to try .. catch and finally ...

At the end In conclusion, explicitly check if connection.state is closed .. close it.

Answer 3

The database connection will be automatically closed when the using block completes. You do not have to do anything.

Answer 4

I'm not sure, but I think the GetOdbcConnection function is the culprit. Try this modified version of the function.

 private static OdbcConnection _Connection; public static OdbcConnection GetOdbcConnection() { string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); _Connection = new OdbcConnection(connectionString); return _Connection; }