SQLite add a list to a table

Question

I am writing a Xamarin Android application using SQLite and am not sure how to add an object to a table where the object has a list.

Here is my model class:

public class TestObject
{
    [PrimaryKey]
    public int Id { get; set; }
    public string name { get; set; }
    public DateTime lastUpdate { get; set; }
    public List<TestItem> items { get; set; }
}

Here is my code to add an object to a table:

public void InsertObjectToDatabase<T>(string databasePath, T objType)
{
    var db = new SQLiteConnection (databasePath);
    db.CreateTable(typeof (T));
    db.InsertOrReplace (objType);
}

Here is my code to add a TestObject to a table:

TestObject testObject = new TestObject ();
testObject.Id = 1;
testObject.name = "Test Object 1";
testObject.lastUpdate = DateTime.Now;

sQLiteService.InsertObjectToDatabase<TestObject> (filename, testObject);

This is the error that I am getting:

System.NotSupportedException: Don't know about System.Collections.Generic.List`1[LearningSQLite.TestItem]

Is it possible add a list to a SQLite table?

Thanks in advance

score 1 · Accepted Answer · answered Sep 18 '14 at 05:37

1

Create another table and refer to the parent table with a foreign key. Once you insert the row in parent, insert all the items from the list in the child. Read more here http://www.sqlite.org/foreignkeys.html

answered Sep 18 '14 at 05:37

eduyayo

2,020
2
15
35

score 1 · Answer 2 · answered Sep 18 '14 at 18:13

If you are using SQLite.Net-PCL you can use the IBlobSerializer interface to store complex types to a BLOB (byte array). Here is a unit test class that provides more info:

https://github.com/oysteinkrog/SQLite.Net-PCL/blob/master/tests/BlobSerializationTest.cs

For the serializer you can either implement your own or use something like JSON serializers to store the data as JSON.

I am using the BLOB interface to use SQLite as key-value pair caching mechanism:

https://github.com/XForms/Xamarin-Forms-Labs/blob/master/src/Xamarin.Forms.Labs/Plugins/Caching/Xamarin.Forms.Labs.Caching.SQLiteNet/SQLiteSimpleCache.cs

SQLite add a list to a table

2 Answers2