Refactor code into using statement

Question

I have a dal layer with lots of methods, all of them call stored procedures, some return lists (so with a use of SqlDataReader), others only a specific value.

I have a helper method that creates the SqlCommand:

    protected SqlCommand CreateSprocCommand(string name, bool includeReturn, SqlDbType returnType)
    {
        SqlConnection con = new SqlConnection(this.ConnectionString);
        SqlCommand com = new SqlCommand(name, con);
        com.CommandType = System.Data.CommandType.StoredProcedure;

        if (includeReturn)
            com.Parameters.Add("ReturnValue", returnType).Direction = ParameterDirection.ReturnValue;

        return com;
    }

Now my average (overly simplified) method body look like:

SqlCommand cmd = CreateSprocCommand("SomeSprocName"); //an override of the above mentioned method
try {
   cmd.Connection.Open();
   using (var reader = cmd.ExecuteReader()) {
       //some code looping over the recors
   }
   //some more code to return whatever needs to be returned
}
finally {
   cmd.Connection.Dispose();
}

Is there a way to refactor this, so that I won't lose my helper function (it does quite a bit of otherwise repetitive work), and yet be able to use using?

Jon Skeet · Accepted Answer · 2009-05-26 08:55:05Z

13

One way is to change it from returning a command to taking a delegate which uses the command:

protected void ExecuteSproc(string name,
                            SqlDbType? returnType,
                            Action<SqlCommand> action)
{
    using (SqlConnection con = new SqlConnection(this.ConnectionString))
    using (SqlCommand com = new SqlCommand(name, con))
    {
        con.Open();
        com.CommandType = System.Data.CommandType.StoredProcedure;

        if (returnType != null)
        {
            com.Parameters.Add("ReturnValue", returnType.Value).Direction = 
                ParameterDirection.ReturnValue;
        }
        action(com);
    }
}

(Note that I've also removed the includeReturn parameter and made returnType nullable instead. Just pass null for "no return value".)

You'd use this with a lambda expression (or anonymous method):

ExecuteSproc("SomeName", SqlDbType.DateTime, cmd =>
{
    // Do what you want with the command (cmd) here
});

That way the disposal is in the same place as the creation, and the caller just doesn't need to worry about it. I'm becoming quite a fan of this pattern - it's a lot cleaner now that we've got lambda expressions.

edited May 26, 2009 at 8:55

answered May 26, 2009 at 8:29

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sam Saffron Over a year ago

It probably makes sense to open the connection as a side effect in this instance ... (since its being closed as a side effect)

Gideon Over a year ago

Great, had already included the opening of the connection! Will start refactoring +/- 200 methods...

rism Over a year ago

We need a new word to describe ideas like this.. something along the lines of "superfablutastic"

Rashmi Pandit · Accepted Answer · 2009-05-26 08:35:17Z

3

You can do this:

protected static SqlCommand CreateSprocCommand(SqlConnection con, string name, bool includeReturn, SqlDbType returnType)
{
    SqlCommand com = new SqlCommand(name, con);
    com.CommandType = System.Data.CommandType.StoredProcedure;

    if (includeReturn)
        com.Parameters.Add("ReturnValue", returnType).Direction = ParameterDirection.ReturnValue;

    return com;
}

and call it like this:

using (SqlConnection con = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = CreateSprocCommand(con, "SomeSprocName", true, SqlDbType.Int)
{
    cmd.Connection.Open();
    using (var reader = cmd.ExecuteReader())
    {
        //some code looping over the recors
    }
    //some more code to return whatever needs to be returned
}

answered May 26, 2009 at 8:35

Rashmi Pandit

23.9k17 gold badges75 silver badges114 bronze badges

1 Comment

Rytmis Over a year ago

Aha, so I'm not the only one who formats nested usings like this!

1800 INFORMATION · Accepted Answer · 2009-05-26 08:27:02Z

1

You can nest using statements:

using (SqlCommand cmd = CreateSprocCommand("..."))
{
  using (var connection = cmd.Connection)
  {
     connection.Open();
     using (var reader = cmd.ExecuteReader())
     {
         ...
     }
     ...
  }
}

answered May 26, 2009 at 8:27

1800 INFORMATION

137k30 gold badges164 silver badges242 bronze badges

1 Comment

Rytmis Over a year ago

I usually omit the block braces from the outer levels and indent them to the same level to communicate that I intend all the disposables formatted that way to be used together. I haven't quite decided yet whether it's a bad idea or a really bad idea ;-)

Rune Grimstad · Accepted Answer · 2009-05-26 08:25:02Z

0

How about:

using (SqlCommand cmd = CreateSprocCommand("whatever"))
{
  cmd.Connection.Open();
  using (var reader = cmd.ExecuteReader())
  {
   //blabla
  }
}

answered May 26, 2009 at 8:25

Rune Grimstad

36.5k10 gold badges66 silver badges77 bronze badges

2 Comments

Sam Saffron Over a year ago

i dont think that disposing the command will dispose the connection

Douglas Leeder Over a year ago

Does cmd.Dispose() call cmd.Connection.Dispose()?

Eoin Campbell · Accepted Answer · 2009-05-26 08:27:04Z

-1

Is this what you mean ?

using (SqlCommand cmd = CreateSprocCommand("SomeSprocName"))
{
    cmd.Connection.Open();
    using (var reader = cmd.ExecuteReader()) 
    {
        //some code looping over the recors
    }
}

answered May 26, 2009 at 8:27

Eoin Campbell

44.4k18 gold badges107 silver badges161 bronze badges

1 Comment

Gideon Over a year ago

Like Rune, this wouldn't close the connection.

Collectives™ on Stack Overflow

Refactor code into using statement

5 Answers 5

3 Comments

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related