Load sqllite from webapi; when webapi is down load list from sqllite

Anonymous
2023-11-25T21:58:52.7166667+00:00

In .NET MAUI mobile application, Upon success code I want to load sqllite table from webapi https://sheet2api.com/v1/wlS0h0USxm9p/quiz

If https://sheet2api.com/v1/wlS0h0USxm9p/quiz is down or not connected to internet I want to load from sqlite

Questions = JsonConvert.DeserializeObject<List<InQuestion>>(jsonString); //When api is down load this from sqllite.

Below is code for webapi json.

    public List<Question> Questions { get; set; } = new List<Question>();
 public class InQuestion


    {

        public int No { get; set; }

        public string QuestionTitle { get; set; }

        public string Opt1 { get; set; }

        public string Opt2 { get; set; }

        public string Opt3 { get; set; }

        public string Opt4 { get; set; }

        public string Answer { get; set; }
    }    


  public class Question
    {
        public string QuestionTitle { get; set; } = string.Empty;
        public IEnumerable<string> Options { get; set; } = new List<string>();
        public string Answer { get; set; } = string.Empty;
    }






  async Task<List<InQuestion>> GetApiData()
        {
            string apiUrl = "https://sheet2api.com/v1/wlS0h0USxm9p/quiz";
            List<InQuestion> Questions = new List<InQuestion>();
            using (HttpClient client = new HttpClient())
            {
                try
                {
                    // Make a GET request to the API
                    HttpResponseMessage response = await client.GetAsync(apiUrl);

                    // Check if the request was successful (status code 200)
                    if (response.IsSuccessStatusCode)
                    {
                        // Read the content of the response
                        string jsonString = await response.Content.ReadAsStringAsync();


                        // Deserialize the JSON string into an object
                        Questions = JsonConvert.DeserializeObject<List<InQuestion>>(jsonString);

                                    // truncate sqlLIte table Load sqlite table  from  Questions
                    }
                    else
                    {
                        // Load  Questions from sql lite  table
                    }
                }
                catch (Exception ex)
                {
                    
                }
            }
            return Questions;
        }



   private async Task LoadQuestionsAsync()
        {




            List<InQuestion> res = await GetApiData();

            Questions.AddRange(res.Select(r => new Question
            {
                QuestionTitle = r.QuestionTitle,
                Options = new List<string>() { r.Opt1, r.Opt2, r.Opt3, r.Opt4 },
                Answer = r.Answer
            }));

        }
.NET MAUI
.NET MAUI
A Microsoft open-source framework for building native device applications spanning mobile, tablet, and desktop.
3,198 questions
0 comments No comments
{count} votes

Accepted answer
  1. Leon Lu (Shanghai Wicresoft Co,.Ltd.) 71,921 Reputation points Microsoft Vendor
    2023-11-27T07:54:40.5666667+00:00

    Hello,

    Firstly, you can refer to this document about how to use sqlite db in the MAUI

    Second, please sqlite-net-pcl 1.6.292. Do not install the latest version, because I get the exceptions when init the sqliteDB.

    Next, you can add the PrimaryKey for your No in the InQuestion

     public class InQuestion
       {
           [PrimaryKey]
           public int No { get; set; }
    ...
       }
    

    Third, I create a custom DB class for CRUD. As note, please configure app constants before you add following custom DB class.

    public class QuestionsDatabase
    {
         SQLiteAsyncConnection Database;
         async Task Init()
         {
             if (Database is not null)
                 return;
            Database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);
             var result = await Database.CreateTableAsync<InQuestion>();
         }
         public async Task<List<InQuestion>> GetItemsAsync()
         {
             await Init();
           var res=  await Database.Table<InQuestion>().ToListAsync();
             return res;
         }
        public async Task<InQuestion> GetItemAsync(int id)
         {
             await Init();
             return await Database.Table<InQuestion>().Where(i => i.No == id).FirstOrDefaultAsync();
         }
    
    
        public async Task<int> SaveItemAsync(InQuestion item)
         {
             await Init();
             InQuestion res = await GetItemAsync(item.No);
             if (res != null)
                 return await Database.UpdateAsync(item);
             else
                 return await Database.InsertAsync(item);
         }
    
    
        public async Task<int> DeleteItemAsync(InQuestion item)
         {
             await Init();
             return await Database.DeleteAsync(item);
         }
    }
    

    In the end, you can Load Questions from sqllite table by IQuestions = await questionsDatabase.GetItemsAsync();. As note: If the first time when you load the api data, it will push data to the sqlite DB.

    
    async Task<List<InQuestion>> GetApiData()
      {
          string apiUrl = "https://sheet2api.com/v1/wlS0h0USxm9p/quiz";
          List<InQuestion> IQuestions = new List<InQuestion>();
          using (HttpClient client = new HttpClient())
          {
              try
              {
                  // Make a GET request to the API
                  HttpResponseMessage response = await client.GetAsync(apiUrl);
    
    
                 // Check if the request was successful (status code 200)
                  if (response.IsSuccessStatusCode)
                  {
                      // Read the content of the response
                      string jsonString = await response.Content.ReadAsStringAsync();
    
    
                      // Deserialize the JSON string into an object
                      IQuestions = JsonConvert.DeserializeObject<List<InQuestion>>(jsonString);
    
    
                     // truncate sqlLIte table Load sqlite table  from  Questions
    //If the first time when you load the api data, it will push data to the sqlite DB.
                      List<InQuestion> itemInTheDB= await questionsDatabase.GetItemsAsync();
                      if (itemInTheDB.Count==0)
                      {
                          foreach (InQuestion item in IQuestions)
                          {
                             await questionsDatabase.SaveItemAsync(item);
                          }
                      }
                     
                  }
                  else
                  {
                      // Load  Questions from sql lite  table
                      IQuestions = await questionsDatabase.GetItemsAsync();
                  }
              }
              catch (Exception ex)
              {
    
    
             }
          }
          return IQuestions;
      }
    

    By the way, I init the QuestionsDatabase class in the onappearing method, you can init it in the constructor as well.

    QuestionsDatabase questionsDatabase;
    protected override void OnAppearing()
    {
         base.OnAppearing();
         questionsDatabase = new QuestionsDatabase();  
    }
    

    Best Regards,

    Leon Lu


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful