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

Question

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
            }));

        }

Answer 1

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.