How to combine two SQLite statements running in C#?

Question

I have these two statements:

db2.Execute(" UPDATE CLICKHISTORY SET " +
            " DAYOFYEAR = " + dayOfYear + " , " +
            " YEAR = " + year + " , " +
            " MONTH = " + month + " , " +
            " DAY = " + day + " , " +
            " BTNACOUNT = BTNACOUNT + 1 WHERE YYMMDD = " + yymmdd );
db2.Execute(" INSERT INTO CLICKHISTORY " +
            " (YYMMDD,DAYOFYEAR,YEAR,MONTH,DAY,BTNACOUNT) " +
            " VALUES ( " +
              yymmdd + " , " +
            dayOfYear + " , " +
            year + " , " +
            month + " , " +
            day + " , " +
            "1) WHERE changes() = 0");

What I would like to do is to check if changes() = 0 in the first statement before running the second statement.

Can anyone tell me how I can group together these two statements in to one so I can check the value of changes()?

Answer 1

Assuming db2 is of type SQLite.SQLiteConnection, you can use the return value of the Execute method to find out the number of affected rows - something like:

int rowsAffected = db2.Execute("UPDATE...");
if (rowsAffected == 0) {
    rowsAffected = db2.Execute("INSERT...");
}

Answer 2

In general, you can combine sqlite statements using semicolon ;.

But as I understand, the real question here is: How to conditionally insert values in SQLite?

You cannot use WHERE with INSERT INTO table VALUES(...), but use can use INSERT INTO table SELECT ... syntax instead and add a WHERE clause to select.

Example

Let's say I have a simple table: scores (name varchar(20), score int). I want to update a row or insert a new one if there's nothing to update yet.

var name = "my team";
var sql  = $"update scores set score = score+1 where name = '{name}';" 
         + $"insert into scores(name, score) select '{name}', 0 where changes() = 0" ;

var cmd = new SQLiteCommand(sql, conn);
cmd.ExecuteNonQuery();

Depending on the driver you use, the C# methods used may differ - I'm using System.Data.Sqlite here.

You may also want to taka look at how to do Upsert in SQLite.