Automatically Open and Close Database Connection Within a Method

Question

I have recently been put on an existing project and am trying to reduce the amount of duplicate code.

Right now there are many methods relating to database interaction. The Database connection is opened and closed within each of these methods. Essentially, the exact same code is used in every single method to open and close the connection.

    public static void AddToTable()
    { 
        DbConnection con = Common.CreateConnection();
        DbCommand cmd = con.CreateCommand();

        //cmd.CommandText = SQL COMMAND GOES HERE
        //cmd.ExecuteNonQuery();

        con.Close();
    }

I understand the importance of opening and closing the connection but seeing these exact same lines of code in every method smells funny to me.

Does C# have some kind of way I could accomplish automatically creating the connection (in this case the con and cmd variables) when the method is started and then closing the connection when the method is finished?

Looks like you might also be able to refactor database command execution into a another method that accepts your command text as a parameter. — Michael Petito
– Michael Petito, Commented Jul 11, 2014 at 13:46
@MichaelPetito I thought about that but many of the database commands will have custom parameters and I feel that would get very messy very quickly. The example in my post excluded this. — Scott
– Scott, Commented Jul 11, 2014 at 13:54

T McKeown · Accepted Answer · 2014-07-11 13:45:06Z

3

public static void AddToTable()
{ 
    using( DbConnection con = Common.CreateConnection() ) 
    {
      DbCommand cmd = con.CreateCommand();

      //cmd.CommandText = SQL COMMAND GOES HERE
      //cmd.ExecuteNonQuery();
    }

}

Because the DBConnection is inside the using and DbConnection implements the interface IDisposable the connections .Dispose() method will be executed once it goes out of scope.

answered Jul 11, 2014 at 13:45

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

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

Comments

T McKeown · Accepted Answer · 2014-07-11 14:05:47Z

1

You could do something like this:

using(var con = Common.CreateConnection())
{
  using(var cmd = con.CreateCommand())
  {
      //cmd.CommandText = SQL COMMAND GOES HERE
      //cmd.ExecuteNonQuery();
  }
}

This will automatically close the connection when disposing of the cmd and con objects after closing the using block(s). It doesn't refactor out the creation of those objects, but honestly that shouldn't be refactored out anyway. It's best to keep those objects entirely within the scope of the method using them. Repeated code isn't that distasteful when it's repeated for a very good reason. (Indeed, one can argue that this isn't even repeated code since it's used for different business purposes. It's simply repeated keystrokes. Andy typing keystrokes into the IDE isn't the difficult part of software development, maintaining the code and abstractions therein is.)

edited Jul 11, 2014 at 14:05

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

answered Jul 11, 2014 at 13:47

David

221k42 gold badges247 silver badges338 bronze badges

1 Comment

T McKeown Over a year ago

command typically isn't required unless you are using an OleDbCommand or OdBcCommand, but it's a good practice non the less.

Ranajit Chatterjee · Accepted Answer · 2014-07-11 13:51:09Z

0

If the method is doing database operations then you need to Open and Close database connections in the same method itself if no such requirement to open the connection indefinitely. If you use 'Using' statement the compiler will convert the code to try/finally block.

See below,

using (SqlConnection sqlConn = new SqlConnection("connectionstring"))
{
      sqlConn.Open();

}

The compiler will convert this code as below.

try
{
     SqlConnection sqlConn = new SqlConnection("connectionstring");
     sqlConn.Open();

}
finally
{
     sqlConn.Close();
}

answered Jul 11, 2014 at 13:51

Ranajit Chatterjee

765 bronze badges

1 Comment

T McKeown Over a year ago

it will actually call sqlConn.Dispose()

gator88 · Accepted Answer · 2014-07-11 14:05:47Z

0

In addition to using blocks consider also the unit-of-work pattern which is used in Entity Framework for example. The main idea is to pick some logical parts of the app's DB work into a single unit and have a single connection open&close for each unit. Your method is called AddToTable so we can assume working with some concrete table data which itself is a set of some essences (rows in this table). One of the possible approaches in this case is to consider a unit of work as a set of operations with a single essence in a table like CRUD. The lifecycle of DB connection would be: open connection - CRUD single essence - close connection.

answered Jul 11, 2014 at 14:05

gator88

1,2332 gold badges11 silver badges14 bronze badges

Comments

Sinatr · Accepted Answer · 2014-07-11 14:46:25Z

0

You could create helper method to move repeatable code into it. Something like

public static void WithDB(Action<SqlConnection, DbCommand> action)
{
    using(var con = Common.CreateConnection())
    {
        var cmd = con.CreateCommand();
        action(con, cmd);
    }
}

Use it like this

public static void AddToTable()
{
    WithDB((con, cmd)=>
    { 
        //cmd.CommandText = SQL COMMAND GOES HERE
        //cmd.ExecuteNonQuery();
    });

}

Proof.

answered Jul 11, 2014 at 14:46

Sinatr

22.3k18 gold badges108 silver badges349 bronze badges

Comments

David V. Corbin · Accepted Answer · 2021-05-28 17:40:33Z

0

Lambda is your friend.... (actually just spent time refactoring 400+ using statements down so there is only one place where the sqlcommand is created (and it is a using statement)

answered May 28, 2021 at 17:40

David V. Corbin

4172 silver badges12 bronze badges

Collectives™ on Stack Overflow

Automatically Open and Close Database Connection Within a Method

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related