CODEWITHSUNDEEP
c#
Scott
Scott

Reputation: 15

Automatically Open and Close Database Connection Within a Method

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?

Upvotes: 0

Views: 5639

Answers (6)

David V. Corbin
David V. Corbin

Reputation: 397

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)

Upvotes: 0

Sinatr
Sinatr

Reputation: 21969

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.

Upvotes: 0

David
David

Reputation: 218798

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.)

Upvotes: 1

gator88
gator88

Reputation: 1223

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.

Upvotes: 0

Ranajit Chatterjee
Ranajit Chatterjee

Reputation: 76

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();
}

Upvotes: 0

T McKeown
T McKeown

Reputation: 12847

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.

Upvotes: 3

Related Questions