Windows Phone 7: SQLite

Question

I'm trying to get Community.Csharp to work with Windows Phone, I tried to use both versions of http://wp7sqlite.codeplex.com/ and compile the main trunk with the WINDOWS_PHONE flag, but when I run the application on the phone, I get an error when I try execute any queries (but not when opening the database, only upon requests): "Unable to open the database file"

_conn = new SqliteConnection("Version=3,uri=file:recipes.sqlite"); _conn.Open(); cmd.CommandText = "SELECT * FROM recipes"; SqliteDataReader reader = cmd.ExecuteReader(); 

Interestingly, however, I use the following to check for a table and exceptions are possible:

 cmd.CommandText = "SELECT * FROM sqlite_master WHERE name='" + tableName + "'"; SqliteDataReader rdr = cmd.ExecuteReader(); exists = rdr.Read(); rdr.Close(); 

I have a Windows application that uses SQLite, so if I could use SQLite and not Sterling DB or something else, it will save a huge amount of time. The problem I currently have is that as soon as I open the database and close it, I cannot reopen it.

Answer 1

I use the same library for our corporate application and, as far as I know, is also documented at http://wp7sqlite.codeplex.com (in the Some section of the Recommendations) , if you close the connection, you will need to recreate it again.

== ADDITIONAL COMMENTS ==

I found out the cause of the error, created a fix and tested it in our application. In short, to migrate the Community.CSharpSqlite library to WP7, the author wrote a FileStream wrapper around WP7 IsolatedStorageFileStream. When db opens, the db file stream opens and reads and closes CSharpSqlite. But the handle to this stream is also stored in the dictionary matching the file path to the stream. When db is opened a second time, the handle to the stream is retrieved, but since it is closed (I assume it has not been tested yet), db does not open.

I will try to incorporate my changes into the wp7sqlite.codeplex.com project, but at the same time, if you have the source code , the following changes to Community.CsharpSqlite.FileStream

change

  public FileStream(string path, FileMode mode, FileAccess access, FileShare share, int unused) { IsolatedStorageFileStream handler = null; if (FileStream.HandleTracker.TryGetValue(path, out handler)) { _internal = handler; } else { if (mode == FileMode.Create || mode == FileMode.CreateNew) { _internal = IsolatedStorageIO.Default.CreateFile(path); } else { _internal = IsolatedStorageIO.Default.OpenFile(path, FileMode.OpenOrCreate); } FileStream.HandleTracker.Add(path, _internal); } } 

to

  public FileStream(string path, FileMode mode, FileAccess access, FileShare share, int unused) { IsolatedStorageFileStream handler = null; if(FileStream.HandleTracker.TryGetValue(path, out handler)) { _internal = handler; if(!_internal.CanRead) { FileStream.HandleTracker.Remove(path); CreateOpenNewFile(path, mode); } } else { CreateOpenNewFile(path, mode); } } private void CreateOpenNewFile(string path, FileMode mode) { if(mode == FileMode.Create || mode == FileMode.CreateNew) { _internal = IsolatedStorageIO.Default.CreateFile(path); } else { try { _internal = IsolatedStorageIO.Default.OpenFile(path, FileMode.OpenOrCreate); } catch(Exception ex) { var v = ex; } } FileStream.HandleTracker.Add(path, _internal); } 

This is the first time I'm trying to debug and contribute to an open source project. Any comments or thoughts on these changes would be greatly appreciated.

Alistair.

Answer 2

Hi, I ran into the same problem ... I think I have a fix. This is what I did.

  public void CloseDB() { Connection.Close(); //Connection is a property(of type SqliteConnection) of my object FileStream.HandleTracker.Clear(); //This here is the fix } 

I do not need to change the dll.
I'm not sure yet that this will lead to errors later, but so far this works for me.
... I'm just a junior programmer: D