Sunday, June 16, 2013

Example of Deleting from SQLite DB using C# in Xamarin Android

I tried to use LINQ syntax to delete my objects from a table like shown here, but got an error "Cannot store type:  MyObject":

private static void DeleteOldObjects()
{
 string path = Path.Combine (System.Environment.GetFolderPath (System.Environment.SpecialFolder.MyDocuments), "MyDatabase.db");
 var db = new SQLiteConnection(path,password,false);
 var query = db.Table<MyObject>().Where(rt => rt.Date < DateTime.Now.AddDays(-3));

 if (query != null) {
  foreach (var object in query.ToList<MyObject>()) {
   db.Delete<MyObject>(object);
  }
 }
 db.Commit ();
}

From what I could determine by looking at the SQLite.cs code, it seems like this error is when it's trying to bind my object as a parameter:


internal static void BindParameter (IntPtr stmt, int index, object value)
{
  if (value == null) {
    SQLite3.BindNull (stmt, index);
  } else {
    if (value is Int32) {
      SQLite3.BindInt (stmt, index, (int)value);
    } else if (value is String) {
      SQLite3.BindText (stmt, index, (string)value, -1, NegativePointer);
    } else if (value is Byte || value is UInt16 || value is SByte || value is Int16) {
      SQLite3.BindInt (stmt, index, Convert.ToInt32 (value));
    } else if (value is Boolean) {
      SQLite3.BindInt (stmt, index, (bool)value ? 1 : 0);
    } else if (value is UInt32 || value is Int64) {
      SQLite3.BindInt64 (stmt, index, Convert.ToInt64 (value));
    } else if (value is Single || value is Double || value is Decimal) {
      SQLite3.BindDouble (stmt, index, Convert.ToDouble (value));
    } else if (value is DateTime) {
      SQLite3.BindText (stmt, index, ((DateTime)value).ToString ("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer);
    } else if (value.GetType ().IsEnum) {
      SQLite3.BindInt (stmt, index, Convert.ToInt32 (value));
    } else if (value is byte[]) {
      SQLite3.BindBlob (stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
    } else {
      throw new NotSupportedException ("Cannot store type: " + value.GetType ());
    }
  }
}

That seemed to me like the most intuitive way to perform that task since it's the same syntax used to insert objects, but since it didn't work I found you actually need to pass the primary key of the object you want to delete, like this:

private static void DeleteOldObjects()
{
 string path = Path.Combine (System.Environment.GetFolderPath (System.Environment.SpecialFolder.MyDocuments), "MyDatabase.db");
 var db = new SQLiteConnection(path,password,false);
 DateTime expireDate = DateTime.Now.AddDays(-3));
 var query = db.Table<MyObject>().Where(rt => rt.Date < expireDate );

 if (query != null) {
  foreach (var object in query.ToList<MyObject>()) {
   db.Delete<MyObject>(object.PrimaryKeyId);
  }
 }
 db.Commit ();
}