Get inserted/updated/deleted rows count from stored procedure - Linq to SQL

Question

I'm calling a stored procedure which does some updates/inserts/deletes (any one of these at a time) from Linq. This stored procedure is added to datacontext I'm using. After calling this stored procedure I want to get the number of rows affected by this stored procedure. This stored procedure may affect more than one table also.

I tried using GetChangeSet method of datacontext but it doesn't return affected rows count for inserts/updates/deletes performed in this stored procedure.

I dont want to use @@rowcount and return that rowcount as return value.

Is there any way I can find this affected rows count?

Answer 1

In your stored procedure you could create a table variable (or temp table if tables variables are not available to you) and insert @@rowcount into it after every part of you stored procedure that affects the row count of a table, then do a select from the table variable as the last operation in your store procedure.

For example

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 to SQL queries.