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

Question

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<int>();

            if (count == 0)
            {
                ImportCsvToDatabase(csvpath);
            }
        }
    }

    public void ImportCsvToDatabase(string csvFilePath)
    {
        var lines = File.ReadAllLines(csvFilePath);
        var header = lines[0].Split(',');

        using (var connection = new SQLiteConnection(_databasePath))
        {
            connection.BeginTransaction();
            try
            {
                foreach (var line in lines.Skip(1))
                {
                    var values = line.Split(',');
                    var command = connection.CreateCommand($@"
                    INSERT INTO {TableName} (CreditCard,Own,Myer, DavidJones, Eleven, Coles, Woolworths, BP)
                    VALUES (?, ?, ?, ?, ?, ?, ?,?)");
                    command.Bind(values[0]);
                    command.Bind(values[1]);
                    command.Bind(values[2]);
                    command.Bind(values[3]);
                    command.Bind(values[4]);
                    command.Bind(values[5]);
                    command.Bind(values[6]);
                    command.Bind(values[7]);
                    command.ExecuteNonQuery();
                }
                connection.Commit();
            }
            catch
            {
                connection.Rollback();
                throw;
            }
        }
    }

    public class VelocityCreditCard
    {
        public required string CreditCard { get; set; }
        public required string Myer { get; set; }
        public required string Coles { get; set; }
        public required string Woolworths { get; set; }
        public required string BP { get; set; }
    }

    public void UpdateOwnColumn(string creditCardName, string newValue)
    {
        using (var connection = new SQLiteConnection(_databasePath))
        {
            // Update command logic here
        }
    }
}

Any guidance on where the SQLite database should be placed for iOS and Android would be greatly appreciated.

Answer 1

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

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