CODEWITHSUNDEEP
androidsqlitemaui
Cliffhanger
Cliffhanger

Reputation: 1237

Migrate SQLite database from Xamarin Android to NET Maui app

I want to release a NET Maui upgrade to my existing Xamarin Android app but as my users already have an SQLite database I need to be able up upgrade the app and retain the database.

My Xamarin app is using SQLiteOpenHelper and getting the user version @"PRAGMA user_version" = 8, but when I run the same query in the Maui app I get 0 as user_version. I'm using the same connection string on both apps: Data Source=/data/user/0/<my app id>/databases/my_database.db.

public partial class BaseHandler : Android.Database.Sqlite.SQLiteOpenHelper
{
    private const string DB_NAME = "my_database.db";
    Android.Content.Context _context;

    private Android.Database.Sqlite.SQLiteDatabase _db = null;
    public BaseHandler (IContext context) : base (context.AppContext, DB_NAME, null, DB_VERSION)
    {
        _context = context.AppContext;
        _db = WritableDatabase;
        var version = ExecuteScalar(@"PRAGMA user_version");
    }

    public override void OnCreate (Android.Database.Sqlite.SQLiteDatabase db)
    {
        var sqls = this.CreateDatabaseSql();
        this.DoSql(db, sqls);
        var version = ExecuteScalar(@"PRAGMA user_version");
    }

The Maui app uses the sqlite-net-pcl package.

public async Task<IEnumerable<MyItem>> GetAllItems()
    {
        var sql = "SELECT * FROM MY_ITEMS;";

        try
        {
            await using var cnn = GetCnn();
            return await cnn.QueryAsync<MyItem>(sql);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

What am I missing?

Upvotes: 3

Views: 616

Answers (1)

CVStrydom
CVStrydom

Reputation: 301

After a lot of digging I finally found the answer. It seems that Xamarin Android and MAUI Android file locations differ. My Xamarin SQLite database was stored at:

Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "database.db3")

However, on Xamarin Android the file path was:

/data/user/0/my.app/files/.local/share/database.db3

But when using the exact same code to access the database in MAUI Android resulted in:

/data/user/0/my.app/files/database.db3

My solution was to tweak the path to my legacy database according to platform: '''

#if ANDROID
            var databaseV1Path = ".local/share/database.db3";
#else
            var databaseV1Path = "database.db3";
#endif

var legacyDatabasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), databaseV1Path)

I used the opportunity to optimise my database and remap the data to a new database for MAUI since the image names also needed to change to accomodate for resizetizer's snake case requirement and then deleting the old database file.

Hope this helps!

Upvotes: 0

Related Questions