CODEWITHSUNDEEP
c#using
Tim
Tim

Reputation: 2911

Using on an object created in a different method

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.

Upvotes: 1

Views: 81

Answers (2)

Erik Philips
Erik Philips

Reputation: 54638

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.

Upvotes: 4

Zdeslav Vojkovic
Zdeslav Vojkovic

Reputation: 14591

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

Upvotes: 1

Related Questions