Post
Ask Question

Insert List Of Items Into Single Row In Xamarin Forms SQLite DB

Most Xamarin.forms developers prefer 'SQLite' for storing data into the local DB. The main limitation of the SQLite DB is  it can't insert a model which contains any of the properties having a list data type. Here we can see how to save the model which contains any of the properties having list data type in the same table in a single row.
 
These are the steps to follow to save the data in the same table in a single row.
 
'SQLiteNetExtension' or 'SQLiteNetExtension.Async' NuGet package is required to install in project solution.
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
Add 'TextBlob' attribute to the model containing List data type property. 
  1. public class Person  
  2. {  
  3.   public Person()  
  4.   {  
  5.      Id = Guid.NewGuid().ToString();  
  6.   }  
  7.   
  8.   [PrimaryKey]  
  9.   public string Id { get; set; }  
  10.   public string Name { get; set; }        
  11.   [TextBlob("AddressesBlobbed")]  
  12.   public List<Address> Addresses { get; set; }  
  13.   // serialized addresses  
  14.   public string AddressesBlobbed { get; set; }   
  15. }  
  16. public class Address  
  17. {  
  18.   public string StreetName { get; set; }  
  19.   public string Number { get; set; }  
  20.   public int PostalCode { get; set; }  
  21.   public string Country { get; set; }  
  22.   public DateTime CurrentDay { get; set; }  
  23.   public double CityNo { get; set; }  
  24.   public bool Values { get; set; }  
  25. }  
Here Text-Blobbed properties are serialized into a text property when saved and deserialized when retrieved automatically. This allows the storing of simple objects in the same table in a single row.
 
These are SQLite queries used to insert and retrieve data from the local DB.
 
Insert Query
  1. await SQLiteNetExtensionsAsync.Extensions.WriteOperations.  
  2.               InsertWithChildrenAsync(LocalDatabaseService.Instance.sQLiteAsyncConnection, per);  
The above query is used to insert data in the local DB. Here we need to pass two parameters. The first parameter is a SQLite connection and the second parameter is model.
 
Retrieve Query
  1. var data = await SQLiteNetExtensionsAsync.Extensions.ReadOperations.  
  2.               GetAllWithChildrenAsync<Person>(LocalDatabaseService.Instance.sQLiteAsyncConnection);  
The above query is used to retrieve data from the local DB. Here we need to give the table model name as a generic type and we need to pass SQLite connection string as a parameter.