C#•2d ago

SqlConnection Connection Disposed

Hi all, I have class library which provides a custom type called SqlServerAccessor This custom type wraps a SqlConnection and its ctor accepts a connection string, news up a SqlConnection, and calls _connection.Open(). This type implements IDisposable and is intended to be used as follows:

try
{
    using var sql = new SqlServerAccessor(connectionString);
    DataTable result = sql.ExecuteQuery(...);
    // Do something with the result
}
catch (Exception ex)
{
    // Sql connection failed
}

try
{
    using var sql = new SqlServerAccessor(connectionString);
    DataTable result = sql.ExecuteQuery(...);
    // Do something with the result
}
catch (Exception ex)
{
    // Sql connection failed
}

Internally, I have a method called EnsureNotDisposed() to handle use after the type has been disposed which throws an InvalidOperationException stating the underlying SqlConnection has been disposed. Using the code sample from above, this catch is always hit and the sql connection is always closed. There does not seem to be a "keep alive" function or anything - my understanding is that SqlConnection should keep its connection open until it is manually closed. I could connect and disconnect for each operation, which might resolve the issue, but the intent is for it to be used in a small using scope like this and calling Dispose() or Close() is responsible for closing the connection. Any idea why the connection is being closed immediately after leaving the ctor?

8 Replies

surf68•2d ago

a SqlConnection will remain open until it is closed or disposed of, yes (ignoring pooling for the moment) you'd have to share the rest of the code for me to have any useful input as to what's going on, though.

Foxtrek_64OP•2d ago

Nothing particularly special here, but I can share. https://gist.github.com/Foxtrek64/ba9864e7c65bc9e8b42c7702d493a6dc The interfaces are just for describing the features of the accessor. This library is intended to implement several types, like SqlServer and Mongo and a few others, but that's not particularly important. None of the interfaces here are DIMs.

Gist

SqlServerAccessor.cs

GitHub Gist: instantly share code, notes, and snippets.

surf68•2d ago

does that conditional on 137 not need inverting?

Foxtrek_64OP•2d ago

That's a good catch Doesn't seem to pass a unit test though. The test is pretty rudimentary - it checks the number of rows in a table, attempts to insert a row, checks the number of rows again, and asserts the counts are not equal (I'm sure there are better ways, it's just what worked in the few seconds that I was writing this test). Before and after the insert both have the same number of rows.

surf68•2d ago

that being a new issue, not the original one with the exception being thrown?

Foxtrek_64OP•2d ago

As far as I can see, I am no longer getting the exception. Let me look at this test logic and see if I can't further isolate where it is failing

surf68•2d ago

alright. just to add to the original issue, you don't need to worry about opening and closing connections frequently. unless you need to use the exact same connection (for transactions), it's not an issue. the network connections to the server are pooled behind the scenes so opening and closing a SqlConnection is not, for the most part, an expensive operation.

Foxtrek_64OP•2d ago

I've got the test to a state where it's passing. Thanks for your help.

Gaming

Programming

SqlConnection Connection Disposed