Making sure a SQL db connection is always closed after each repo function is called

Question

I am trying to figure out whether I need a using statement in each of my repository functions in order to make sure the connection closes after each batch of database calls.

For example: I want to call connection.query or connection.execute multiple times inside of certain repository functions. If I don't use a using statement when will my connection ever close? The goal is to make it as efficient as possible for web development.

BaseRepository

public static string ConnectionString => @"Server=.;Database=applicationDb;User ID=sa;Password=Password12!";
protected SqlConnection _connection;
protected SqlConnection connection => _connection ?? (_connection = GetOpenConnection());

public static SqlConnection GetOpenConnection(bool mars = false)
{
    var cs = ConnectionString;
    if (mars)
    {
        var scsb = new SqlConnectionStringBuilder(cs)
        {
            MultipleActiveResultSets = true
        };
        cs = scsb.ConnectionString;
    }
    var connection = new SqlConnection(cs);
    connection.Open();
    return connection;
}

public SqlConnection GetClosedConnection()
{
    var conn = new SqlConnection(ConnectionString);
    if (conn.State != ConnectionState.Closed) throw new InvalidOperationException("should be closed!");
    return conn;
}

public void Dispose()
{
    _connection?.Dispose();
}

CustomerRepository : BaseRepository

With the BaseRepository setup the way it is. Is there any difference between the following:

public IEnumerable<Customer> GetCustomers()
{
    using (connection)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("SELECT Id, Name, Email ");
        sql.AppendLine("FROM Customer;");

        StringBuilder deleteSql = new StringBuilder();
        deleteSql = new StringBuilder();
        deleteSql.AppendLine("DELETE FROM Xyz ");
        deleteSql.AppendLine("FROM CustomerId = @CustomerId;");
        connection.Execute(deleteSql.ToString(), new { CustomerId = 5 });

        return connection.Query<Customer>(sql.ToString()).ToList();
    }
}

OR without the using:

public IEnumerable<Customer> GetCustomers()
{
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("SELECT Id, Name, Email ");
        sql.AppendLine("FROM Customer;");

        StringBuilder deleteSql = new StringBuilder();
        deleteSql = new StringBuilder();
        deleteSql.AppendLine("DELETE FROM Xyz ");
        deleteSql.AppendLine("FROM CustomerId = @CustomerId;");
        connection.Execute(deleteSql.ToString(), new { CustomerId = 5 });

        return connection.Query<Customer>(sql.ToString()).ToList();
}

score 2 · Answer 1 · edited Jul 02 '17 at 18:09

2

My suggest here is to use using statement, because the purpose of using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose connection object and obviously connection also closed due to it.

edited Jul 02 '17 at 18:09

Racil Hilan

24,690
13
50
55

answered Jul 02 '17 at 17:52

Roxy'Pro

4,216
9
40
102

score 1 · Accepted Answer · edited Sep 29 '17 at 14:24

1

You need to wrap your calls in using statement (or try...catch if you wish to handle the exceptions), and it will close it and dispose it automatically.

edited Sep 29 '17 at 14:24

Graham

7,431
18
59
84

answered Jul 02 '17 at 16:44

Racil Hilan

24,690
13
50
55

That is exactly what I was trying to figure out. So with the BaseRepository setup the way it is if I don't have a using statement per function the connection will never close correct? I was thinking maybe this isn't a bad thing, why not keep the same connection for a long time if the BaseRepository always checks if a connection already exists use it, otherwise open another one. – Blake Rivell Jul 02 '17 at 16:46
1

You don't need to worry about opening and closing the connection as often as you want. You are actually encouraged to close the connection immediately after you're done with it. That will not really close the connection. It will just return it to the connections pool, and next time you try to open a new connection (within a short time), you will actually be given a connection from the pool. – Racil Hilan Jul 02 '17 at 16:56

Making sure a SQL db connection is always closed after each repo function is called

2 Answers2