update different values for each row in a table(sqlite)

Question

I have an object, say a note, whose parameters I want to save in a table(content, size, width, height, etc). With having the sql string updateNotes:

UPDATE NOTES 
SET 
    CONTENT=@content, 
    POSX=@posX, 
    POSY=@posY,
    COLOR=@color,
    ORIENTATION=@orientation,
    HEIGHT=@height,
    WIDTH=@width,
    AUTHOR=@autho 
WHERE SESSION =@sid

    public void save()
    {
        SQLiteConnection conn = new SQLiteConnection(connectionString);

        foreach (Note note in notes)
        {
            using (SQLiteCommand comm = new SQLiteCommand(updateNotes, conn))
            {
                comm.Parameters.AddWithValue("@content", note.getNoteItemContent());
                comm.Parameters.AddWithValue("@posX", note.getSvi().Center.X);
                comm.Parameters.AddWithValue("@posY", note.getSvi().Center.Y);
                comm.Parameters.AddWithValue("@sid", sid);
                comm.Parameters.AddWithValue("@color", note.getColor());
                comm.Parameters.AddWithValue("@orientation", note.getSvi().Orientation);
                comm.Parameters.AddWithValue("@height", note.getSvi().ActualHeight);
                comm.Parameters.AddWithValue("@width", note.getSvi().ActualWidth);
                comm.Parameters.AddWithValue("@author", note.getAuthor());

                try
                {
                    conn.Open();
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                catch (Exception e) { Microsoft.Surface.UserNotifications.RequestNotification("Database error", "Could not write to the database:" + e.Message); }
                finally
                {
                    if (conn != null) { conn.Close(); }
                    listLoaded = false;
                }
            }
        }
    }

The method above does indeed update the rows in the table but with one value for all the rows that result from the query.

As a solution I thought of reading the note id first and then iterate (i++), but given that some notes(represented as rows in the table) may be deleted, the id will not necessarily follow a consecutive numbering.

Another solution was to query the database for all the rows(notes) for the given session(sid) and store their id in an array and the update the notes whose id can be found in the array.

Do you know of any other better optimised solution? Or do you reckon I should use the array for storing the id's of the rows to be updated and the apply the query.

Thanks!

Answer 1

We seem to have resolved this in the question's comments but just to be clear on the answer (and hopefully give you something to accept), here is a summary:

When creating note objects, the data constituting those notes is selected from the database. As well as selecting outward facing data such as the note colour and content, we also select the primary key of each note and store this as a property of the note object.

Now, when a note object is updated, you can include its id in the where clause for the corresponding update statement and hence only update the one row corresponding to the modified note.

Glad I could provide some "enlightenment" -- as you put it.