As written in Non-Composable Functions, Returning Resultsets, cursors can be mapped to either out parameters or return values of the wrapper methods. Here are the examples for both mapping kinds.
The following example demonstrates the case when a stored routine returns a single resultset. The stored procedure is written for the Oracle database.
CREATE OR REPLACE PROCEDURE get_emps_by_dept_id (
id INTEGER,
cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN cur FOR
SELECT * FROM emp WHERE deptno = id;
END;
/
Here is the corresponding wrapper method, using output parameter to return the resultset.
[Function(Name = @"GET_EMPS_BY_DEPT_ID")]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.OutCursorParameter)]
public void GetEmpsByDeptId(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id,
[Parameter(Name = "CUR", DbType = "INT")] out IEnumerable<Emp> cur
)
{
cur = null;
IExecuteResult _GetEmpsByDeptIdResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
id,
cur
);
cur = (IEnumerable<Emp>)_GetEmpsByDeptIdResult.GetParameterValue(1);
}
<[Function](Name:="GET_EMPS_BY_DEPT_ID")> _
<ResultType(GetType(Emp), 1, ResultTypeOrigin.OutCursorParameter)> _
Public Sub GetEmpsByDeptId( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal), _
<Parameter(Name:="CUR", DbType:="INT")> ByRef cur As IEnumerable(Of Emp) _
)
cur = Nothing
Dim _GetEmpsByDeptIdResult As IExecuteResult = _
Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id, cur)
cur = DirectCast(_GetEmpsByDeptIdResult.GetParameterValue(1), IEnumerable(Of Emp))
End Sub
Notice that we have to initialize the output parameter (as we then pass it to ExecuteMethodCall); on the other hand, ExecuteMethodCall needs this parameter only to properly describe procedure call, so we can just assign null to 'cur'. This wrapper method returns void and has the output parameter of the IEnumerable<> type. The signatures of the method and the procedure are similar, however it is not very convenient to work with such method because you need to create a variable for loading resultset into:
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
IEnumerable<Emp> list;
context.GetEmpsByDeptId(30, out list);
foreach (var item in list)
Console.WriteLine(item.Ename);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim list As IEnumerable(Of Emp)
context.GetEmpsByDeptId(30, list)
For Each item In list
Console.WriteLine(item.Ename)
Next
The following wrapper method returns the resultset as ISingleResult<>:
[Function(Name = @"GET_EMPS_BY_DEPT_ID")]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.OutCursorParameter)]
public ISingleResult<Emp> GetEmpsByDeptId(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id
)
{
IExecuteResult _GetEmpsByDeptIdResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
id
);
return (ISingleResult<Emp>)_GetEmpsByDeptIdResult.ReturnValue;
}
<[Function](Name:="GET_EMPS_BY_DEPT_ID")> _
<ResultType(GetType(Emp), 1, ResultTypeOrigin.OutCursorParameter)> _
Public Function GetEmpsByDeptId( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal) _
) As ISingleResult(Of Emp)
Dim _GetEmpsByDeptIdResult As IExecuteResult = _
Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id)
Return DirectCast(_GetEmpsByDeptIdResult.ReturnValue, ISingleResult(Of Emp))
End Function
As ISingleResult<> is inherited from IEnumerable<>, we can simply iterate the result.
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var list = context.GetEmpsByDeptId(30);
foreach (var item in list)
Console.WriteLine(item.Ename);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim list = context.GetEmpsByDeptId(30)
For Each item In list
Console.WriteLine(item.Ename)
Next
We describe the resultset in both cases using the ResultType attribute that specifies the type of the collection elements (emp), the number of the resultset, and its source (the cursor parameter in our case). In this example it is not necessary because runtime understands that Oracle database is used after reading the ProviderAttribute or the corresponding element of XML mapping, and for Oracle it considers that resultsets are returned through output cursor parameters by default.
 Important Note: |
