locked
How to create and populate a local SQLite db with API json data? RRS feed

  • Question

  • User43400 posted

    Maybe my question is a comum question, but I didn't find the right answered in any other place.

    I'm creating a Xamarin.Forms application that will consume data from an API Rest that I created in C#.

    What I'm trying to do is to store all json data returned from the API to a local SQLite db, then I'll use that data in all the application interfaces.

    Till here all is well:

    public async void GetProduct()
        {
            var httpClient = new HttpClient();
            var response = await httpClient.GetStringAsync("localhost/api/products");
            var product = JsonConvert.DeserializeObject<List<Product>>(response);
    
            lvProducts.ItemsSource = propriedade;
    
        }
    

    What I want is to populate a table in local db instead o populating a listview.

    Do you have some simple implementable code that I can use to reach this goal? I'm very new in cross-platform development.

    Thanks,

    Claudionir Queiroz

    Tuesday, February 4, 2020 6:03 PM

Answers

  • User43400 posted

    The problem was in SQLiteAsyncConnection. When I used SQLiteConnection only, the problem was solved. Thanks so much.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 7, 2020 2:16 PM
  • User369979 posted

    If you have solved this issue try to mark it. Therefore, other community members could get the right direction if they have related issues.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Monday, February 10, 2020 10:41 AM

