Windows Phone 7: SQLite

Question

I'm trying to get Community.Csharp working with Windows Phone, I've tried using both the version from http://wp7sqlite.codeplex.com/ and compiling the main trunk with the WINDOWS_PHONE flag, but when I run the application on the phone I get an error when trying to execute any queries (but not when opening the database; only on queries): "Unable to open database file"

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

Interestingly though, I'm using the following to check for the existence of a table and no exceptions are thrown:

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

I have a Windows app which uses SQLite, so if I could use SQLite as opposed to Sterling DB or something else, that would save huge amounts of time. The problem I'm having currently is that once I open the database and close it, I cannot re-open it.

Answer 1

I am using the same library for our company's application and as far as I know, also documented at http://wp7sqlite.codeplex.com (under Some Recommendations ), if you close the connection you'll need to recreate it again.

== ADDITIONAL COMMENTS ==

I've tracked down the cause of the error, created a fix and am testing it in our application. Briefly, in order to port the Community.CSharpSqlite library to WP7, the author wrote a FileStream wrapper around WP7 IsolatedStorageFileStream. When a db is opened, the db file stream is opened and read and closed by CSharpSqlite. But a handle to this stream is also stored in a Dictionary mapping the file path to stream. When a db is opened for a second time, the handle to the stream is retrieved but since it's closed (I'm assuming, haven't verified yet) the db fails to open.

I will attempt to get my changes deployed to the wp7sqlite.codeplex.com project, but in the meantime if you have the source code make the following changes to Community.CsharpSqlite.FileStream

change from

    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 attempting to debug and contribute to an open source project. Any comments or thoughts on these changes will be greatly appreciated.

Alasdair.

Answer 2

Hi I have encountered the same issue ... I think i got the fix for it. This is what I've done.

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

I don't really need to change the dll.
I'm not yet sure if this will cause errors later on , but for now it works for me.
... I'm only a junior programmer:D