CODEWITHSUNDEEP
c#.netsqlitedatabase-connectionsystem.data.sqlite
user850010
user850010

Reputation: 6359

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

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?

Upvotes: 7

Views: 15012

Answers (4)

Maciej
Maciej

Reputation: 7961

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;

Upvotes: 5

Steve
Steve

Reputation: 216293

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
}

Upvotes: 5

igofed
igofed

Reputation: 1442

In C# there is a special syntax for such situation:

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

it compiles to something like:

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

on calling Dispose() you close connection.

Upvotes: 1

Mr Lister
Mr Lister

Reputation: 46569

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 within Main, after Run...).

Upvotes: 1

Related Questions