All replies

  • User369979 posted

    We could store the data directly into a local database using SQLite: https://docs.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/databases Firstly, initialize the database:

    SQLiteAsyncConnection dataBase;
    protected async override void OnAppearing()
    {
        base.OnAppearing();
    
        var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
        var databasePath = Path.Combine(basePath, "SQLite.db3");
    
        dataBase = new SQLiteAsyncConnection(databasePath);
        await dataBase.CreateTableAsync(typeof(Product));
    }
    

    Then we could store the data which is retrieved from the api:

    public async void GetProduct()
    {
        var httpClient = new HttpClient();
        var response = await httpClient.GetStringAsync("localhost/api/products");
        var product = JsonConvert.DeserializeObject<List<Product>>(response);
    
        lvProducts.ItemsSource = propriedade;
    
        await dataBase.InsertAllAsync(product);
    }
    

    Get the data from the database:

    var product = await dataBase.Table<Product>().ToListAsync();
    // Set it to your list view's items source if needed
    

    I simply combine the database with the page here, you could separate it to a single constant class as the documentation did.

    Wednesday, February 5, 2020 2:26 AM
  • User43400 posted

    Thanks friend, I tried to implement this code in my application, but I think I did some mistake. Can you please share a sample of doing it in a button click event? My idea is to create a sync button to populate the local table.

    After that, I'll get the data from the local table and show them in different screens, do you understand?

    That's what I did:

    SQLiteAsyncConnection dataBase;
    public string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "localdb.db3");
    
                private void Button_Clicked(object sender, EventArgs e)
                {
                    if (!File.Exists(dbPath))
                    {
                        getProducts();
                    }
                    else
                    {
                        DisplayAlert("Confirmação", CreateDb(dbPath), "OK");
                    }
                }
    
                public async void getProducts()
                {
                    try
                    {
                        dataBase = new SQLiteAsyncConnection(dbPath);
                        await dataBase.CreateTableAsync(typeof(Models.Product));
    
                        var httpClient = new HttpClient();
                        var response = await httpClient.GetStringAsync("locahost/api/products");
                        var product = JsonConvert.DeserializeObject<List<Models.Product>>(response);
                        await dataBase.InsertAllAsync(product );
                        var products = await dataBase.Table<Models.Product>().ToListAsync();
                        await DisplayAlert("Resultado", "Deu certo", "OK");
                    }
                    catch (SQLiteException ex)
                    {
                        await DisplayAlert("Erro", "O seguinte erro ocorreu: " + ex.Message, "OK");
                    }
                }
    
                public string CreateDb(string caminho)
                {
                    var db = new SQLite.SQLiteAsyncConnection(caminho, SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.ReadWrite);
                    return "Banco de dados criado com sucesso";
    
                }
    

    But it didn't work. The following error is displaying: Unhanded Exception: System.TypeInitializationException: The type initializer for 'SQLite.SQLiteConnection' threw an exception

    Wednesday, February 5, 2020 2:06 PM
  • User369979 posted

    I made a simple project here you could refer to the attachment for the specific code. Here is the effect It used insert button to store data into the database and get button to retrieve data.

    Thursday, February 6, 2020 10:20 AM
  • User43400 posted

    I tried to implemente according you example, but the following error: System.TypeInitializationException: 'The type initializer for 'SQLite.SQLiteConnection' threw an exception.' in this part of the code:

    await dataBase.CreateTableAsync(typeof(Product));

    I cannot advance from here. That's is my full code:

        public string apiPath = "http://localhost/api/";
                public string apiName;
    
        public string apiName; 
    
    public HomePage()
                    {
                        InitializeComponent();
                    }
    
                    SQLiteAsyncConnection dataBase;
                    protected async override void OnAppearing()
                    {
                        base.OnAppearing();
    
                        var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
                        var databasePath = Path.Combine(basePath, "localdb.db3");
    
                        dataBase = new SQLiteAsyncConnection(databasePath);
    
                        await dataBase.CreateTableAsync(typeof(Propriedade)); //error goes here
    
                    }
    
                    private void Button_Clicked(object sender, EventArgs e)
                    {
                        getProducts();
    
                    }
    
    
    
                    protected async void getProducts()
                    {
                        try
                        {
                             //Definindo a API a ser chamada
                            apiName = "products";
    
                            //Conectando à API
                            var httpClient = new HttpClient();
                            var response = await httpClient.GetStringAsync(apiPath + apiName);
    
                   //Recuperando os dados da API e deserializando
                            var prod = JsonConvert.DeserializeObject<List<Product>>(response);
    
                            List<Product> product = new List<Product>();
                            foreach (var item in prod)
                            {
                                prop.Add(new Product{
                                    idProduct = item.idProduct,
                                    nomeProduct = item.nomeProduct ,
                                    descProduct = item.descProduct,
                                    priceProduct = item.priceProduct,
                                    statusProduct = item.statusProduct
                                    });
    
                            }
                            await dataBase.InsertAllAsync(prop);
    
                        }
                        catch (HttpRequestException ex)
                        {
                            await DisplayAlert("Erro", "O seguinte erro ocorreu: " + ex.Message, "OK");
                        }
    
                    }
    
        public class Product
            {
                public int idProduct { get; set; }
                public string nomeProduct { get; set; }
                public string descProduct { get; set; }
                public double priceProduct  { get; set; }
                public string statusProduct { get; set; }
            }
    

    My ideia is to populate the SQLite local db with the API data in the button click. If it has a progressbar it would be better, but I'm not having success.

    Is it posible to adapt your code to help me reaching this goal?

    Thanks, so much.

    Thursday, February 6, 2020 5:40 PM
  • User369979 posted

    Have you created a database using this path before? If the database has been created but you used another class to connect it there will be an exception. If you want to create a new database try to change the file path:

    var databasePath = Path.Combine(basePath, "newdb.db3");
    
    Friday, February 7, 2020 9:22 AM
  • User43400 posted

    The problem was in SQLiteAsyncConnection. When I used SQLiteConnection only, the problem was solved. Thanks so much.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 7, 2020 2:16 PM
  • User369979 posted

    If you have solved this issue try to mark it. Therefore, other community members could get the right direction if they have related issues.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Monday, February 10, 2020 10:41 AM
  • User398627 posted

    Hi

    Note : Its Working

    Can you give me the Select query with where condition?

    I used the below code, But its return null values only

    public Task GetItemAsync(string productname) { return dataBase.Table().Where(i => i.ProductName== productname).FirstOrDefaultAsync(); }

    Saturday, December 26, 2020 7:16 AM
  • User399779 posted

    var aa=await dataBase.InsertAllAsync(responseData.Results.LabTestData);

    When i am use dataBase.InsertAllAsync that time it allow repetition of my primary key column. My primary key column is TestId how am resolve this issue

    Friday, March 5, 2021 12:31 PM