Upsert in SQLite 3 with Linq expression

Question

Possible Duplicate:
SQLite - UPSERT not INSERT or REPLACE
How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I'm developing metro app using Windows 8 release preview and C#(VS 2012), I'm new to SQLite, I integrated SQLite 3.7.13 in my App and it is working fine, In MYSQL we have like

INSERT INTO tablename (id, name)
VALUES (21, 'foo')
ON DUPLICATE KEY UPDATE name = 'boo';

How can we achieve this in SQLite 3 using Linq expression, Now I'm inserting the record and catching SQLiteException and updating that record on exception.Please observe my code below

var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,"Student.db");
DateTime LastUpdated = DateTime.UtcNow;
using (var db = new SQLite.SQLiteConnection(dbPath))
{   
    try
    {
        db.Insert(new studentRecord() { sid = 21, name = 'foo', last_updated = LastUpdated });                   
    }
    catch(SQLite.SQLiteException ex)
    {
        db.Update(new studentRecord() { sid = 21, name = 'boo', last_updated = LastUpdated });
    }
}

Where studentRecord is my table name. I'm not sure this is right way, Is there any way that we can achieve in a single query, Please help me. Thanks in advance

What if there is another exception thrown? Something like "Connection lost", it would throw another error when you try to update in the catch and would crash since you don't try-catch that. Perhaps there is a better way like your mysql, but I would rather check whether the item already exists and update or insert based on that query. — Silvermind, Aug 22 '12 at 15:02

Marcelo Cantos · Answer 1 · 2012-08-24T00:10:04.997

The solution you've proposed has a race-condition. After the insert fails, someone else might delete the row, so that the update fails too. If you don't have concurrent writers, this is fine (though you might need to be more selective in your exception handling, in case something else went wrong). If you do…

Use the conflict clause of SQLite's INSERT statement:

INSERT OR REPLACE INTO …

I doubt you'll be able to access this directly from LINQ, so you'll probably have to use raw SQL via DataContext.ExecuteQuery.

Upsert in SQLite 3 with Linq expression

1 Answers1