System.Data.Odbc - Connection Object Not Closed When DataReader Object is Disposed/Closed Upon Command Execution

[ashishoffline]

Description

When accessing a Vertica Database via System.Data.Odbc, the expected behavior of closing the underlying connection upon calling ExecuteReaderAsync(CommandBehavior.CloseConnection) is not observed. Despite disposing of the DbDataReader object returned by this method, the connection.State remains Open, indicating that the associated connection is not being closed as intended.

Reproduction Steps

public static async Task ExecuteReaderAsync()
{
    string sqlQuery = "SELECT {ColumnName} FROM {SchemaName.TableName};";

    var connection = new OdbcConnection("Driver=Vertica;{VerticaDatabase_Connection_String}");

    DbDataReader dataReader;
    await using (var command = connection.CreateCommand())
    {
        command.CommandText = sqlQuery;
        command.CommandType = CommandType.Text;

        await connection.OpenAsync();
        dataReader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
    }

    using (dataReader)
    {
        while (await dataReader.ReadAsync())
        {
            Console.WriteLine(dataReader["ColumnName"]);
        }
    }

    Console.WriteLine(connection.State);
}

This outputs:-

Expected behavior

When the ExecuteReaderAsync method is invoked with the CommandBehavior.CloseConnection enum, the underlying connection should automatically close upon disposing or closing the DbDataReader object returned by this method.

Actual behavior

Upon calling the ExecuteReaderAsync method with the CommandBehavior.CloseConnection enum, the underlying connection remains open even after disposing or closing the DbDataReader object returned by this method.

Regression?

I've tested two versions of System.Data.Odbc (6.0.1 & 8.0.0), and both exhibit the same issue.

Known Workarounds

No response

Configuration

• Which version of .NET is the code running on? -> .NET 6
• What OS and version ? -> Windows 11
• What is the architecture ? -> x64

Other information

No response

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Description

When accessing a Vertica Database via System.Data.Odbc, the expected behavior of closing the underlying connection upon calling ExecuteReaderAsync(CommandBehavior.CloseConnection) is not observed. Despite disposing of the DbDataReader object returned by this method, the connection.State remains Open, indicating that the associated connection is not being closed as intended.

Reproduction Steps

`public static async Task ExecuteReaderAsync()
{
string sqlQuery = "SELECT {ColumnName} FROM {SchemaName.TableName};";

var connection = new VerticaConnection("Driver=Vertica;{VerticaDatabase_Connection_String}");

DbDataReader dataReader;
await using (var command = connection.CreateCommand())
{
    command.CommandText = sqlQuery;
    command.CommandType = CommandType.Text;

    await connection.OpenAsync();
    dataReader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
}

using (dataReader)
{
    while (await dataReader.ReadAsync())
    {
        Console.WriteLine(dataReader["ColumnName"]);
    }
}

Console.WriteLine(connection.State);

}`

This outputs:-

Expected behavior

When the ExecuteReaderAsync method is invoked with the CommandBehavior.CloseConnection enum, the underlying connection should automatically close upon disposing or closing the DbDataReader object returned by this method.

Actual behavior

Upon calling the ExecuteReaderAsync method with the CommandBehavior.CloseConnection enum, the underlying connection remains open even after disposing or closing the DbDataReader object returned by this method.

Regression?

I've tested two versions of System.Data.Odbc (6.0.1 & 8.0.0), and both exhibit the same issue.

Known Workarounds

No response

Configuration

• Which version of .NET is the code running on? -> .NET 6
• What OS and version ? -> Windows 11
• What is the architecture ? -> x64

Other information

No response

Author:	ashishoffline
Assignees:	-
Labels:	area-System.Data, untriaged
Milestone:	-

[dotnet-policy-service]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

[dotnet-policy-service]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

[roji]

@ashishoffline apologies for taking so long to answer this.

Looking at your code above, I don't see ODBC being used anywhere (as is implied in the issue title) - you seem to be using a Vertica ADO.NET database driver directly (ODBC use would have you using OdbcConnection instead of VerticaConnection).

As such, the behavior you're seeing here is fully implemented in the Vertica ADO.NET driver, and has nothing to do with the .NET runtime. You'll need to raise this with the maintainers of that driver.

I'll go ahead and close this, but if you have further questions, don't hesitate to post back here.

[ashishoffline]

@roji Apologies for the incorrect code snippet. I have now updated it with the correct code, and the issue I described still persists.

I was testing whether the problem lies specifically with the Odbc or Vertica ADO.NET Driver also, and I mistakenly included the wrong code snippet. Issue is only with the System.Data.Odbc

[roji]

@ashishoffline ok, thanks. Can you please try this with a different ODBC provider and report on the results?

[ashishoffline]

@roji Just to clarify, are you asking me to try a different package other than System.Data.Odbc, or to test with a different database (e.g., MySQL, SQL Server, etc.)?