Xamarin forms local database

CingCing NLMember ✭✭
edited January 30 in Xamarin.Forms

I am trying to implement a database following this: https://docs.microsoft.com/nl-nl/xamarin/xamarin-forms/app-fundamentals/databases

But I am getting an aggregate.exception on the main() function when debugging on my iphone.

I added this to my app.cs:

 ` 
static ItemDatabase database;

    public static ItemDatabase Database
    {
        get
        {
            if (database == null)
            {
                database = new ItemDatabase(
                  Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "WaarStaatSQLite.db3"));
            }
            return database;
        }
    }`

Then this is the database class:

`public class ItemDatabase
    {
    readonly SQLiteAsyncConnection database;

    public ItemDatabase(string dbPath)
    {
        database = new SQLiteAsyncConnection(dbPath);
        database.CreateTableAsync<Item>().Wait(); // aggregate.exception probably from this line
        Debug.WriteLine("Created Database");
    }

    //get all items from db async
    public async Task<ObservableCollection<Item>> GetItemsAsync()
    {
        Debug.WriteLine("get items");
        var data = await database.Table<Item>().ToListAsync();
        Debug.WriteLine(data.ToString());
        if(data != null)
        {
            return new ObservableCollection<Item>(data);
        }
        else
        {
            return null;
        }

    }

    //get all items from db not async
    public async Task<ObservableCollection<Item>> GetItemsNotDoneAsync()
    {
        var data = await database.QueryAsync<Item>("SELECT * FROM [Item] WHERE [Done] = 0");
        return new ObservableCollection<Item>(data);
    }

    //get item from db with id
    public async Task<Item> GetItemAsync(int id)
    {
        return await database.Table<Item>().Where(i => i.ID == id).FirstOrDefaultAsync();
    }

    //save or update a single item
    public async Task<int> SaveItemAsync(Item item)
    {
        if (item.ID != 0)
        {
            return await database.UpdateAsync(item);
        }
        else
        {
            return await database.InsertAsync(item);
        }
    }

    //delete item in db
    public async Task<int> DeleteItemAsync(Item item)
    {
        return await database.DeleteAsync(item);
    }
}`

I then call this function in the MainPageViewModel:

                `public override async void OnNavigatedTo(INavigationParameters parameters)
                    {
                        var data = await App.Database.GetItemsAsync();
                        if (data != null)
                        {
                            Debug.WriteLine(data.ToString());
                            Items = data;
                        }            
                    }`

OnNavigatedTo is a prism function.
I first thought maybe the OnNavigatedTo function can't be async but I found a question only saying the OnNavigatedTo function could be async void.

So I did some debugging using breakpoints and print statements and I found that the code stops in the database constructor on the createTable line. Because the Debug.writeline is never executed. Using a breakpoint on that line didn't help because i couldn't step in on the function.
I don't know if I needs to implement something in the ios-project because the sample didn't include anything.

Any help implementing databases is appreciated.

Best Answer

  • CingCing NL ✭✭
    edited February 25 Accepted Answer

    As for the final solution.

    I serialized the ObservableCollection and wrote it to a file.
    I use Newtonsoft.json plugin to do the serialization, here is the code for any futher readers:

    `

    using Newtonsoft.Json;
    using System.Collections.ObjectModel;
    using System.Diagnostics;
    using System.IO;
    using System.Threading.Tasks;
    using WaarStaat.Models;
    using Xamarin.Essentials;
    
    namespace WaarStaat.Services
    {
        public class ItemService
        {
            private static string mainDir = FileSystem.AppDataDirectory;
            private static string FileName = "File_Name.txt";
            private static string FilePath;
    
            public ItemService()
            {
                FilePath = Path.Combine(mainDir, FileName);
            }
    
            public async Task SaveDataAsync(ObservableCollection<Item> data)
            {
                using (var writer = new StreamWriter(FilePath, false))
                {
                    var dataJson = JsonConvert.SerializeObject(data);
                    await writer.WriteAsync(dataJson);
                }
            }
    
            public async Task<ObservableCollection<Item>> RetrievDataAsync()
            {
                if (File.Exists(FilePath))
                {
                    using (var stream = await FileSystem.OpenAppPackageFileAsync(FilePath))
                    {
                        using (var reader = new StreamReader(stream))
                        {
                            var fileContents = await reader.ReadToEndAsync();
                            Debug.WriteLine(fileContents);
                            if (!Equals(fileContents, null))
                            {
                                var data = JsonConvert.DeserializeObject<ObservableCollection<Item>>(fileContents);
                                return data;
                            }
                            return null;
                        }
                    }
                }
                else
                {
                    Debug.WriteLine("no file");
                    return null;
                }
    
            }
        }
    }
    

    `

