Insert Dapper, check for record

Question

So, I used this method to insert records into my database:

TransactionBlock.Connection.Execute(
                            "INSERT Table(Item,Id)VALUES(@Item, @Id);
                            new {Item,Id = id }, TransactionBlock.Transaction);

Now I need to change this to first check if Item / id is in the database using the following:

const sql = "IF EXISTS (SELECT * FROM Table, where Item = @Item ... etc. etc.

but I did not find examples of how to achieve this. I can achieve this by creating a stored procedure, but I would like to try to implement this approach.

Answer 1

Assuming you are using SQL Server and want to insert a record only if it does not already exist, the SQL you are looking for is

IF NOT EXISTS (SELECT * FROM Table WHERE Id = @Id) 
    INSERT INTO Table(Item, Id) VALUES(@Item, @Id)

Answer 2

INSERT INTO TableName (Item, ID)
SELECT @Item, @Id WHERE NOT EXISTS ( SELECT 1 FROM TableName WHERE Id=@Id )

This will work with one ad.

, , , - , , , , , .

, , :

http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

!

Answer 3

, , , try/catch DuplicateKeyException ( , ) .