Implement a `withConnection` context function for ensuring connections close in scripts

This was discussed in the last HADES meeting and I think it would be good to implement something that we can say is our best practice principles for working with db connections to ensure they aren't left hanging. Something I'm sure everyone is guilty of in scripts at some point.

Toy example

The following is along the lines of how I think the function should look. This heavily borrows from the withr package style.

library(DatabaseConnector)

withConnection <- function (connection, code)
{
  stopifnot(dbIsValid(connection))
  on.exit({
    if (dbIsValid(connection)) disconnect(connection)
  })
  force(code)
}

# Example
connectionDetails <- createConnectionDetails(server = ":memory:", dbms = "sqlite")
connection <- connect(connectionDetails)
withConnection(connection, {
  insertTable(connection, tableName = "cars", data = mtcars)
  result <- renderTranslateQuerySql(connection, "SELECT * FROM cars")
})

# Should print 50
print(nrow(cars))
# Should print FALSE
print(dbIsValid(connection))

Most OHDSI functions already use the on.exit to be clean but this approach is more general and could be applied everywhere.

Some semantic sugar could be added around the connection details instance so you auto assign a connection with this.

A part of any PR for this should be to update any guides and examples in this package to use the agreed standard practice for ensuring the connection is closed after usage.

Start by reviewing DatabaseConnector’s existing connection functions, guides, and examples, then compare them with the proposed withConnection toy example. Clarify the agreed connection and semantic-sugar API before implementation. Done means the agreed pattern is implemented and the package guides and examples consistently show connections being closed after use.