Answers

  • NMackayNMackay GBInsider, University mod
    edited January 30

    @Cing said:
    I am trying to implement a database following this: https://docs.microsoft.com/nl-nl/xamarin/xamarin-forms/app-fundamentals/databases

    But I am getting an aggregate.exception on the main() function when debugging on my iphone.

    I added this to my app.cs:

     ` 
    

    static ItemDatabase database;

        public static ItemDatabase Database
        {
            get
            {
                if (database == null)
                {
                    database = new ItemDatabase(
                      Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "WaarStaatSQLite.db3"));
                }
                return database;
            }
        }`
    

    Then this is the database class:

      
    

    `public class ItemDatabase
    {
    readonly SQLiteAsyncConnection database;

        public ItemDatabase(string dbPath)
        {
            database = new SQLiteAsyncConnection(dbPath);
            database.CreateTableAsync<Item>().Wait(); // aggregate.exception probably from this line
            Debug.WriteLine("Created Database");
        }
    
        //get all items from db async
        public async Task<ObservableCollection<Item>> GetItemsAsync()
        {
            Debug.WriteLine("get items");
            var data = await database.Table<Item>().ToListAsync();
            Debug.WriteLine(data.ToString());
            if(data != null)
            {
                return new ObservableCollection<Item>(data);
            }
            else
            {
                return null;
            }
          
        }
    
        //get all items from db not async
        public async Task<ObservableCollection<Item>> GetItemsNotDoneAsync()
        {
            var data = await database.QueryAsync<Item>("SELECT * FROM [Item] WHERE [Done] = 0");
            return new ObservableCollection<Item>(data);
        }
    
        //get item from db with id
        public async Task<Item> GetItemAsync(int id)
        {
            return await database.Table<Item>().Where(i => i.ID == id).FirstOrDefaultAsync();
        }
    
        //save or update a single item
        public async Task<int> SaveItemAsync(Item item)
        {
            if (item.ID != 0)
            {
                return await database.UpdateAsync(item);
            }
            else
            {
                return await database.InsertAsync(item);
            }
        }
    
        //delete item in db
        public async Task<int> DeleteItemAsync(Item item)
        {
            return await database.DeleteAsync(item);
        }
    }`
    

    I then call this function in the MainPageViewModel:

      
                  `public override async void OnNavigatedTo(INavigationParameters parameters)
                        {
                            var data = await App.Database.GetItemsAsync();
                            if (data != null)
                            {
                                Debug.WriteLine(data.ToString());
                                Items = data;
                            }            
                        }`
    

    OnNavigatedTo is a prism function.
    I first thought maybe the OnNavigatedTo function can't be async but I found a question only saying the OnNavigatedTo function could be async void.

    So I did some debugging using breakpoints and print statements and I found that the code stops in the database constructor on the createTable line. Because the Debug.writeline is never executed. Using a breakpoint on that line didn't help because i couldn't step in on the function.
    I don't know if I needs to implement something in the ios-project because the sample didn't include anything.

    Any help implementing databases is appreciated.

    There is a lot wrong here, I don't mean to be rude. Your database connection should be a platform specific specific dependancy injection.

    I demonstrate how to implement SQlite in Prism on my github site

    https://github.com/mackayn/CrudSample

    As for calling an async event in OnNavigated to, as long as it's not thread blocking your page should load nicely and hydrate it's data from SQLite.

  • CingCing NLMember ✭✭

    Hi @NMackay ,
    Thank you for the quick response.
    And I don't think it is rude. I just followed the sample from microsoft, so it is weird that it isn't complete.

    Although you sample isn't that different the incidentService = as my ItemDatabase, you just have a platform specific connection code. Which I already thought I would need. Thank you very much for the sample I will try it out.

    And for the OnNavigatedTo is found multiple post with you awnsering :smile: https://forums.xamarin.com/discussion/84854/prism-how-call-async-method-in-constructor

  • CingCing NLMember ✭✭

    Hi @NMackay,

    I tried your sample but I am getting an NSInternalInconsistencyException Reason: Application windows are expected to have a root view controller at the end of application launch.
    You register the services with the registerType function but that function is obsolute so i changed it to registerSingleton, guessing because your app should have 1 database. Would that be the right funtion or is the registerInstance better.

    Could you confirm if your code is still working, with that changed? Because maybe some other code of mine is the cause.

    Thanks

  • CingCing NLMember ✭✭

    Hi @NMackay ,
    Could you check if your code still works?

  • NMackayNMackay GBInsider, University mod

    @Cing said:
    Hi @NMackay ,
    Could you check if your code still works?

    I'll have to nuke my nuget cache & update it as it PCL and not .NETStandard 2.0 but yes, pretty sure it will run. The was a sample for a job interview knocked up in 4 hours.

    To help you, you need to chuck a sample on github and I'll help you with the issue as best I can as I have helped many others, I'm not willing to 'guess' what your issue is or re-create it. A repo helps so much.

  • NickANickA USMember ✭✭

    @Cing said:

    Any help implementing databases is appreciated.

    You're code is fine. Everyone has their own "right" way of doing thing. Some people like to over architect the simplest of things.

    Anyway, what does your Item model look like? Do you have any complex types (Arrays, Lists, etc.)? Post your Model Item class.

    Also make sure you have the right sqlite-net-pcl installed. Should be the one by Frank Krueger

  • CingCing NLMember ✭✭
    edited February 13

    @NMackay,
    Yes, I get that and I don't want you to go into to much trouble I thought maybe you still have the sample or used it recently. I really appreciate it for you to take any time at all trying to help me. :)
    I remade your example including my model and stuff: CrudSample2
    And the same with this sample it crashes. On main() function of iOS with the error: Got a SIGABRT while executing native code. This usually indicates
    a fatal error in the mono runtime or one of the native libraries
    used by your application. And then a hole stack trace.

    @NickA ,
    I understand there are multiple ways to do it but I thought there may be a standard way to do it. But maybe xamarin isn't that standardized. Although I have seen that this is the case with more things I encountered. Well the model is really complicated maybe that is the issue. You can see the sample above. And yes I have the sqlite-net-pcl from Frank Krueger.

    Thank you guys for helping.

  • CingCing NLMember ✭✭

    @NickA ,
    After your hint i deleted the functions and the location variables in the model.
    And SUDDENLY worked the app again.

    I also tested with only the currentLocation added but that didn't worked.
    So my problem was the use of the Location class (object) from xam.essentials.
    Is this something just isn't something witch just isn't possible do i need to add something or would an own private location model would work?

  • NickANickA USMember ✭✭

    @Cing said:
    @NickA ,
    After your hint i deleted the functions and the location variables in the model.
    And SUDDENLY worked the app again.

    I also tested with only the currentLocation added but that didn't worked.
    So my problem was the use of the Location class (object) from xam.essentials.
    Is this something just isn't something witch just isn't possible do i need to add something or would an own private location model would work?

    It should work fine. You'll need to add the proper permissions and such. Also, there are a few things you need to setup in your project before using Xamarin.Essentials. Check out these two links:

    https://docs.microsoft.com/en-us/xamarin/essentials/get-started?context=xamarin%2Fxamarin-forms&tabs=windows%2Candroid

    And here is how to set the permissions for GeoLocation:

    https://docs.microsoft.com/en-us/xamarin/essentials/geolocation?context=xamarin%2Fxamarin-forms&tabs=android

  • CingCing NLMember ✭✭

    @NickA
    I hadden't done it in my crudsample but in my own project i had done all that knowing it worked because I have tested the get location.

    Even after doing the proper preparations my sample still gives the same error.

  • CingCing NLMember ✭✭

    @NickA @NMackay
    Sorry to bother you guys again, just one more time if it then doesn't work i quit.

    So I tried a bunch more things and removing more things every step of the way.
    I now have (you can see it on the github)
    A model class for the database which has a variable using my own model with just one variable in it .
    Which crashes when run.

    But if I remove that variable using the model( is called Hello is source code) the app doesn't crash.

    Thus basicly when using another type then the default c# one sqlite doesn't work?
    Or do I need something to make it work?

    I thought about some prefix like the "[Primarykey]" but when looking at the documentation of it I couldn't find anything.

  • NickANickA USMember ✭✭
    edited February 25

    When you add in this line:

    public Hello Something { get; set; } //commeting out this line makes it work

    it crashes because it's trying to add a field with the type of "Hello" in your CreateTable() statement.

    Add this above that line: [Ignore]. So it will look like this:

    [Ignore]
    public Hello Something { get; set; } //commeting out this line makes it work
    

    This when be ignored when the table is created.

  • CingCing NLMember ✭✭

    That must be the same reason the app crashes when having the Location type of xam.essentials.

    But how can I then have Location stored? With a seperate table i guess.
    Then I will go with json serializer and just write it to a file.
    Because that would get to complicated for just saving the data.

    Still Thank you very much @NickA and @NMackay

  • DirkWilhelmDirkWilhelm USMember ✭✭✭✭

    Why not save the serialized location in your database?

  • amirvenusamirvenus USMember ✭✭✭

    I suggest using a very simple library called MTSQL

    https://www.xamarinexpert.it/blog/plugins/mt-sqlite/

  • CingCing NLMember ✭✭
    edited February 25

    Thanks for the respons guys

    @DirkWilhelm said:
    Why not save the serialized location in your database?

    I could actually do that but it would make things to complicated, I now just serliaze the object and write it to a file.

    @amirvenus said:
    I suggest using a very simple library called MTSQL

    https://www.xamarinexpert.it/blog/plugins/mt-sqlite/

    Both the tutorial as the project website are gone so I can't really us that

  • CingCing NLMember ✭✭
    edited February 25 Accepted Answer

    As for the final solution.

    I serialized the ObservableCollection and wrote it to a file.
    I use Newtonsoft.json plugin to do the serialization, here is the code for any futher readers:

    `

    using Newtonsoft.Json;
    using System.Collections.ObjectModel;
    using System.Diagnostics;
    using System.IO;
    using System.Threading.Tasks;
    using WaarStaat.Models;
    using Xamarin.Essentials;
    
    namespace WaarStaat.Services
    {
        public class ItemService
        {
            private static string mainDir = FileSystem.AppDataDirectory;
            private static string FileName = "File_Name.txt";
            private static string FilePath;
    
            public ItemService()
            {
                FilePath = Path.Combine(mainDir, FileName);
            }
    
            public async Task SaveDataAsync(ObservableCollection<Item> data)
            {
                using (var writer = new StreamWriter(FilePath, false))
                {
                    var dataJson = JsonConvert.SerializeObject(data);
                    await writer.WriteAsync(dataJson);
                }
            }
    
            public async Task<ObservableCollection<Item>> RetrievDataAsync()
            {
                if (File.Exists(FilePath))
                {
                    using (var stream = await FileSystem.OpenAppPackageFileAsync(FilePath))
                    {
                        using (var reader = new StreamReader(stream))
                        {
                            var fileContents = await reader.ReadToEndAsync();
                            Debug.WriteLine(fileContents);
                            if (!Equals(fileContents, null))
                            {
                                var data = JsonConvert.DeserializeObject<ObservableCollection<Item>>(fileContents);
                                return data;
                            }
                            return null;
                        }
                    }
                }
                else
                {
                    Debug.WriteLine("no file");
                    return null;
                }
    
            }
        }
    }
    

    `

Sign In or Register to comment.