Xamarin.forms Backup a SQLite database

Question

My Xamarin.forms application (Android) integrates a SQLite database, which I can manage correctly thanks to the example I found here: https://learn.microsoft.com/fr-fr/xamarin/get-started/quickstarts/database

My first question is: how to save this notes.db3 file (on Onedrive or possibly Google drive).

My second question: how to provide the application with a database containing tables with data. I understand, from what I found on the internet, that you need to copy the pre-filled file sqlite.db3 to the Resources folder, and then copy this file with code to the application folder.

I searched a lot, but I couldn't find the exact code to be able to do it. Thank you for helping me, it would be very useful because there is very little documentation on this subject.

Edit : Here is the answer to the second question:

1. I use the program to fill the tables with the useful data when the program is run for the first time by a new user.
2. I programmatically copy the SQLite file into a folder accessible with an external application: Android Studio (the file manager of the Android Device Monitor utility in Visual Studio 2019 does not work!). Here is the code:

using Xamarin.Essentials;
using FileSystem = Xamarin.Essentials.FileSystem;

public void CopyDBToSdcard(string dbName)
        {
            var dbPath = Path.Combine(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), dbName));
            string destPath = Path.Combine("/sdcard/Android/data/com.aprisoft.memocourses/files", dbName);
            //
            if (File.Exists(dbPath))
            {
                if (File.Exists(destPath))
                {
                    File.Delete(destPath);
                }
                File.Copy(dbPath, destPath);
            }
        }

3. I copy this pre-filled SQLite file into my application, at the root of the main project. In the properties of this file, I indicate "Embedded resource" in "Build action".

4. When the program is run for the first time, it checks whether it finds the SQLite file. If it cannot be found, I use the code given by Dirk here to copy the file into the special folder "LocalApplicationData" Here is the code:

public void CopyDB_FR(string filename)
        {
            var embeddedResourceDb = Assembly.GetExecutingAssembly().GetManifestResourceNames().First(s => s.Contains(filename));
            var embeddedResourceDbStream = Assembly.GetExecutingAssembly().GetManifestResourceStream(embeddedResourceDb);

            var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), filename);
            //
            if (!File.Exists(dbPath))
            {
                using (var br = new BinaryReader(embeddedResourceDbStream))
                {
                    using (var bw = new BinaryWriter(new FileStream(dbPath, FileMode.Create)))
                    {
                        var buffer = new byte[2048];
                        int len;
                        while ((len = br.Read(buffer, 0, buffer.Length)) > 0)
                        {
                            bw.Write(buffer, 0, len);
                        }
                    }
                }
            }

        }

I leave the thread open, because I don't have an answer to my first question yet. Any help will be appreciated. Thank you.

Edit 2: I followed Microsoft's exemple to build a connection to Azure into my app using Graph APIs, and it works: the connection is fine, and I can retrieve user data. However, I can't find a way to copy a file to Onedrive. I am using the following code:

await (Application.Current as App).SignIn();
            btnConnect.IsEnabled = false;
            //
            // put user's files
            
            string path = Path.Combine("/data/data/com.ApriSoft.memocourses/files/Backup", "MemoCourses.db3");
            byte[] data = System.IO.File.ReadAllBytes(path);
            Stream stream = new MemoryStream(data);
            
            await App.GraphClient.Me
                    .Drive
                    .Root
                    .ItemWithPath("/Backup/MemoCourses.db3")
                    .Content
                    .Request()
                    .PutAsync<DriveItem>(stream);

            ;

But after a few minutes I got the following error:

Authentication Error Code: GeneralException Message: An error occured sending the request.

Is my code incorrect? Please help me, I would like to finish my app. Thank you very much.

Answer 1

I finally managed to back up and restore my SQLite database to OneDrive. It’s both simple and complicated. It's simple if you follow the example Microsoft provided with Graph and Azure here: https://learn.microsoft.com/en-us/graph/tutorials/xamarin?tutorial-step=1 And it's complicated because this example doesn't explain how to copy files to OneDrive. Here is what I did: I followed Microsoft's step-by-step example by applying it to my application.

In the Azure admin center, I added in the configured permissions:

Device.Read
Files.ReadWrite.All
Files.ReadWrite.AppFolder

The latter is the most important.

In my application: In OAuthSettings.cs, the following line is modified by adding Files.ReadWrite.AppFolder to the definition of the Scopes constant:

Public const string Scopes = "User.Read Calendars.Read Files.ReadWrite.AppFolder";

This is very important to have access to the application folder on OneDrive. In the method that corresponds to the backup of the SQLite database, here is the code to put:

Stream contentStream = null;
//
var path = await App.GraphClient
                        .Me
                        .Drive
                        .Special
                        .AppRoot
                        .ItemWithPath("MemoCourses.db3")
                        .Request()
                        .GetAsync();


//
try
{
    //foundFile = await path.Request().GetAsync();
    if (path == null)
    {
        await DisplayAlert("Attention", "Backup file not found", "OK");
    }
    else
    {
        contentStream = await App.GraphClient.Me
                                    .Drive
                                    .Special
                                    .AppRoot
                                    .ItemWithPath("MemoCourses.db3")
                                    .Content
                                    .Request()
                                    .GetAsync();

         
        var destPath = Path.Combine(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), dbName));
        var driveItemFile = System.IO.File.Create(destPath);
        contentStream.Seek(0, SeekOrigin.Begin);
        contentStream.CopyTo(driveItemFile);
        //
    }
}
catch (Exception ex)
{
    var error = ex;
    await DisplayAlert("Attention", error.ToString(), "OK");
}

dbName contains the name of the SQLite file.

For the restoration of the database:

var dbPath = Path.Combine(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), dbName));
byte[] data = System.IO.File.ReadAllBytes(dbPath);
Stream stream = new MemoryStream(data);
//
try
{
    await App.GraphClient.Me
        .Drive
        .Special
        .AppRoot
        .ItemWithPath("MemoCourses.db3")
        .Content
        .Request()
        .PutAsync<DriveItem>(stream);
}
catch
{
    await DisplayAlert("Attention", "Problem during file copy...", "OK");
}

In my app everything is working perfectly. I hope I have helped those who are still looking for a solution to this problem. Please don't hesitate to ask me any questions if you want more information.