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:

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jan 18, 2016 at 19:51
Note that your UPDATE does nothing since it looks for the town with that town name, then "updates" it to the same name. If you want to "change the town name you need to pass in the original name or ID and the new name as separate parameters. — D Stanley
– D Stanley, Commented Jan 18, 2016 at 20:09
You're right, I didn't test this script. I made this in a few seconds to compliment my question. Regarding the SP noted. I forgot that T-SQL makes use of sp_execute, sp_rename, etc. — Isaac Hili
– Isaac Hili, Commented Jan 18, 2016 at 20:12
To answer your question, though, you can execute non-entity queries by using traditional data access methods (SqlCommand, etc.) or by using the ExecuteCommand method on the data context. — D Stanley
– D Stanley, Commented Jan 18, 2016 at 20:13

dwbartz · Accepted Answer · 2016-01-18 20:17:11Z

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

answered Jan 18, 2016 at 20:17

dwbartz

88610 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Isaac Hili Over a year ago

I have already tried a merge statement and it didn't work. I added the output and I am getting the outputted result but whenever I right click on the table and select Show Data my inserted data is not there, even after Refresh.

dwbartz Over a year ago

@IsaacHili Does it run correctly inside SQL Server?

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related