SqlConnection closes unexpectedly inside using sta

问题:

I have an SQL connection service that I'm trying to build. I essentially do this:

var data = await GetDataFromFlatFilesAsync(dir).ConfigureAwait(false);
using (var conn = new SqlConnection(MyConnectionString))
{
    try
    {
        conn.Open();
        var rw = new SqlReaderWriter(conn);

        await DoStuffWithSqlAsync(rw, data).ConfigureAwait(false);
     }
     catch (Exception ex)
     {
         // Do exceptional stuff here
     }
}

DoStuffWithSqlAsync operates like this:

private async Task DoStuffWithSqlAsync(SqlReaderWriter rw, IEnumerable<Thing> data)
{
    await Task.WhenAll(data.Select(rw.RunQueryAsync)).ConfigureAwait(false);
}

And RunQueryAsync operates like this:

public async Task RunQueryAsync<T>(T content)
{
    // _conn is assigned to in the constructor with conn
    try
    {
        var dataQuery = _conn.CreateCommand();
        dataQuery.CommandText = TranslateContentToQuery(content);
        await dataQuery.ExecuteNonQueryAsync().ConfigureAwait(false);
    }
    catch (Exception ex)
    {
        // Do exceptional stuff here
    }
}

The problem I'm running into is that DoStuffWithSqlAsync continuously fails with a System.InvalidOperationException stating that conn is in a closed state. However, when I examine the stack trace, I find execution is still inside the using statement. I also know that conn is not closed anywhere inside this operation as execution must necessarily return to the statement and any exception inside should be bubbled up to the enclosing try-catch and caught there. Additionally, the connection is pooled and MARS is enabled. So then, why is this being closed?

UPDATE: It was pointed out that I hadn't opened my connection. I have done so but the error persists except now _conn.State is set to Open.

UPDATE: I was having an issue wherein I used await conn.OpenAsync.ConfigureAwait(false); which would not block execution. So, when I tried to connect to the database, I would get an exception stating that the connection state was closed but by that point it would have opened so on examination it would show Open. It was suggested that I make the calling method async void but as the application is console, not UI I don't think that will help. I have since reverted to using the synchronous method.

回答1:

You just need to open the connection:

try
{
  conn.Open() //<--add this
  var rw = new SqlReaderWriter(conn);
  await DoStuffWithSqlAsync(rw, data).ConfigureAwait(false);
}