How to retrieve datas with SQLlite for XAmarin

Question

Hi. I have two tables in a SQLlIte DB(in two distincts files). The first is Nome and the second is Luogo. The first table contains a city and others datas and Luogo,the second table contains the city name and various atttributes. I need to make a select "SELECT * FROM table Nome, Luogo WHERE Nome.citta= Luogo.citta". and get the attributes of the city.

But even if is a simple statement I don't know how to deal or retrieve the values. In SQLServer is simple because after the query done with SQLCommand you can access to the data with SQLDataReader. The object is read in a loop with Reader.Read().

I need to do the same operation with SQLlite for Xamarin

the code looks like this example found on internet:

SQLiteAsyncConnection sQLiteAsync = new SQLiteAsyncConnection(ini_Path);

        var stocksStartingWithA = sQLiteAsync.QueryAsync<Luogo>("SELECT * FROM Items WHERE Symbol = ?", "A");

but I don't know how use stocksStartingWithA for retriving the results of the query.

Thanks in advanced for any reply.

Answer 1

Hello,​

Welcome to our Microsoft Q&A platform!

Try creating another table to place the selected data.

   public class TableA  
   {  
       [PrimaryKey]  
       public int TableA_ID { get; set; }  
       public string TableA_Name { get; set; }  
       public string Notes { get; set; }  
   }  
     
   public class TableB  
   {  
       [PrimaryKey]  
       public int TableB_ID { get; set; }  
       public string TableB_Name { get; set; }  
       public string Description { get; set; }  
   }  
     
   public class TheItem  
   {  
       public int TableA_ID { get; set; }  
       public string TableA_Name { get; set; }  
       public string Notes { get; set; }  
       public int TableB_ID { get; set; }  
       public string TableB_Name { get; set; }  
       public string Description { get; set; }  
   }  

Then query the data from the two tables with the following code.

   public Task<List<TheItem>> GetSelectedItems()  
   {  
       return Database.QueryAsync<TheItem>(@"SELECT * FROM TableA,TableB WHERE TableA.TableA_ID = TableB.TableB_ID;");  
   }  

Best Regards,

Jarvan Zhang

---

If the response is helpful, 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.