|
When working with output cursor parameters, the Order parameter of the ResultType attribute specifies the number of the corresponding parameter of the stored procedure (starting from 0). As of our stored procedure, the cursor parameter is the second one, so we pass 1 as the Order parameter. It allows you to map the stored procedures with scalar and cursor parameters mixed up. By default, LinqConnect runtime considers that the resultset cursor parameter is the first one after all the scalar ones.
|
And now let's demonstrate the case with several output cursor parameters. To show how the Order parameter of the ResultType attribute works, the input parameter will be placed between the two output cursor parameters.
CREATE OR REPLACE PROCEDURE get_dept_and_emps_by_id (
dept_cur OUT SYS_REFCURSOR,
id INTEGER,
emp_cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN dept_cur FOR
SELECT * FROM dept WHERE deptno = id;
OPEN emp_cur FOR
SELECT * FROM emp WHERE deptno = id;
END;
/
Here is the corresponding wrapper method, using IMultipleResults to return resultsets.
[Function(Name = @"GET_DEPT_AND_EMPS_BY_ID")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.OutCursorParameter)]
[ResultType(typeof(Emp), 2, ResultTypeOrigin.OutCursorParameter)]
public IMultipleResults GetDeptAndEmpsById(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id
)
{
IExecuteResult _GetDeptAndEmpsByIdResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
id
);
return (IMultipleResults)_GetDeptAndEmpsByIdResult.ReturnValue;
}
<[Function](Name:="GET_DEPT_AND_EMPS_BY_ID")> _
<ResultType(GetType(Dept), 0, ResultTypeOrigin.OutCursorParameter)> _
<ResultType(GetType(Emp), 2, ResultTypeOrigin.OutCursorParameter)> _
Public Function GetDeptAndEmpsById( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal) _
) As IMultipleResults
Dim _GetDeptAndEmpsByIdResult As IExecuteResult = _
Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id)
Return DirectCast(_GetDeptAndEmpsByIdResult.ReturnValue, IMultipleResults)
End Function
In this case the ResultType attributes must be used because otherwise runtime has no information on types, returned by the method. The Order parameter also must be specified. By default, runtime assumes that the input parameter is the first one, and the latter two are the output cursor parameters. In our case the first and the third parameters are output cursor ones. The Origin parameter can be omitted as before, because LinqConnect runtime assumes that the stored procedure returns resultsets through output cursor parameters by default for Oracle.
The resultsets are retrieved from IMultipleResults with the GetResult<> method:
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var res = context.GetDeptAndEmpsById(30);
Console.WriteLine(
res.GetResult<Dept>().Single().Dname
);
foreach (var emp in res.GetResult<Emp>())
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim res = context.GetDeptAndEmpsById(30)
Console.WriteLine(res.GetResult(Of Dept)().[Single]().Dname)
For Each emp In res.GetResult(Of Emp)()
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job)
Next
The following wrapper method uses output parameters to return the resultset:
[Function(Name = @"GET_DEPT_AND_EMPS_BY_ID")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.OutCursorParameter)]
[ResultType(typeof(Emp), 2, ResultTypeOrigin.OutCursorParameter)]
public void GetDeptAndEmpsById(
[Parameter(Name = "DEPT_CUR", DbType = "INT")] out IEnumerable<Dept> deptCursor,
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id,
[Parameter(Name = "EMP_CUR", DbType = "INT")] out IEnumerable<Emp> empCursor
)
{
deptCursor = null;
empCursor = null;
IExecuteResult _GetDeptAndEmpsByIdResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
deptCursor,
id,
empCursor
);
deptCursor = (IEnumerable<Dept>)_GetDeptAndEmpsByIdResult.GetParameterValue(0);
empCursor = (IEnumerable<Emp>)_GetDeptAndEmpsByIdResult.GetParameterValue(2);
}
<[Function](Name:="GET_DEPT_AND_EMPS_BY_ID")> _
<ResultType(GetType(Dept), 0, ResultTypeOrigin.OutCursorParameter)> _
<ResultType(GetType(Emp), 2, ResultTypeOrigin.OutCursorParameter)> _
Public Sub GetDeptAndEmpsById( _
<Parameter(Name:="DEPT_CUR", DbType:="INT")> _
ByRef deptCursor As IEnumerable(Of Dept),
<Parameter(Name:="ID", DbType:="NUMBER")> _
id As System.Nullable(Of Decimal),
<Parameter(Name:="EMP_CUR", DbType:="INT")> _
ByRef empCursor As IEnumerable(Of Emp)
)
deptCursor = Nothing
empCursor = Nothing
Dim _GetDeptAndEmpsByIdResult As IExecuteResult = _
Me.ExecuteMethodCall( _
Me, _
DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
deptCursor, _
id, _
empCursor _
)
deptCursor = DirectCast( _
_GetDeptAndEmpsByIdResult.GetParameterValue(0), _
IEnumerable(Of Dept) _
)
empCursor = DirectCast( _
_GetDeptAndEmpsByIdResult.GetParameterValue(2), _
IEnumerable(Of Emp) _
)
End Sub
Unlike the wrapper, returning IMultipleResults, this wrapper does not require using ResultType attributes because the order and type of the collections can be determined from the method signature. However the method itself and the code using this method become cumbersome and inconvenient because it is necessary to define and initialize variables. See the example of using this method:
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
IEnumerable<Dept> depts;
IEnumerable<Emp> emps;
context.GetDeptAndEmpsById(out depts, 30, out emps);
Console.WriteLine(depts.Single().Dname);
foreach (Emp emp in emps)
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim depts As IEnumerable(Of Dept)
Dim emps As IEnumerable(Of Emp)
context.GetDeptAndEmpsById(depts, 30, emps)
Console.WriteLine(depts.[Single]().Dname)
For Each emp As Emp In emps
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job)
Next
| Note: |
|
You must not mix both returning values through output parameters and through IMultipleResults. In such case runtime does not always know where to map a resultset. For example, a stored procedure has two output cursor parameters, the first is returned through IMultipleResults, and the second - through the method output parameter; the ResultType attributes cannot specify which of these cursors is returned first.
|
| Note: |
|
There is one more scenario when stored routine returns several cursors. It is a cursor stored function that has one or more cursor output parameters in addition to the cursor return value. This scenario is demonstrated in the Return Value of Cursor Type topic.
|
See Also
Non-Composable Functions, Returning Resultsets
| Explicit Queries inside Procedure
| Return Value of Cursor Type
