Calling a Stored Procedure from NHibernate and retrieving @@ROWCOUNT

Question

After trying to work this out for a few hours, I'm beginning to think I can't solve this without your help. Situation:

My stored procudure in SQL Server Management Studio (basically):

SET NOCOUNT ON;
DELETE myTable
...                    -- Complex join query that deletes duplicates
RETURN @@ROWCOUNT      -- The number of rows affected

This procedure works well, if I run it in SSMS the following query is run:

USE myDb
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[StoredProcedure]
        @var1 = N'asd',
        @var2 = N'fgh',
        @var3 = N'jkl'
SELECT  'Return Value' = @return_value
GO

The result is shown as 1 row with name Return Value and value 8700 (this is correct, it's the number of rows that was deleted by the query).

My problems begin in my C#/NHibernate code. In my NHibernate mapping:

<sql-query name="MyStoredProcedureName">
  exec dbo.StoredProcedure @var1=:var1, @var2=:var2, @var3=:var3
</sql-query>

In my method where I call the stored procedure:

var deletedEntities = session.GetNamedQuery("MyStoredProcedureName")
                             .SetString("var1", "asd")
                             .SetString("var2", "fgh")
                             .SetString("var3", "jkl")
                             .ExecuteUpdate();

This results in deletedEntities = -1. I would like it to be deletedEntities = 8700.

I'd like to point out that the following did return the number of affected rows:

var deletedEntities = session.CreateQuery("DELETE Class c WHERE c.var1 = :var1 AND c.var2 = :var2 AND c.var3 = :var3")
                             .SetString("var1", var1)
                             .SetString("var2", var2)
                             .SetString("var3", var3)
                             .ExecuteUpdate();

Where Class is the class that belongs to the NHibernate mapping.

So, to summarize:

1. ExecuteUpdate on a SIMPLE session.CreateQuery gives me back the number of affected rows (GOOD)
2. ExecuteUpdate on a COMPLEX session.GetNamedQuery gives me back -1 (BAD)
3. This same complex stored procedure gives me back the desired 8700 when I execute it in SSMS (GOOD)

Anyone has an idea of how to fix 2?

Answer 1

Using http://csharptechies.blogspot.co.uk/2013/04/how-to-get-return-value-from-stored.html I got my code working. In the procedure, replaced RETURN @@ROWCOUNT with SELECT @@ROWCOUNT

In the C# code, I replaced .ExecuteUpdate() with .UniqueResult(). UniqueResult returns an object, which will contain the @@ROWCOUNT value (need to convert to int as stated in the link above).

So the solution is a combination (I'd like to use the mapping):

1. Use SELECT instead of RETURN
2. Use .UniqueResult() instead of .ExecuteUpdate()

Edit: I got another problem, the query had a timeout whenever there was a large number of records to be removed. I fixed this with the help of other questions, such as SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?.

3. Use WITH RECOMPILE in a complex query on large tables (my query involved a left outer join, min(id) and group by's, the number of records to be affected are often around 20.000 but can run up to 100.000+ and my table has over 350.000.000 records in it) to avoid timeouts.