I've got a stored procedure that returns two possible result sets. If successful, it returns a 0 (int). If it fails it returns -1 and a table containing a list of errors. I'm trying to figure out how to deal with this using LINQ.
I found Scott Guthrie's post about this, but he was using results that were mapped to types/tables. I had to go to the length of defining my own type for the errors table:
public partial class myStoredProcResult
{
...
[Column(Storage = "_error", DbType = "varchar(max) NULL")]
public string error
{
...
}
[Column(Storage = "_msg", DbType = "varchar(max) NULL")]
public string msg
{
...
}
}
I've logged the EXECUTE that's being generated and it's correct. If I copy that generated sql over to a query window it returns a table containing errors then an int with the error code.
Refactoring the output signature of the proc is not an option.
[Function(Name="dbo.myStoredProc")]
[ResultType(typeof(myStoredProcResult))]
public IMultipleResults myStoredProc (...)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)
(MethodInfo.GetCurrentMethod())), ...);
return ((IMultipleResults)(result.ReturnValue));
}
But whenever I run this proc I get a Return value (-1, correct) but no myStoredProcResult object. Calling result.GetResult() on the IMultipleResults that's returned only gives me a -1, which is the same as I had before I added the multi results set. I wish I could just dump this thing to a dataset :(