Will supplying a SQLCommand with a new SQLConnection call Dispose on the new SQL Connection?

Question

PREAMBLE

I understand that normally most SQL calls run thusly

using(var cnn = new DbConnection("YOURCONNECTIONSTRINGHERE") 
{
  cnn.Open(); //Open the connection.
  using(var cmd = new DbCommand("YourSQL", cnn)
  {
    cmd.ExecuteScalar; //Or whatever type of execution you want.
  }
}

This will properly dispose of both the connection and the command.

My Question: Will this code properly dispose of both objects?

  using(var cmd = new SqlCommand("YourSQL", new Connection("YOURCONNECTIONSTRINGHERE"))
  {
    cmd.ExecuteScalar; //Or whatever type of execution you want.
  }

In reality I'm using a method that provides and opens the connection.

public SqlConnection Connection()
{
    var product = new SQLConnection("ConnectionString");
    product.Open();
    return product;
}

So at the end of the day the call looks like this:

  using(var cmd = new SqlCommand("YourSQL", Connection())
  {
    cmd.ExecuteScalar; //Or whatever type of execution you want.
  }

I know the SqlCommand object will be disposed of but will the SQLConnection, created within the using parameter declaration, be disposed of? I've tried running some simple unit tests but it seems inconclusive.

This might be what you're looking for. If you look at [ExecuteReader()](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executereader?view=netframework-4.8) you'll see there's an overload that takes a [CommandBehavior](https://learn.microsoft.com/en-us/dotnet/api/system.data.commandbehavior?view=netframework-4.8) value. One of those behaviors is `CloseConnection` which closes the connection automatically when the `DataReader` is closed. — itsme86, Dec 24 '19 at 23:42
`Dispose` won't be called in your code, but eventually object will be destructed. But if you need to call `Dispose` - you can use `using` with your function as well - `using (var conn = Connection()) using (var cmd = ...` — Ulugbek Umirov, Dec 24 '19 at 23:45
`using` will only `Dispose()` the resource created/tracked by that `using` statement. That you happen to create a `Connection` inline and pass it as a parameter does not provide the guarantee of that `using` statement; it's just some ancillary object. That's why you always see one `using` statement each for the `Connection` and `Command`. — Lance U. Matthews, Dec 25 '19 at 00:08
Does this answer your question? [Dispose object that has been instantiated as method parameter c#](https://stackoverflow.com/questions/8765260/dispose-object-that-has-been-instantiated-as-method-parameter-c-sharp) or [Does “using” also dispose objects created in the constructor?](https://stackoverflow.com/q/37836565/150605) or [Does the using statement dispose only the first variable it create?](https://stackoverflow.com/q/17317251/150605) — Lance U. Matthews, Dec 25 '19 at 00:22

Hooman Bahreini · Accepted Answer · 2019-12-26T20:13:41.717

5

Will this code properly dispose of both objects?

using(var cmd = new SqlCommand("YourSQL", new Connection("YOURCONNECTIONSTRINGHERE"))
{
    cmd.ExecuteScalar; //Or whatever type of execution you want.   
}

The above code does not call Dispose() on the connection. The using block ensures that cmd.Dispose() is called immediately after the execution of the block, but the connection remains open. Since the connection has no object referencing it, it will be eventually closed/disposed by the Garbage Collector.

If you want to dispose the Command and Connection immediately, then try:

using (var con = Connection()) // <-- GetConnection() would be a better name
using (var cmd = new SqlCommand(con)
{
    cmd.ExecuteScalar; 
}

edited Dec 26 '19 at 20:13

answered Dec 25 '19 at 00:22

Hooman Bahreini

14,480
11
70
137

I'm aware that nesting them without brackets would dispose of it. It's a shame that it doesn't close the connection. I'm always looking for places to minimize my code while maintaining efficiency. Thanks for the explanation. I suppose I'll be nesting it. – Imbaker1234 Dec 25 '19 at 01:41
1

I don't know your use case... if you were using Entity Framework 6, then you could initialize `DbContext` with a connection... `DbContext` constructor has a flag called `contextOwnsConnection` if you set the flag, then disposing `DbContext` would dispose connection too [see here](https://learn.microsoft.com/en-us/ef/ef6/fundamentals/connection-management)... but there is no such flag for `SqlCommand`... – Hooman Bahreini Dec 25 '19 at 02:00
Good information to have for personal projects but we don't have heavy use of Entity Framework where I work. – Imbaker1234 Dec 25 '19 at 02:46

Will supplying a SQLCommand with a new SQLConnection call Dispose on the new SQL Connection?

1 Answers1