CODEWITHSUNDEEP
c#.net
Brijesh Mishra
Brijesh Mishra

Reputation: 2748

How to close a .NET ODBC connection?

I am using ODBC to connect to a Sybase databse. The problem is the connection doesn't close even after it's is scope complete, and I see around 200 connection open in the database when I run sp_who. I tried enabling the connection pool, but that doesn't 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 which I use 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);
}

Upvotes: 4

Views: 4505

Answers (5)

André Sobreiro
André Sobreiro

Reputation: 51

ado.net keeps connections opened to reuse than, it´s called connection pool. I think for odbc connections, pooling are configured under odbc settings in windows. for other kinds of connection like SqlConenction, you can configure pooling feature in connection string.

hope it helps.

Upvotes: 0

Anuraj
Anuraj

Reputation: 19618

I am 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;
}

Upvotes: 0

rotman
rotman

Reputation: 1651

Connection to database would be closed automatically when the using block ends. You don't have to do anything at all.

Upvotes: 0

sajoshi
sajoshi

Reputation: 2763

Try to put a try.. catch and finally...

Under Finally explicitly check if the connection.state is not closed.. close it..

Upvotes: 0

Peter
Peter

Reputation: 14518

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

Upvotes: 3

Related Questions