Is it possible to return a return value and a result set from a stored procedure using LINQ

Question

Looking for suggestions on cleanest way to get the return value and the result set (without passing a referenced parameter to the stored proc).

MY stored procs have return values to show errors, etc and they end with a select statement to get the information. With regular commands I would create an output parameter which would hold the return value. I can't change the stored procs in the db so I can't pass a different output parameter other then the return value.

thoughts??

Answer 1

When you create a stored procedure in the LINQ to SQL Data Context designer by dragging it from the Server Explorer, the designer will analyze the sproc to determine if it returns a result set. Unless it features heavy dynamic SQL, it's usually spot on.

If it does, the method generated returns ISingleResult<T> where T is either a class generated from an analysis of your sproc to fit the shape of the result set, or one you specify in the sproc properties page. Because ISingleResult derives from IEnumerable, most people do a simple foreach on the result to get their result set. But what most people forget is that ISingleResult also derives from IFunctionResult, which has a property called ReturnValue. This is the return value from the sproc.

So all you have to do is something along the lines of:

using(var dc = new MyDataContext())
{
    var results = dc.MyStoredProcedure();
    var returnValue = (int)results.ReturnValue; // note that ReturnValue is of type object and must be cast.
    foreach(var row in results)
    {
        // process row
    }
}