Explicit Queries inside Procedure

In This Topic

This topic covers procedures that include explicit select statements. For such stored routines, mapping is most straight-forward. For those common with LINQ to SQL we can say that this type of procedures should be mapped exactly as it is done in LINQ to SQL. The following procedure illustrates this case (the procedure is written for SQL Server; if you use MySQL, you can keep the wrapper code, but the stored procedure syntax must be modified):

  
CREATE PROCEDURE GetEmpsByDeptId
  @id int
AS
BEGIN
  SELECT * FROM emp WHERE emp.deptno = @id;
END

Here is the corresponding wrapper method of DataContext.

C#csharp	Copy Code
[Function(Name=@"GetEmpsByDeptId")] [ResultType(typeof(Emp), 0, ResultTypeOrigin.ExplicitSelect)] public ISingleResult<Emp> GetEmpsByDeptId( [Parameter(Name="id", DbType="int")] int? id) { IExecuteResult _GetEmpsByDeptIdResult = this.ExecuteMethodCall( this, (MethodInfo)MethodInfo.GetCurrentMethod(), id ); return (ISingleResult<Emp>)_GetEmpsByDeptIdResult.ReturnValue; }

C#csharp

Copy Code

[Function(Name=@"GetEmpsByDeptId")]
[ResultType(typeof(Emp), 0, ResultTypeOrigin.ExplicitSelect)]
public ISingleResult<Emp> GetEmpsByDeptId(
    [Parameter(Name="id", DbType="int")] int? id) {
 
    IExecuteResult _GetEmpsByDeptIdResult = this.ExecuteMethodCall(
        this,
        (MethodInfo)MethodInfo.GetCurrentMethod(),
        id
    );
    return (ISingleResult<Emp>)_GetEmpsByDeptIdResult.ReturnValue;
}

Visual Basic	Copy Code
<[Function](Name:="GetEmpsByDeptId")> _ <ResultType(GetType(Emp), 0, ResultTypeOrigin.ExplicitSelect)> _ Public Function GetEmpsByDeptId( _ <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer) ) 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

Visual Basic

Copy Code

<[Function](Name:="GetEmpsByDeptId")> _
<ResultType(GetType(Emp), 0, ResultTypeOrigin.ExplicitSelect)> _
Public Function GetEmpsByDeptId( _
    <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer)
) 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

We show the ResultType attribute here for greater clarity, but in fact it isn't necessary. Runtime understands that we are working with SQL Server (after reading the ProviderAttribute or the corresponding element of XML mapping), and for SQL Server methods returning ISingleResult are considered as stored procedures with a SELECT statement inside by default. And the type of the collection is specified by the generic argument of ISingleResult.

However, Entity Developer generates the ResultType attribute to make code more clear. This attribute has three parameters: type of the collection (Emp in our case), the number (in our case, the number of SELECT in the stored procedure), and the resultset source type (ResultTypeOrigin.ExplicitSelect).

You can simply iterate the result ISingleResult<Emp> (since it is inherited from IEnumerable<Emp>).

C#csharp	Copy Code
ScottDataContext context = new ScottDataContext() { Log = Console.Out }; var list = context.GetEmpsByDeptId(30); foreach (var item in list) Console.WriteLine(item.Ename);

Visual Basic	Copy Code
Dim context As New ScottDataContext() With { _ .Log = Console.Out _ } Dim list = context.GetEmpsByDeptId(30) For Each item In list Console.WriteLine(item.Ename) Next

And now let's look at a stored procedure, returning two resultsets.

CREATE PROCEDURE [dbo].[GetDeptAndEmpsById]
  @id int
AS
BEGIN
  SELECT * FROM dept WHERE deptno = @id;
  SELECT * FROM emp WHERE deptno = @id;
END

The corresponding wrapper method can be the following:

C#csharp	Copy Code
[Function(Name = @"GetDeptAndEmpsById")] [ResultType(typeof(Dept), 0, ResultTypeOrigin.ExplicitSelect)] [ResultType(typeof(Emp), 1, ResultTypeOrigin.ExplicitSelect)] public IMultipleResults GetDeptAndEmpsById( [Parameter(Name = "id", DbType = "int")] int? id ) { IExecuteResult _GetDeptAndEmpsByIdResult = this.ExecuteMethodCall( this, (MethodInfo)MethodInfo.GetCurrentMethod(), id ); return (IMultipleResults)_GetDeptAndEmpsByIdResult.ReturnValue; }

C#csharp

Copy Code

[Function(Name = @"GetDeptAndEmpsById")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.ExplicitSelect)]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.ExplicitSelect)]
public IMultipleResults GetDeptAndEmpsById(
  [Parameter(Name = "id", DbType = "int")] int? id
)
{
 
    IExecuteResult _GetDeptAndEmpsByIdResult = this.ExecuteMethodCall(
      this,
      (MethodInfo)MethodInfo.GetCurrentMethod(),
      id
    );
    return (IMultipleResults)_GetDeptAndEmpsByIdResult.ReturnValue;
}

Visual Basic	Copy Code
<[Function](Name:="GetDeptAndEmpsById")> _ <ResultType(GetType(Dept), 0, ResultTypeOrigin.ExplicitSelect)> _ <ResultType(GetType(Emp), 1, ResultTypeOrigin.ExplicitSelect)> _ Public Function GetDeptAndEmpsById( _ <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer) _ ) As IMultipleResults Dim _GetDeptAndEmpsByIdResult As IExecuteResult = _ Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id) Return DirectCast(_GetDeptAndEmpsByIdResult.ReturnValue, IMultipleResults) End Function

Visual Basic

Copy Code

<[Function](Name:="GetDeptAndEmpsById")> _
        <ResultType(GetType(Dept), 0, ResultTypeOrigin.ExplicitSelect)> _
        <ResultType(GetType(Emp), 1, ResultTypeOrigin.ExplicitSelect)> _
Public Function GetDeptAndEmpsById( _
    <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer) _
) 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 description of resultsets is required, because otherwise runtime cannot determine the types of the collections, returned by the procedure. ResultTypeOrigin still can be omitted, because runtime knows we are working with SQL Server and will expect SELECT statements inside the procedure.

Here is the example of how to use the returned IMultipleResults.

C#csharp	Copy Code
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);

C#csharp

Copy Code

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);

Visual Basic	Copy Code
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

Visual Basic

Copy Code

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

Here we call the GetResult<> method and pass the Dept and Emp types to it depending on which resultset we are reading. The resultsets are read in the order, specified by the Order parameter of the ResultType attributes, and this order in turn is determined by the order of the SELECT statements in the procedure. In fact, the generic argument is used only to know the type to materialize the resultset rows to. If we tried to read Emps first, and then Depts, we would get an invalid cast error.

See Also