SqlConnection在using语句中意外关闭 [英] SqlConnection closes unexpectedly inside using statement

问题描述

我有一个要构建的SQL连接服务.我基本上是这样做的:

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的运行方式如下:

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

RunQueryAsync的运行方式如下:

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
    }
}

我遇到的问题是DoStuffWithSqlAsync连续失败，并显示System.InvalidOperationException，表明conn处于关闭状态.但是，当我检查堆栈跟踪时，发现执行仍然在using语句内.我也知道conn在此操作中的任何地方都没有关闭，因为执行必须返回到该语句，并且其中的任何异常都应冒泡到封闭的try-catch并在那里捕获.此外，连接被池化并启用了MARS.那么，为什么要关闭它呢?

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?

更新:有人指出我尚未打开连接.我这样做了，但是错误仍然存​​在，除了现在将_conn.State设置为Open.

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.

更新:我遇到一个问题，其中使用了await conn.OpenAsync.ConfigureAwait(false);，它不会阻止执行.因此，当我尝试连接到数据库时，我会得到一个异常，指出连接状态已关闭，但到那时它已经打开了，因此在检查时会显示打开".有人建议使调用方法异步无效，但由于应用程序是控制台，而不是UI，所以我认为这不会有所帮助.此后，我恢复使用同步方法.

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.

推荐答案

您只需要打开连接:

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

这篇关于SqlConnection在using语句中意外关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！