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()?

The first statement will update all rows in the table. The second one is not valid SQL. — CL., Oct 11 '17 at 14:33
I have updated the second one and will refine and check more. What I was showing was just an example. What I need to do is to insert a row if it's not already there and update if it is. I researched this and the most easy way would appear to be an update followed by an insert if the update didn't work — Alan2, Oct 11 '17 at 14:37
You could do `case count(select ...) when 0 then insert else update`, although this is plus additional query. Is that what you are looking for? — Andrei, Oct 11 '17 at 15:12
In fact I guess this is what you are looking for: https://stackoverflow.com/q/108403/728795 — Andrei, Oct 11 '17 at 15:55
Every SQL driver should have some method to return the number of updated rows (but I don't know which one you're using). — CL., Oct 11 '17 at 16:40
Hey Alan! Can you post the code of your model? I.e. what does `CLICKHISTORY` look like, either as a POCO or a SQL Table. — Brandon Minnick, Oct 16 '17 at 17:33
How are you connecting to the DB? Can you post your connection code and which library you used? `Mono.Data.SQLite`? — Tarun Lalwani, Oct 16 '17 at 19:34

Steve Chambers · Accepted Answer · 2017-10-18T20:20:48.527

8

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...");
}

edited Oct 18 '17 at 20:20

answered Oct 18 '17 at 15:30

Steve Chambers

37,270
24
156
208

score 5 · Answer 2 · answered Oct 17 '17 at 04:49

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.

Hello, Thanks for your answer. Can you explain the usage of the $" and also the paren around name? — Alan2, Oct 17 '17 at 07:23
@Alan2 The $"" and parentheses are the usage of the [C# string interpolation](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/interpolated-strings). It's giving the same results as [string.Format()](https://msdn.microsoft.com/en-us/library/system.string.format(v=vs.110).aspx) but in a (IMO) more human-readable way. — Filnor, Oct 17 '17 at 12:55

How to combine two SQLite statements running in C#?

2 Answers2

Example