1

I am storing a list of objects of a class to a SQLite table. The class has 30 strings and I have roughly 6000 objects of the class in my List. It's only 6MB. Breaking up this list and storing that takes almost a minute. But can I store the entire list as one? Here is my code so far: 

    List<TheClass> ListOfObjects;

    public static void AddAllObjects() {
        for (int i = 0; i < ListOfObjects.Count; i++) {
            AddObject(ListOfObjects[i]);
        }
    }

    public static void AddObject(Object obj) {
        database.SaveItem(obj);
    }

    public void SaveItem<T>(T item) {
        connection.Insert(item);
    }
james
  • 171
  • 1
  • 1
  • 10
  • You want to store the list as one row? Or do you want to store each object in the list as one row? What I mean is, are you looking to just serialize your list into a blob, or are you looking for something like bulk insert for sqlite? – Lasse V. Karlsen Mar 29 '17 at 20:29
  • @LasseV.Karlsen My current method seems incredibly inefficient mainly because of the amount of inserts. I would like to reduce that number some how. Perhaps a blob – james Mar 29 '17 at 20:33
  • It is possible with storing a xml into the database. But you need to serialize the data. – Jeroen van Langen Mar 29 '17 at 20:35
  • @JeroenvanLangen Serialising takes a lot of time too. I'm looking for a way to just store the List as it is. Like a blob – james Mar 29 '17 at 20:38
  • @james serialization is still required when storing it as a blob. The advantage of xml is, that sql can parse and select from it server side. [example](http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008) – Jeroen van Langen Mar 29 '17 at 20:40
  • @james If you do not like the option of serialization, then you also can use asynchronism – Palindromer Mar 29 '17 at 20:42
  • Storing it as a serialised JSON object with Newtonsoft.NET in a single string, has decreased writing to 12 seconds. But Reading has increased from 3 seconds to 7 seconds... – james Mar 29 '17 at 21:34
  • 2
    @james Regarding the original code, are you performing 6000 inserts within ***one*** transaction?... – SushiHangover Mar 29 '17 at 23:40

1 Answers1

1

Object SQLiteConnection of sqlite-net has the method InsertAll.

List<Object> models = new List<Object>();
// ...
// LocalConnection conn = ...;
SQLiteConnection db = conn.GetSQLiteConnection();
db.InsertAll(models,true); // the true means "run in transaction"

https://github.com/oysteinkrog/SQLite.Net-PCL/blob/master/src/SQLite.Net/SQLiteConnection.cs

By the way it's implemented as a foreach inside a transaction, so I guess it's the only way, at least using sqlite-net.

Mauro Piccotti
  • 1,807
  • 2
  • 23
  • 34