How to close Sqlite Connection without explicitly calling the Close method using .NET.

Question

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

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

But now I also added the ability to attach additional databases, but after the connection is closed, all connected databases are lost.

To fix this, I modified the class so that the connection opens in the constructor and remains open.

After the application finishes, I want this connection to close without explicitly calling the Close method.

Any suggestions on how to do this?

Answer 1

Maintaining a connection throughout the life of your application is not a good way. I suggest not following this route. On the contrary, I will try to encapsulate the functionality in order to attach the database inside the method, which could be called the need to use the database.

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 it will only work when the mechanism for combining .NET connections is enabled. Make sure this is included in the connection string:

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

Answer 3

Depending on how your class is defined, you can use Dispose or a destructor. Or explicitly call Close () at the end of the program (from Main, after Run ...).

Answer 4

In C #, there is a special syntax for this situation:

 using(var connection = new Connection()) { //work with connection } 

it compiles something like this:

 Connection connection = null; try { connection = new Connection(); //your operations } finally { connection.Dispose(); } 

when you call Dispose () you close the connection.