IExecuteResult to return data from stored procedures

When a call is made to a stored procedure or a function that returns a scalar value,  linq to SQL designer generated code calls ExecuteMethodCall which returns an object that implements of  IExecuteResult. IExecuteResult interface has one method and one property. Below is the definition for IExecuteResult interface.

If the stored procedure has output parameters, than you can access those parameters using GetParametervalue passing in the index 0 based index of the parameter you want to access.

The above stored procedure returns orders for a customer passed in as a parameter. The stored procedure also has an output parameter that returns totalorders a customer has placed. Screen shot below shows the code generated when I drag the stored procedure on linq to SQL designer.

In the above code, when the method GetOrdsForCust calls ExecuteMethodCall, we get IExecuteResult object back. To access the output parameter for the stored procedure, I am calling GetParameterValue on IEecuteResult object returned, passing in the index of the parameter as it appears in the stored procedure. GetParameterValue returns an object so I need to cast the object to the correct type. To access the table returned from the stored procedure, I am accessing the ReturnValue property on IExecuteResult interface. If the stored procedure returns a single table, than I need to cast my return value to ISingleResult.  ISingleResult implements IEnumerable of T which allows you to enumerate the results. ISingleResult also implements IFunctionalResult interface which has one readonly property called ReturnValue. ReturnValue property on IFunctionalResult simply returns the readonly value given by IExecuteResult.ReturnValue.

If the stored procedure returns multiple results, than ReturnValue property on IExecuteResult needs to be cast to IMultipleResult. To access a specific result in IMultipleResult, GetResult method can be used passing in type of entity you want returned. Below is an example where the stored procedure returns Categories and Products at the same time.

The above stored procedure returns to two tables. The first table returns rows of Categories. The second table returns rows of products. Since the stored procedure returns multiple results, I have not figure out a way to apply that in the designer when I drag the stored procedure. I had to go into the generated code apply the following attributes to make it work.

In the above code, to indicate that my stored procedure returns multiple result, I am using an attribute called ResultType. ResultType takes in a type object that defines what is the type being returned. Since my stored procedure returns two entities, I am using ResultType twice first with Category and than with Product. The order in which you put ResultType at the attribute level is irrelevant to the order in which stored procedure returns data. Secondly my method return type is also IMultipleResult because we do not know which object to return. IMultipleResult has a method called GetResult that allows you to return a specific entity from the collection of tables returned by the stored procedure. Below code shows an example of how to call the method that returns multiple results.

In the above code, I am getting a reference to IMultipleResults by calling GetcatandProd method. Than, in order to retrieve my category, I simply call GetResult passing in the type of entity I want returned. I do the same logic for getting my products back as well. Notice the order in which I am returning my entities. I first ask for categories and than for products because this is how my results are returned by the  stored procedure. If you do not call GetResult in this order you may get an exception because the result returned would not be of the type that you are asking.

IMultipleResult interface can also be used in scenarios where you are only returning one result from your stored procedure but you do not know what would be the result. For instance a stored procedure may taken in a parameter and based on that parameter it will either return Categories or Products.  To handle this scenario, you have to resort to IMulitplResult. To make sure that you do not get runtime exception, you need to call the GetResult  with correct type you want returned based on the parameter passed to the stored procedure.