Is there a better SQLite update / insert pattern than this?

Question

I've got a function which updates an existing record or inserts a new one, but it looks very clunky to me.

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
    db.RunInTransaction(() =>
    {
        var y = db.Table<MyClass>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();

        bool insert = false;

        if (y == null)
        {
            y = new MyClass();
            y.GUID = this.GUID;
            insert = true;
        }

        y.VersionNumber = this.VersionNumber;
        y.WordCount = this.WordCount;
        y.Updated = DateTime.Now;
        y.Checksum = this.Checksum;
        y.Deleted = this.Deleted;
        y.Source = this.Source;

        if (insert)
        {
            db.Insert(y);
        }
        else
        {
            db.Update(y);
        }
    });
}

The only alternative I can see is a bigger if else, which looks neater but has some argument repitition

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
    db.RunInTransaction(() =>
    {
        var y = db.Table<PoeFileVersion>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();

        if (y == null)
        {
            y = new PoeFileVersion();
            y.GUID = this.GUID;

            y.VersionNumber = this.VersionNumber;
            y.WordCount = this.WordCount;
            y.Updated = DateTime.Now;
            y.Checksum = this.Checksum;
            y.Deleted = this.Deleted;
            y.Source = this.Source;

            db.Insert(y);
        }
        else
        {               
            y.VersionNumber = this.VersionNumber;
            y.WordCount = this.WordCount;
            y.Updated = DateTime.Now;
            y.Checksum = this.Checksum;
            y.Deleted = this.Deleted;
            y.Source = this.Source;

            db.Update(y);
        }
    });
}

This is not unique to SQLite of course - I've come up against the same ugly code in Linq-to-SQL. Is there a better pattern?

Whenever you have common code, factor it out to a new routine. CopyThisToY() — Snowbody
– Snowbody, Commented Mar 13, 2015 at 18:01
I assume simply using REPLACE is not an option? That way you could just skip the query/branch. — Mario
– Mario, Commented Mar 25, 2015 at 8:43
What you may and may not do after receiving answers - Basically, please do not put new code in the question. If you want the new code reviewed, make a new question. If you want to say that you went a completely different direction and feel like sharing, make an answer reviewing your own code. If you just want to say "I applied the changes", maybe make a comment. — Pimgd
– Pimgd, Commented Feb 11, 2016 at 13:06

Pimgd · Accepted Answer · 2016-02-11 13:09:16Z

1

The second code block is the clunky one, it has repetition which the first one doesn't. If you wanted to refactor the first example you would need to go higher up and do the check for existing record before you make the call to save or update then have a method for save and one for update, but this would lead to some duplicate code.

From what you have posted I'd stick with the first one.

edited Feb 11, 2016 at 13:09

Pimgd

22.5k5 gold badges68 silver badges144 bronze badges

answered Mar 25, 2015 at 8:21

matt_lethargic

1764 bronze badges

Add a comment |

roryok · Accepted Answer · 2016-02-11 13:53:55Z

I've accepted matt's answer, and gone with my original block of code. I ended up modifying it slightly, so I thought I'd post my updated version.

using (SQLiteConnection db = new SQLiteConnection(AppSettings.DbPath))
{
    db.RunInTransaction(() =>
    {
        var y = db.Table<MyClass>().Where(x => x.GUID == this.GUID && x.VersionNumber == this.VersionNumber).FirstOrDefault();

        bool insert = (y == null);

        if (insert)            
            y = new MyClass{ GUID = this.GUID, VersionNumber = this.VersionNumber };

        y.WordCount = this.WordCount;
        y.Updated = DateTime.Now;
        y.Checksum = this.Checksum;
        y.Deleted = this.Deleted;
        y.Source = this.Source;

        if (insert)
            db.Insert(y);
        else
            db.Update(y);
    });
}

I thought about doing this

y = (insert ? new MyClass{ GUID = this.GUID, VersionNumber = this.VersionNumber } : y);

But I think what I have is more readable.

Stack Exchange Network

Is there a better SQLite update / insert pattern than this?

2 Answers 2

You must log in to answer this question.

Hot Network Questions

Is there a better SQLite update / insert pattern than this?

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions