Thursday, April 30, 2009

Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

@SomeID int
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));

The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.

kick it on


Kiran said...

u r telling for select statements which are retrieving result from one table..
i want for join condition..
please help me..

Clarence Tunstall said...

The problem with this elementary oft repeated explanation is you're creating a new connection to the database with the DataContext object... what good then is linq or entity? I may as well use ADO.Net and skip the middle man... this as with all of the other SAME explanation is such a waste of my time, I may has well just using ADO.NET