0

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?

Kei
  • 121
  • 1
  • 10
  • 3
    Trying to reuse a DBCommand object can lead to all sorts of issues - often ending in a SO question. DBConnection objects are a bit different and depends more on the app/DB, but in general connection pooling means you dont save much trying to reuse them. – Ňɏssa Pøngjǣrdenlarp Sep 28 '16 at 17:36
  • 2
    Generally you want to create, use, and destroy external dependencies in as small a scope as possible. If creating these objects is a *heavy* operation (it probably isn't) then you can look into some kind of re-use. But that opens up all sorts of cans of worms which are otherwise not a problem if you don't try to re-use the same dependency object in different operations/scopes. – David Sep 28 '16 at 17:37
  • 1
    people who `like to dispose just anything after use` like their apps not to leak. If something has a Dispose method, it needs to be disposed of when you are done with it – Ňɏssa Pøngjǣrdenlarp Sep 28 '16 at 17:43
  • Hmm, like, what sort of issues, and how do they leak, though? I've searched through forums and all they say is just go for disposing them, pooling and whatnot. No one really mentioned specifically of what issues arises from such situation -- or perhaps I didn't look around more than enough. – Kei Sep 28 '16 at 18:28
  • When more than one person comments, if you want to reply to someone specifically, use @ + Username as in @Kei. This is not a forum so it doesnt act like one. As for disposing, I've answered 2-3 in the last month that was a result of reusing. Reusing a Command object means you start with all the params from the last use. It is just as easy to create a new one (and less error prone) than to clear out the old ones. – Ňɏssa Pøngjǣrdenlarp Sep 28 '16 at 21:04
  • @Plutonix Okay, you haven't answered my question -- what sort of issues could arise? Other than human-prone errors, I couldn't find what other issues is. – Kei Sep 28 '16 at 23:22
  • 1
    What other kind is there? This place is lousy with questions about open datareaders, goofy, inexplicable SQL errors and resource depletion issues either from sloppy programming or because someone thought it would be cool to build a DB helper class to reuse things that ought not be reused. It is hard to imagine anything less reusable than a DBCommand object. – Ňɏssa Pøngjǣrdenlarp Sep 29 '16 at 00:32

1 Answers1

1

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • 1
    From what I know, SQLite does _not_ support connection pooling. [Same as Microsoft Access OleDB](http://stackoverflow.com/a/25197037/107625) :-( – Uwe Keim Sep 28 '16 at 17:43
  • `The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling.` [link](https://msdn.microsoft.com/en-us/library/ms254502(v=vs.110).aspx) There can be some benefit to using the same connection for the file based DBs though. – Ňɏssa Pøngjǣrdenlarp Sep 28 '16 at 17:47
  • 2
    @UweKeim yes you can, to both of these. For OleDB, you can't use both ODBC connection pooling AND OleDB resource pooling, but you can choose one. – Dispersia Sep 28 '16 at 17:48
  • @UweKeim To be honest I've never used SQLite so I can't speak to it specifically, but OleDB connections (including Access) are pooled by .NET - the KB article in your answer refers to OLE-DB and ASP which uses a different pooling mechanism than ADO.NET. – D Stanley Sep 28 '16 at 17:53
  • 1
    I stand by my opinion: SQLite and Access does _not_ support connection pooling. [See my (probably naive but at least comparable) test results](http://stackoverflow.com/a/35225563/107625) on a similar topic. – Uwe Keim Sep 28 '16 at 17:55
  • @DStanley Well I have no measurable problems, I am just confused of why do I/we have to dispose it. Isn't that closing it already enough to add the connection to the pool -- or do I/we have to dispose it so it will only be pooled? – Kei Sep 28 '16 at 18:25
  • You should dispose of it whether it's pooled or not. Disposing cleans up the unmanaged resources (in this case a database connection) without waiting for garbage collection. Closing the connection is not enough - if you just close the connection you still have a reference to the unmanaged resource. – D Stanley Sep 28 '16 at 18:36
  • Since connections are pooled, you can create lots of them relatively cheaply, sop there's no need to keep objects at the class level like you're suggesting. It doesn't _hurt_ but it makes the coding harder since you have to check state before using it. – D Stanley Sep 28 '16 at 18:38
  • @DStanley Alright, it seems everyone says we should clear unmanaged resources, Although I don't know why it becomes unmanaged when the resource is to be reused over and over -- right, nevermind, perhaps it's to prevent human-made-errors instead of system errors. – Kei Sep 28 '16 at 23:20
  • @DStanley Additionally, the objects are created at initialization -- obviously to be reused throughout the operation, I don't get you. There are no state-checking whatsoever. – Kei Sep 28 '16 at 23:49
  • @Kei I'm not sure you fully know what "managed" means, but if you use database connections you _must_ dispose of them. Whether you do that every time you use it or create one connection and reuse it doesn't change that fact. If you decide to hold on to a connection then _your_ class should implement `IDisposable` as well. There are lots of questions here and articles on other sites to guide you. – D Stanley Sep 29 '16 at 01:08
  • @DStanley I am perfectly aware of the difference between managed and unmanaged resources. Unamanaged ones do not fall into Garbage Collection, they ones needs to be disposed of. However... I simply could not understand why do I have to dispose it -- when it is to be reused over and over very frequently. Notice that my example of reusable object is initialized as a "static", which means it is global, or singleton. I am not creating thousands of reusable SQLite objects. Furthermore, I mentioned the objects are created and reused until application termination. Why would I dispose my static class? – Kei Sep 29 '16 at 01:16