CODEWITHSUNDEEP
c#sqlite
Kei
Kei

Reputation: 121

C# Close() or using/Dispose() frequently used SQLite object

Sometimes I see people like to dispose just anything after use regardless of how frequently they're being used (probably not related to SQLite question, but I am dealing with SQLite as of now, I'm puzzled) -- or perhaps I am mistaken. This has caused a massive confusion for me.

For example (taken from elsewhere):

using(var con = new SQLiteConnection(conString))
using(var cmd = new SQLiteCommand(con))
{
    con.Open();
    // ...
} // also closes the connection

My question is, should I store the SQLiteConnection and SQLiteCommand objects in the field, and use the method .Open(), .Close() to handle the database connection without disposing them at all until application termination -- or dispose them into the Garbage Collection like it's not really an elegant idea in my perspective?

Edit: If one says dispose, then why? I need better answers, I need the true reason why, not because of pooling, and whatnot. I need to know what exact problems could arise other than human-prone errors, and provide an example or perhaps a link to the example.

For example the class field:

private static SQLiteConnection sQLiteConnection; /// <summary>SQLiteConnection.</summary>
public static SQLiteConnection SQLiteConnection { get { return sQLiteConnection; } }

private static SQLiteCommand sQLiteCommand; /// <summary>SQLiteCommand.</summary>
public static SQLiteCommand SQLiteCommand { get { return sQLiteCommand; } }

Where the private fields are initialized with a private method, and the objects are to be reused without disposing them; Hence the read-only properties.

Edit 2: For clearer clarification, are you people misreading? I am saying "reusing". Which means one is created, and stored somewhere in the field to be reused. I am storing it in the static field in a static class to be reused until the application is closed. Tell me, why do I have to dispose it?

Why, do, I, have, to, dispose, it? If, I, were, to, "reuse", it? Why?

Upvotes: 0

Views: 1544

Answers (1)

D Stanley
D Stanley

Reputation: 152614

Connections are pooled by .NET, so creating them generally isn't an expensive operation. Using the "standard" approach is generally much cleaner that trying to keep track of if a connection is open or closed, etc.

Unless you have measurable problems with connection I would stick with the idomatic approach of creating them, using them, and disposing of them.

Upvotes: 1

Related Questions