How to use DataContext.ExecuteCommand and get the executed stored proc return value?

Question

In a c# project, I call a stored procedure as follows:

System.Data.Linq.DataContext dataContext = MembershipContext.GetContext(connectionString);
int returnValue = dataContext.ExecuteCommand("EXEC usp_SomeProcedure {0}, {1}, {2}", param1, param2, param3);

However, ExecuteCommand returns the number of rows affected, and not my stored procedure return value. What would be the easiest way to get this value. I need this because the SP returns 0 on success and a positive int value if an error occurred.

For now, the stored proc uses RETURN to output its return value. However, I could change this for a SELECT or I could also use an output parameter if required.

score 4 · Accepted Answer · edited May 23 '17 at 10:29

4

I believe you need to set the CommandType to CommandType.StoredProcedure in order to get the return value from your stored proc. See the accepted answer here: Getting return value from stored procedure in ADO.NET

edited May 23 '17 at 10:29

Community

1
1

answered Jul 25 '12 at 16:56

proggrock

3,239
6
36
51

I used the example given on the page you referred too, and it is working. I am not using the DataContext anymore as Serg Rogovtsev suggested. +1 to both of you! – Jean-François Beauchamp Jul 26 '12 at 23:22

score 2 · Answer 2 · edited May 23 '17 at 12:02

2

Don't use DataContext for calling SPs if you don't need Linq2Sql mapping functionality. Just use SqlCommand as down.with.the.bass shows.

edited May 23 '17 at 12:02

Community

1
1

answered Jul 25 '12 at 20:03

Sergei Rogovtcev

5,804
2
22
35

How to use DataContext.ExecuteCommand and get the executed stored proc return value?

2 Answers2

Linked