How can I call a stored procedure with INSERT using LINQ?

Question

Is there any possible way to execute a non query without having to assign it to a LINQ to SQL class?

Let's say I have this procedure and I want to INSERT only if the Town_Name (UNIQUE CONSTRAINT) is non existent. Otherwise, the UPDATE is executed. This procedure works well and does the job when executed through SQL Server. However, I can't manage to execute it through C# code.

I am trying to avoid using SQL client, as part of my coursework, my little application has to be capable of selecting, inserting / updating and deleting data using LINQ.

The approach I'm taking always results in 0 rows affected.

// Call Procedure.
db.P_SaveClient("000000001M", "Test", "Dummy", null, "+35699999999");

-- Procedure to be executed.
CREATE PROCEDURE        Appointment.SaveClient
                        (
                            @id NVARCHAR(10), 
                            @firstName NVARCHAR(35), 
                            @lastName NVARCHAR(35), 
                            @tel NVARCHAR(12), 
                            @mob NVARCHAR(12)
                        )
AS
BEGIN

    DECLARE @clientCount TINYINT

    SELECT      @clientCount = COUNT(Client_ID)
    FROM        Appointment.Client
    WHERE       Client_ID = @id

    IF @clientCount = 0
        BEGIN
            INSERT INTO     Appointment.Client
            VALUES          (
                                @id
                                , @firstName
                                , @lastName
                                , @tel
                                , @mob
                            )
        END
    ELSE
        BEGIN
            UPDATE          Appointment.Client
            SET             Client_ID = @id
                            , First_Name = @firstName
                            , Last_Name = @lastName
                            , Telephone = @tel
                            , Mobile = @mob
        END

END

Some tutorials I found:

• https://www.youtube.com/watch?v=dlXT-vE46sc
• https://www.youtube.com/watch?v=-PAMtSwplu8

Answer 1

You're looking for a Merge statement to execute in SQL, which you could call via the stored proc. This will allow you to insert or update depending on whether it was found. It can even return the ID of the record inserted which can save you another query.

Merge Town t
using ( select @Town_Name as 'Town_Name') 
src on (src.Town_Name = t.Town_Name )
when NOT MATCHED then
insert (Town_Name) values (src.Town_Name)
output INSERTED.Town_ID

See here for syntax and more examples: https://msdn.microsoft.com/en-us/library/bb510625.aspx