How to Close Sqlite Connection without explicitly calling Close Method using .NET

Question

I am creating desktop application in winform that will use Sqlite Database.

So I created Sqlite Helper class that uses System.Data.SQLite and each method of that Helper class opens and closes connection.

But now I also added ability to attach additional databases but after Connection is Closed, all attached databases gets lost.

To correct this I modified the class so that the connection is opened in constructor and remains open.

After the application ends, I want that connection to close without explicitly calling the Close method.

Any suggestions how to do that?

Answer 1

Keeping the connection open for the lifetime of your application is not a good way to go.
I suggest to not follow this route.
On the contrary, I will try to encapsulate the functionality to attach a database inside a method that could be called on the need to use basis.

For example:

private static void AttachDB(string fileDB, string aliasName, SQLiteConnection cn) 
{ 
    string sqlText = string.Format("ATTACH '{0}' AS {1}", fileDB, aliasName) 
    SQLiteCommand cmd = new SQLiteCommand(sqlText, cn) 
    cmd.ExecuteNonQuery(); 
} 

then in your code

using(SQLiteConnection cn = new SQLiteConnection(GetConnectionString()))
{
     AttachDB(@"C:\SQLite\UserData.sqlite3", "UserData", cn);
     // Do your code here
} 

Answer 2

Close should not disconnect your database but this will only work when .NET connection pooling mechanism is on. Make sure you have that enabled in your connection string:

Data Source=filename;Version=3;Pooling=True;Max Pool Size=100;