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

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

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

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
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.

Setting Execution Order of Triggers in Sql Server

I had multiple triggers defined for update on a table. I got a requirement to force the execution for the triggers to be done in a defined order. I had searched some websites and found that it is possible using the built in sproc sp_settriggerorder

sp_settriggerorder [@triggername = ] ‘triggername‘
, [@order = ] ‘value‘
, [@stmttype = ] ‘statement_type‘

Argument

[@triggername = ] ‘triggername‘

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] ‘value‘

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.

Important The First and Last triggers must be two different triggers.

Value	Description
First	Trigger will be fired first.
Last	Trigger will be fired last.
None	Trigger will be fired in undefined order.

[@stmttype = ] ‘statement_type‘

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.