insert conditionally get row id?

Question

insert conditionally get row id?

.net dapper sql sqlite

Question

I want to create a thread but only if the subject is unique in that section. Then I want to get the row id of the thread. How do I write it safely? My thoughts were something like

connection.Query<long>(@"insert into thread(section, subject, body) 
    select @section,@subject,@body
    where not exists in (select 1 from thread where section=@section and subject=@subject;
    select last_insert_rowid()", new {...}).First();

The problem is I have no idea if last_insert_rowid something from the past or from my insert statement. How do I write this query safely

user34537

View more on Stack Overflow

peterm · Accepted Answer · 8/20/2013 10:01:58 PM

If understand correctly you can use OR IGNORE syntax. In order for it to work you have to have a UNIQUE constraint on section and subject

CREATE TABLE thread
(id INTEGER PRIMARY KEY, 
 section INT, 
 subject TEXT(128), 
 body TEXT(256),
 UNIQUE (section, subject));

Now to ignore a row if it violates one of constraints

INSERT OR IGNORE INTO thread (section, subject, body)
VALUES (1, 'Subject1', 'Body1');

Now if insert succeeded then LAST_INSERT_ROWID() will return an id for the inserted row, but if it was a duplicate for section and subject insert failed it will return 0.

insert conditionally get row id?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator