The connection string for SqLite can include options for using a connection pool, e.g. (pseudo code)
Dim connectionString = "Data Source=" + Path + ";" +
"Version=3;" +
"Pooling=True;" +
"Max Pool Size=100;"
I would expect that there is some extra class or factory method for using connection pools, e.g.
dim connectionPool = new SqLiteConnectionPool(connectionString)
dim firstConnection = connectionPool.getConnection()
...
firstConnection.dispose()
...
dim secondConnection = connectionPool.getConnection()
However, I could not find such a class.
=> How do I give back a connection to the connection pool?
=> How can I reuse a connection that has previously been returned to the pool?
Searching for "pool" on https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki did not give any results.
a) Do I have to call the constructor for the connection several times?
dim firstConnection = new SqLiteConnection(connectionString)
...
firstConnection.dispose()
dim secondConnection = new SqLiteConnection(connectionString)
Passing the connection string several times does not seem to be intuitive for me.
b) Or would I create the connection only once and somehow wake it up after it has been closed/disposed?
dim connection = new SqLiteConnection(connectionString))
using connection
...
end using
connection.open()
using connection
...
end using
The SQLite driver will manage pools for you, however, there is one key point many don't realize when using connections.
OPEN LATE, CLOSE EARLY
A connection can't be returned to the pool if it's open. So, you can create the connection, prepare the statements, however, only open the connection immediately before executing the query.