Migrate SQLite database from Xamarin Android to NET Maui app

Question

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?

I think you can access the db with its path directly, if its not already created, create one! — FreakyAli
– FreakyAli, Commented May 2, 2023 at 1:31
I'm not really sure how your answer helps? My problem is that I have an existing Xamarin app installed (e.g. MyApp v1.0) but my new version is created in Maui and replaces the existing app (e.g. MyApp v2.0) and the data that was created with v1 should be used, and not replaced, by the v2 app but the database is not recognized/found. App Id is identical so that app is replaced on the device but I also need to use the existing already created database. — Cliffhanger
– Cliffhanger, Commented May 2, 2023 at 5:21
Does it have a different bundle id? Also i haven't answered anything i am commenting to understand things better — FreakyAli
– FreakyAli, Commented May 2, 2023 at 8:37
I might be wrong but I believe that "BundleId" is what IOS calls what Android calls "ApplicationId" and I refer to above as "app id". Yes, they are the same between the Xamarin and Maui app. — Cliffhanger
– Cliffhanger, Commented May 2, 2023 at 12:24
If that is the case make sure your path is correct and you should be able to access the database — FreakyAli
– FreakyAli, Commented May 2, 2023 at 13:05

CVStrydom · Accepted Answer · 2024-06-24 12:40:00Z

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!

Collectives™ on Stack Overflow

Migrate SQLite database from Xamarin Android to NET Maui app

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related