Blake Rivell Blake Rivell - 5 months ago 32
C# Question

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

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

Answer Source

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. You can read here about using statement