SqlConnection closes unexpectedly inside using statement

Question

I have an SQL connection service that I am trying to create. I basically 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 works as follows:

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

And RunQueryAsync works as follows:

 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 ran into is that DoStuffWithSqlAsync constantly fails with a System.InvalidOperationException , stating that conn is in a closed state. However, when I look at the stack trace, I find that the execution is still inside the using statement. I also know that conn does not close anywhere inside this operation, since execution must necessarily return to the statement, and any exception inside should be bubbled to the enclosing try-catch and caught there. In addition, the connection is combined and MARS is allowed. So why is it closing?

UPDATE: It was pointed out that I did not open my connection. I did this, but the error persists, but now _conn.State set to Open .

UPDATE: I had a problem in which I used await conn.OpenAsync.ConfigureAwait(false); that did not block execution. Therefore, when I tried to connect to the database, I would get an exception stating that the connection status is closed, but by the time it opens, it will show Open in the exam. It has been suggested that I call the async void method, but since the application is a console and not a user interface, I do not think this will help. Since then, I have returned to using the synchronous method.

Answer 1

You just need to open the connection:

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