Windows Runtime Apps: Upgrading SQLite database and adding a new table

Question

I have an SQLite database in the first version of my Windows app (which is on the Windows store). Now I want to release the second version of the application, which also has an SQLite database in it with new tables added. I have my data saved in first version and don't want to lose them. I found that Android has onCreate and onUpgrade methods for handling sqlite database versions. Android: upgrading DB version and adding new table .

Similar question is here. But that is for iOS.

Is there any similar solutions for Windows Runtime Apps(Windows 8.1 and Windows Phone 8.1)? Please suggest some alternatives.

Thanks in advance.

Possible duplicate of [Upgrading database on app update](http://stackoverflow.com/questions/15703352/upgrading-database-on-app-update) — chue x, Dec 09 '15 at 20:18

score 2 · Answer 1 · answered Dec 09 '15 at 21:04

A good way to handle this kind of issue is to add a versionning system in the database. Before using the connection to the database, simply check the app version in the database and, if the new version is higher of the previous one, run all the necessary commands to update your database.

Ex:

public async Task<SQLite.SQLiteConnection> GetSqliteConnectionForUserAsync(string login)
{
    using (await _mutex.LockAsync())
    {
        if (login == null)
        {
            login = "__anonymous__";
        }

        SQLite.SQLiteConnection conn;
        if (!_userConnections.TryGetValue(login, out conn))
        {
                conn = new SQLite.SQLiteConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path,
                string.Format("{0}.db", Uri.EscapeDataString(login))));
                await SqlSchemaHandler.EnsureSchemaReadyAsync(conn, s =>
                {
                    _logger.Info("Schema handler message : {0}", s);
                });
            _userConnections[login] = conn;
         }
     return conn;
}

}

And (SqlSchemaHandler):

public static Task EnsureSchemaReadyAsync(SQLiteConnection connection, Action<string> actionReporter)
{
    return Task.Run(() =>
    {
        connection.CreateTable<SchemaInfo>();
        var schemaInfo = connection.Table<SchemaInfo>().FirstOrDefault();
        if (schemaInfo == null)
        {
            ApplyV0ToV1(connection);
            schemaInfo = new SchemaInfo { Id = 1, Version = 1 };
            connection.Insert(schemaInfo);
        }
    });
}

private static void ApplyV0ToV1(SQLiteConnection connection)
{
    connection.CreateTable<Test>();
}

Thanks,

Thanks for your suggestion! Appreciate your effort. Yes that's a better idea. @Thomas LEBRUN — Kishor Bikram Oli, Dec 10 '15 at 10:22

score 1 · Accepted Answer · answered Dec 10 '15 at 14:59

A better (for performance) way to have a version of the DB is to use the "PRAGMA user_version"

var sqLiteAsyncConnection = new SQLiteAsyncConnection(path);

// read the user version
var version = sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version");

perfMon.TraceSinceLast("StandardQueryBase : db version read");

if (version == "0")
{
   // update the schema here   

    // store the new version number in the DB
    sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=1;");
}

Thank You @Jmix90. It's good for performance and ended up with your answer. Thanks again :) — Kishor Bikram Oli, Dec 14 '15 at 05:32

Windows Runtime Apps: Upgrading SQLite database and adding a new table

2 Answers2