I'm developing a program for a Windows Mobile Handheld 6.5.3 device (attention: compact framework!). The app is primary built for MySQL. As I want to be able to use different databases, I thought a factory-scheme would be the best way.
So I made a db factory out of a tutorial (http://www.c-sharpcorner.com/UploadFile/db2972/factory-design-pattern/) and rewrote most of the database parts, so that it uses the correct interface methods.
Executing a query looks like this (simplified):
using (conn)
using (cmd)
{
conn.Open();
// Prepare query and stuff
//..
// Fetch results
conn.Close();
// check result
return result;
}
}
There is a chain of database queries depending on the result of the first query. Using my app with MySQL is no problem. But switching to SQLite throws an ObjectDisposedException
at conn.Open()
of the query that gets executed AFTER the FIRST one.
Any site/tutorial that writes about handling databases in c# refer to close the connection as soon as the query is done and - if you want to execute a new query - just open the connection again.
Why is SQLite having problems with my code? How can I get any tips from the debugger except for ObjectDisposed (as in: how to handle it with ensuring that mysql runs fine nonetheless)?
Some infos about the IDE, assemblys and the corresponding connectionstrings:
SERVER={0};DATABASE={1};UID={2};PASSWORD={3}
Data Source=mydb.sqlite;Version=3;Pooling=True;Max Pool Size=100;FailIfMissing=True;
Data Source=mydb.sqlite;Version=3;FailIfMissing=True;
Edit: looking at conn
while debugging shows that when running mysql conn.Close()
, it seems that conn
is still available (but obviously closed). While looking at conn
with sqlite, it seems that conn
gets destroyed after conn.Close()
... Shouldn't it have the same behaviour because of the IDbConnection interface? Any hint what I may do now?
Both docs say: public void Close()
implements IDbConnection.Close
Edit 2: conn.Close()
doesn't seem to be the problem - but the end of the using
-block is. I set up a debugger-watch on the conn-object and tested it with mysql and then with sqlite. After exiting the using-block, the conn-object gets "destroyed" with sqlite, but not with mysql.
The using
construct calls Dispose
when you exit the block. It is designed to clean up unmanaged resources no matter what. Therefore it makes sense that you would get an ObjectDisposedException
if you try to access the object again. One simple way to deal with this is to not try to reuse the disposed connection but instead connect each time. Alternatively, move your dispose logic to a higher level such that all you DB access is done before disposing.