Xamarin forms trouble updating Boolean with SQLite

Question

I'm trying to create a simple Notes app with Xamarin forms. I have Entrys, a Date and then a switch to determine if the note is done or not.

When creating a new note everything works fine with inserting the data. But when I try to update a note the Switch Element always inserts as "false", the other data works with the update. I have no idea what the problem might be?

Here is the method for updating the note:

 public void UpdateNote(string _name, string _note, string _date, bool _done, int noteId)
    {
        _sqlconnection.Query<Notes>("UPDATE [Notes] SET Name='" + _name + "', Note='" + _note + "', Date='" + _date + "', Done='" + _done + "' WHERE [ID]='" + noteId + "'");
    }

And here is the click event from the Views code behind:

 UpdateClick.Clicked += delegate {
            _notes = new Notes();
            _notesDb = new NotesDB();
            _notes.Name = name.Text;
            _notes.Note = note.Text;
            _notes.Date = DateTime.Now.ToString();
            _notes.Done = done.IsToggled;
            _notesDb.UpdateNote(_notes.Name, _notes.Note, _notes.Date, _notes.Done, noteId);
            Navigation.PopAsync();
        };

Answer 1

There is no boolean type in SQLite so you have to use 0 for false and 1 for true. Also, instead of using Query you should use Execute:

_sqlconnection.Execute("UPDATE [Notes] SET Name='" + _name + "', Note='" + _note + "', Date='" + _date + "', Done='" + _done ? 1 : 0 + "' WHERE [ID]='" + noteId + "'");

From SQLite documentation:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Answer 2

SQLite stores booleans as 0/1:

var _doneInt = _done : 1 : 0;

---

public void UpdateNote(string _name, string _note, string _date, bool _done, int noteId)
{
    var _doneInt = _done : 1 : 0;
    _sqlconnection.Query<Notes>("UPDATE [Notes] SET Name='" + _name + "', Note='" + _note + "', Date='" + _date + "', Done='" + _doneInt + "' WHERE [ID]='" + noteId + "'");
}

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Ref: https://www.sqlite.org/datatype3.html

Answer 3

Could you not just use update? sent the object in to update then let sqlite.net work it out :

public void UpdateNote(Notes _note)
{
    _sqlconnection.Update(_note)
}

If you look at the update test call in here this is assuming that your noteId field has [PrimaryKey]

This seems to work for me:

Model:

    public class Notes
    {
        [PrimaryKey, AutoIncrement, NotNull] 
        public int Id { get; set; } 
        public string Name { get; set; } 
        public string Note { get; set; } 
        public string Date { get; set; } 
        public bool Done { get; set; } 
    }

test method:

    public bool testForNotes(Notes n)
    {
        _sqliteConnection.CreateTable<Notes>();
        _sqliteConnection.Insert (n);
        n.Done = true;
        var i = _sqliteConnection.Update (n);
        return (i > 0);
    }

UNIT test

    public void GetNotes ()
    {
        NoteDataStore _noteDataStore = new NoteDataStore (new SqlConnection());
        var note = new Notes () {
            Name = "TestName",
            Note = "TestNote",
            Date = DateTime.Now.ToString (),
            Done = false
        };
        var show = _noteDataStore.testForNotes (note);
        Assert.IsNotNull (show);
    }