SQLite Database Not Loading on iOS and Android with .NET MAUI but works with windows

Question

SQLite Database Not Loading on iOS and Android with .NET MAUI but works with windows

martin schneeberger 20

Using Visual Studio 2022 preview with .NET 9 MAUI, there is an issue getting SQLite to work on iOS and Android. The code compiles for all maui platforms but does not load on the Android emulator or attached Iphone. It works on Windows without issues.

I am confusion regarding where to place the SQLite database for iOS and Android.

I am confused about what path to use for iOS and Android

Below is the relevant code:

using SQLite;
using System.IO;

public class Pointsdatabase
{
    public const string DatabaseName = "pointsdatabase.db";
    public const string TableName = "VelocityCreditCards";
    public const string csvpath =  C:\\Users\\schne\\source\\repos\\MPointsSQLite\\VelocityCreditCards.csv";
    public readonly string _databasePath;

    public Pointsdatabase() 
    {
        var folderPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        _databasePath = Path.Combine(folderPath, DatabaseName);

        if (!File.Exists(DatabaseName))
        { 
            InitializeDatabase();
        }
    }

    private void InitializeDatabase()
    {
        using (var connection = new SQLiteConnection(_databasePath))
        {
            var command = connection.CreateCommand($@"
            CREATE TABLE IF NOT EXISTS {TableName} (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                CreditCard TEXT,
                Own TEXT, 
                Myer TEXT,
                DavidJones TEXT,
                Eleven TEXT,
                Coles TEXT,
                Woolworths TEXT,
                BP TEXT
            )");
            command.ExecuteNonQuery();

            var countCommand = connection.CreateCommand($@"
            SELECT COUNT(*) FROM {TableName}");
            var count = countCommand.ExecuteScalar

Accepted answer

0 additional answers

Your answer

Answer 1

Bruce (SqlWork.com) 79,101 Volunteer Moderator

you should use the Filesysten Helper, "FileSystem.Current.AppDataDirectory", not special folders.

note: your csv path is not valid for non-windows.

martin schneeberger 20 Reputation points

2025-03-25T12:40:05.61+00:00

Hello Bruce

Thanks for your help I made progress

I added you changes

var folderPath = FileSystem.Current.AppDataDirectory;

_databasePath = Path.Combine(folderPath, DatabaseName);

_csvPath = Path.Combine(FileSystem.Current.AppDataDirectory, "VelocityCreditCards.csv");

The application loaded but I could not get the Android or IOS environment T

I could not get the android or IOS environment to open the CSV and build the SQLite database.

'Could not find file '/data/user/0/com.companyname.MpointsSQLite/files/VelocityCreditCards.csv'.'

v

Also when I built a database using DB Browser SQLite called "pointsdatabase.db" and placed it in the main directory of my Visual Studio 2022 the Andriod or IOS could not find the database .

Thanks again
Bruce (SqlWork.com) 79,101 Reputation points Volunteer Moderator

2025-03-25T15:55:28.7866667+00:00

the standard publish does not copy any files to the app data folder. you would need to add this publish step.

Maui supports publishing files in the resources/Raw folder and reading with FileSystem.OpenAppPackageFileAsync().

martin schneeberger 20

Hi Bruce

Thanks for your help I have had some success

I have given up on the idea of using csv to build the database within the app.

Using visual studio 2022

I place pointsdata.db in If I use resources/Raw

I then make sure the database has been placed in the correct place using FileSystem.OpenAppPackageFileAsync()

Strangly if i use FileSystem.Current.AppDataDirectory as a file path it works for windows and Andriod

However I can only get the IOS to work using FileSystem.CacheDirectory.

Which is strange and I will work on this.

Thanks again

To help out others I include an outline of my code

using Microsoft.Maui.Controls;

using Microsoft.Maui.Controls.PlatformConfiguration.iOSSpecific;

using Microsoft.Maui.Storage;

using SQLite;

using System.Diagnostics;

using System.IO;

namespace MpointsSQLite

{

public partial class UserSettingsPage : ContentPage

{

    public const string DatabaseName = "pointsdatabase.db";

    public const string TableName = "VelocityCreditCards";

    public UserSettingsPage()

    {

        InitializeComponent();

        LoadSettings();

        CopyDatabaseIfNotExists();

    }

private void LoadSettings()

{

load my maui setting for page

}

private async void CopyDatabaseIfNotExists()

    {

      

   //     var destinationPath = Path.Combine(FileSystem.CacheDirectory, DatabaseName);

       var destinationPath = Path.Combine(FileSystem.Current.AppDataDirectory, DatabaseName);

        if (!File.Exists(destinationPath))

        {

            using (var stream = await FileSystem.Current.OpenAppPackageFileAsync(DatabaseName))

            {

                using (var fileStream = new FileStream(destinationPath, FileMode.Create, FileAccess.Write))

                {

                    await stream.CopyToAsync(fileStream);

                }

            }

            Debug.WriteLine($"Database copied to: {destinationPath}");

        }

    }

    private void OnCheckBoxCheckedChanged(object sender, CheckedChangedEventArgs e)

    {

        Debug.WriteLine($"checkbox checked");

        try

        {

            var checkBox = sender as CheckBox;

            if (checkBox == null)

                return;

            string creditCardID = checkBox.AutomationId; // Assuming the CheckBox's AutomationId property holds the name of the credit card

            Debug.WriteLine($"checkbox checked for: {creditCardID}");

          //  var tempFilePath = Path.Combine(FileSystem.CacheDirectory, DatabaseName);

           var tempFilePath = Path.Combine(FileSystem.Current.AppDataDirectory, DatabaseName);

            Debug.WriteLine($"Database file path: {tempFilePath}");

            using (var connection = new SQLiteConnection(tempFilePath))

            {

                Debug.WriteLine($"Database connection opened: {tempFilePath}");

                // Execute the UPDATE statement based on the checkbox state

                var updateCommand = connection.CreateCommand($"UPDATE VelocityCreditCards SET Own = ? WHERE CreditCard = ?");

                updateCommand.Bind(checkBox.IsChecked ? 1 : 0); // Set Own to 1 if checked, 0 if unchecked

                updateCommand.Bind(creditCardID);

                Debug.WriteLine($"Executing command: {updateCommand.CommandText}");

                int rowsAffected = updateCommand.ExecuteNonQuery();

                Debug.WriteLine($"Rows affected: {rowsAffected}");

            }

            SaveSettings();

        }

        catch (Exception ex)

        {

            // Handle exceptions as needed

            Debug.WriteLine($"tried to update check box error: {ex.Message}");

        }

    }

    private void OnCheckBoxUnchecked(object sender, CheckedChangedEventArgs e)

    {

        Debug.WriteLine($"checkbox unchecked");

        try

        {

            var checkBox = sender as CheckBox;

            if (checkBox == null || e.Value) // Ensure the checkbox is unchecked

                return;

            string creditCardID = checkBox.AutomationId; // Assuming the CheckBox's AutomationId property holds the name of the credit card

            Debug.WriteLine($"checkbox unchecked for: {creditCardID}");

           // var tempFilePath = Path.Combine(FileSystem.CacheDirectory, DatabaseName);

            var tempFilePath = Path.Combine(FileSystem.Current.AppDataDirectory, DatabaseName);

            Debug.WriteLine($"Database file path: {tempFilePath}");

            using (var connection = new SQLiteConnection(tempFilePath))

            {

                Debug.WriteLine($"Database connection opened: {tempFilePath}");

                // Execute the UPDATE statement to set Own to 0 when unchecked

                var updateCommand = connection.CreateCommand($"UPDATE VelocityCreditCards SET Own = ? WHERE CreditCard = ?");

                updateCommand.Bind(0); // Set Own to 0

                updateCommand.Bind(creditCardID);

                Debug.WriteLine($"Executing command: {updateCommand.CommandText}");

                int rowsAffected = updateCommand.ExecuteNonQuery();

                Debug.WriteLine($"Rows affected: {rowsAffected}");

            }

            SaveSettings();

        }

        catch (Exception ex)

        {

            // Handle exceptions as needed

            Debug.WriteLine($"tried to update check box error: {ex.Message}");

        }

    }

    public async Task<string> GetBestCreditCardVelocityAsync(string placeName)

    {

        Debug.WriteLine($"writing from in usersetting getbest credit cards");

        try

        {

           // var tempFilePath = Path.Combine(FileSystem.CacheDirectory, DatabaseName);

            var tempFilePath = Path.Combine(FileSystem.Current.AppDataDirectory, DatabaseName);

            using (var connection = new SQLiteConnection(tempFilePath))

            {

                var selectCommand = connection.CreateCommand($"SELECT CreditCard FROM VelocityCreditCards WHERE Own = 1 ORDER BY Coles DESC LIMIT 1");

                var result = selectCommand.ExecuteScalar<string>();

                Debug.WriteLine($"Database reading creditcards {tempFilePath}");

                Debug.WriteLine($"reading the database after checkboxes have changed: {result}");

                return result;

            }

        }

        catch (Exception ex)

        {

            // Handle exceptions as needed

            Debug.WriteLine($"Error in GetBestCreditCardVelocityAsync: {ex.Message}");

            return null;

        }

    }

}

Share via

SQLite Database Not Loading on iOS and Android with .NET MAUI but works with windows

0 additional answers

Your answer