Using on an object created in a different method

Question

I'm trying to make a generic SQL call, which led me to an interesting question. I have a method that executes the SQL, and returns a SQLDataReader.

    private SqlDataReader ExecuteSql(SqlCommand command)
    {
        using (var connection = new SqlConnection(ConnectionText, Credentials))
        {
            command.Connection = connection;
            connection.Open();
            return command.ExecuteReader();
        }
    }

The calling command takes the reader and processes the returned data correctly. However, knowing that the reader needs to be disposed of, I enclosed it in a using statement.

        using (SqlDataReader reader = ExecuteSql(command))
        {
            while (reader.Read())
            {
                try { ... }
                catch(Exception e) { ... }
            }
        }

I think Dispose should be called on the SqlDataReader at the end of the using statement despite where in the code it was created. However, I have not been able to find anything that specifically confirms this.

Generalizing, can the using statement be successfully used on an object that was created elsewhere in the code?

As a side note, I do realize that if the SqlDataReader was created as an object in the ExecuteSql method rather than returned directly, then there could be an issue with it throwing an exception while in the ExecuteSql method and not being disposed of.

In general *no characteristic of an **object** depends on the code location in which it is created*. However that is not true of **variables**; characteristics of variables such as guaranteed lifetime *do* depend on where the variable is created. — Eric Lippert, Oct 25 '19 at 19:14

Erik Philips · Answer 1 · 2019-10-25T19:06:19.617

4

You can accomplish this by passing an Action like so:

private void ExecuteSql(SqlCommand command, Action<SqlDataReader> action)
{
    using (var connection = new SqlConnection(ConnectionText, Credentials))
    {
        command.Connection = connection;
        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            action(reader);
        }
    }
}

Then the calling function:

var myCommand = //...

int id;

ExecuteSql(myCommand, (reader) => {
  id = reader.GetInt32(0);
});

Now any caller doesn't need to know if they have to dispose of it or not and your connection will be disposed after the method has done it work on the reader.

edited Oct 25 '19 at 19:06

answered Oct 25 '19 at 18:59

Erik Philips

53,428
11
128
150

1

While [Zdeslav](https://stackoverflow.com/a/58564105/18192) answered the OP's specific question, I think the architecture suggested in this answer is far superior to the approach the OP is taking. That said, I would rather use a generic `private T ExecuteSql` that accepts a `Func`. You can get a bit fancier by having the `Func` process only a single row, than having various collection-specific variations on `ExecuteSql`. – Brian Oct 25 '19 at 20:28
@Brian I'm in complete agreement. Without any context, this solution isn't limited to `SqlDataReader` as it can be used with anything disposable, which is more of where I was going with it based on the Title to the question. – Erik Philips Oct 25 '19 at 20:37

score 1 · Accepted Answer · answered Oct 25 '19 at 18:45

1

It's OK to use the object created in another method in the using statement. However, in your case, you are using an SqlDataReader which uses the SqlConnection which is disposed at the end of the ExecuteSql call.

As explained here, you need a valid connection object to use the SqlDataReader

answered Oct 25 '19 at 18:45

Zdeslav Vojkovic

14,391
32
45

Thank you for the answer, and pointing out the error that I didn't catch, but should have. – Tim Oct 25 '19 at 18:48

Using on an object created in a different method

2 Answers2