Post
Ask Question

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

Introduction 

 
Most of the xamarin.forms developers prefer 'SQLite' for storing data into local DB. This 'SQLite' data base engine will help to save data objects into local DB or user local device. The main limitation of the SQLite DB is can't able to insert a model which contains any of the property have a list data type. So if we have a situation to save a list of objects into a single row, we have in a trouble. To resolve this problem, here we can see how to save the model which contains any of the properties that have list data type in the same table in a single row.
 
These are the steps to follow to save the list of data in the same table in a single row.
 
Step 1
 
'SQLiteNetExtension' or 'SQLiteNetExtension.Async' NuGet package is required to install in the project solution.
 
First, we need to install these packages in all projects in our solutions from the NuGet gallery. If you have any async operations regarding the database, required to install 'SQLiteNetExtensions.Async' other wise 'SQLiteNetExtensions' will be fine.
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
Step 2
 
Add a 'TextBlob' attribute to the model contains the List data type property.
 
The next step is to create a model to create a table structure and save the data in the local DB. While creating a model we need to add a 'TextBlob' attribute to a list type property, as shown below for saving a list of data in a single row and single column.
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
Here Text-Blobed properties are serialized into a text property when saved and deserialized when retrieved automatically. This allows storing simple objects in the same table in a single row.
 
Step 3
 
These are SQLite queries used to insert and retrieve data from local DB.
 
The next step after creating the table is to save the data by using an insert query. If we want to save data in the local DB, we need to formulate the query as shown below and run the code.
 
Insert Query
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
The above query is used to insert data in local DB. here we need to pass two parameters. the first parameter is SQLite connection and the second parameter is model.
 
Retrieve Query
 
Once table creating and data saving is completed, whenever we required to get the data from local DB, we need to write the query like below to get the data.
 
Insert List Of Items Into Single Row In Xamarin Forms SQLite DB
 
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.