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

Save model contains a list of objects into a single row in the same table using 'SQLiteNetExtensions' NuGet package in xamarin forms.

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.

 

 

Add 'TextBlob' attribute to the model containing List data type property. 

public class Person
{
  public Person()
  {
     Id = Guid.NewGuid().ToString();
  }

  [PrimaryKey]
  public string Id { get; set; }
  public string Name { get; set; }
  [TextBlob("AddressesBlobbed")]
  public List<Address> Addresses { get; set; }
  // serialized addresses
  public string AddressesBlobbed { get; set; }
}
public class Address
{
  public string StreetName { get; set; }
  public string Number { get; set; }
  public int PostalCode { get; set; }
  public string Country { get; set; }
  public DateTime CurrentDay { get; set; }
  public double CityNo { get; set; }
  public bool Values { get; set; }
}

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

await SQLiteNetExtensionsAsync.Extensions.WriteOperations.
              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

var data = await SQLiteNetExtensionsAsync.Extensions.ReadOperations.
              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.