cannot retreive data from a prepopulated sqlite database in dot net maui for andriod

Question

I am creating a dot net maui android app. The app needs to access a prepopulated sqlite data base and retrieve a list of questions. I have the database and I seem to be linking it correctly to the application but I cannot retrieve the data.

This is how i am asking for the data :

 private async Task<List<TodoItem>> RetrieveFromDatabase()
    {
        try
        {
            var compDatabase = new CompDatabase();
            var questions = await compDatabase.GetItemsAsync();
            Console.WriteLine("Lines retrieved: " + questions.Count);
            return questions;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error retrieving questions: " + ex.Message);
            // Handle any exceptions here
            return new List<TodoItem>();
        }
    }

[DOTNET] Lines retrieved: 0

the below is based on the micros soft example : https://github.com/dotnet/maui-samples/blob/main/7.0/Data/TodoSQLite/TodoSQLite/Data/TodoItemDatabase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;
using Comp.Models;

namespace Comp.Data
{
    public class CompDatabase
    {
        SQLiteAsyncConnection Database;

        public CompDatabase()
        {
        }

        async Task Init()
        {
            if (Database is not null)
                return;

            Database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
            var result = await Database.CreateTableAsync<TodoItem>();
        }

        public async Task<List<TodoItem>> GetItemsAsync()
        {
            await Init();

            Console.WriteLine("Database path: " + Constants.DatabasePath);
            Console.WriteLine("Database file exists: " + File.Exists(Constants.DatabasePath));


            return await Database.Table<TodoItem>().ToListAsync();
        }

        public async Task<List<TodoItem>> GetItemsNotDoneAsync()
        {
            await Init();
            return await Database.Table<TodoItem>().Where(t => t.IsAnsweredCorrectly).ToListAsync();

            // SQL queries are also possible
            //return await Database.QueryAsync<TodoItem>("SELECT * FROM [TodoItem] WHERE [Done] = 0");
        }

        public async Task<TodoItem> GetItemAsync(int id)
        {
            await Init();
            return await Database.Table<TodoItem>().Where(i => i.ID == id).FirstOrDefaultAsync();
        }

        public async Task<int> SaveItemAsync(TodoItem item)
        {
            await Init();
            if (item.ID != 0)
            {
                return await Database.UpdateAsync(item);
            }
            else
            {
                return await Database.InsertAsync(item);
            }
        }

        public async Task<int> DeleteItemAsync(TodoItem item)
        {
            await Init();
            return await Database.DeleteAsync(item);
        }

    }
}

[DOTNET] Database path: /data/user/0/com.companyname.comp/files/Databases/TodoSQLite.db3

[DOTNET] Database file exists: True

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;

namespace Comp.Databases
{
    public static class Constants
    {
        public const string DatabaseFilename = "TodoSQLite.db3";

        public const SQLite.SQLiteOpenFlags Flags =
            SQLite.SQLiteOpenFlags.ReadWrite |
            SQLite.SQLiteOpenFlags.Create |
            SQLite.SQLiteOpenFlags.SharedCache;


        public static string DatabasePath
        {
            get
            {
                var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
                return Path.Combine(basePath, "Databases", DatabaseFilename); // Make sure the path includes "Databases" folder
            }
        }
    }
}

I dont understand and cant seem to find the reason as to why I cannot retrieve the data. according to my Console logs the data base is being passed to the android emulator so it should be accessible and i am not getting any errors.

the data base is being passed to the android emulator What exactly is happening? I you have a prepopulated database file then what did you do to copy it to the path your app is expecting? Which path? — blackapps
– blackapps, Commented Sep 16, 2023 at 11:07
created the database and populated data using DB Browser for SQLite. added the database by copying to the project and "add existing item" — M-Corp
– M-Corp, Commented Sep 16, 2023 at 11:11
And after that where would it land in your project? And if it is in your project on pc then how would it land in the databases directory of your apps private dir in emulator or device? — blackapps
– blackapps, Commented Sep 16, 2023 at 11:15

Samuel B. · Accepted Answer · 2023-09-16 11:52:51Z

2

Hope this helps

MauiProgram.cs

builder.Services.AddSingleton<SQLConnection>(s => ActivatorUtilities.CreateInstance<SQLConnection>(s));

        var assembly = IntrospectionExtensions.GetTypeInfo(typeof(App)).Assembly;

        using (Stream stream = assembly.GetManifestResourceStream("Neminaj.Resources.Database.test.db3"))
        {
            using (MemoryStream memoryStream = new MemoryStream())
            {
                stream.CopyTo(memoryStream);
                File.WriteAllBytes(SQLConnection.m_DBPath, memoryStream.ToArray());
            }
        }

SQLConnection.cs

public class SQLConnection
{
    public static string m_DBPath { get; } = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "test.db3");
    public static SQLiteAsyncConnection m_ConnectionAsync = null;
    public static SQLiteConnection m_ConnectionSync = null;

    public static string StatusMessage { get; set; }

    public SQLConnection()
    {
        InitAsync(); // todo
    }

    public static async Task InitAsync()
    {
        if (m_ConnectionAsync is not null)
            return;

        m_ConnectionAsync = new SQLiteAsyncConnection(SQLConnection.m_DBPath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite);

        await m_ConnectionAsync.CreateTableAsync<Test>();
        await m_ConnectionAsync.CreateTableAsync<TestTable>();
        // and so on
    }
}

answered Sep 16, 2023 at 11:52

Samuel B.

5164 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M-Corp Over a year ago

Thank you !!. You helped point me in the right direction. I found my answer with "Gerald Versluis : youtube.com/watch?v=ftDq-leq5OM : github.com/jfversluis/ExistingSQLiteDbSample/tree/main"

Collectives™ on Stack Overflow

cannot retreive data from a prepopulated sqlite database in dot net maui for andriod

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related