Insert / update / delete row count from stored procedure - Linq to SQL

Question

I call a stored procedure that does some updates / inserts / deletes (any of them at a time) from Linq. This stored procedure is added to the used datacontext. After calling this stored procedure, I want to get the number of rows affected by this stored procedure. This stored procedure can affect more than one table.

I tried using the GetChangeSet method for the datacontext, but it does not return the number of corrupted rows for insertions / updates / deletes performed in this stored procedure.

I do not want to use @@ rowcount and return this row number as the return value.

Is there any way to find this row counter?

Answer 1

In your stored procedure, you can create a table variable (or a temporary table if table variables are not available to you) and insert @@ rowcount after each part of the stored procedure that affects the number of rows in the table, then make a selection from the table variable as the last operation in your storage procedure.

for instance

CREATE PROCEDURE myProc
AS
BEGIN
    DECLARE @ra TABLE
    (
        rowsAffected INT,
        queryName VARCHAR(50)
    )

    INSERT INTO sometable
    SELECT col1, col2
    FROM someothertable

    INSERT INTO @ra (rowsAffected, queryName) VALUES (@@ROWCOUNT, 'insert into sometable')

    DELETE FROM anothertable
    WHERE thingID = something

    INSERT INTO @ra (rowsAffected, queryName) VALUES (@@ROWCOUNT, 'delete from anothertable')

    SELECT rowsAffected, queryName
    FROM @ra
END

Then update your DBML so that the rows are available in Linq queries for SQL.