locked
WFC RIA Service - Result from Stored Procedures... RRS feed

  • Question

  • Hello,

    I have the need to show the result of some stored procedures to LS with a RIA Service but this is no possible with Entity Framework because there I cannot use SELECT Stored Procedures because the result is nit mapped to an existing table.

    Is it possible to create a Domain Service class and use nativ ADO.NET to execute the stored procedure and return the result.

    robert

    Thursday, October 17, 2013 3:51 PM

Answers

  • Hi Mad,

    '********************* Explanation *********************
    '
    'Here is an example that I use in production to show
    'Results of a stored procedure
    'Create in separate files in your project WCF RIA Service
    'The classes below
    '
    
    
    
    
    '********************* Returns the connection from my config file *********************
    Imports System.Data.SqlClient
    
    Public Class ConectaBD
    
        Public Shared Function Conexao() As SqlConnection
            Return New SqlConnection(My.Settings.Snak_Empresarial_WCF_RIA_Service_ConnectionString)
        End Function
    
    End Class
    
    
    '********************* Class that will represent the result of my stored procedure *********************
    Imports System.ComponentModel.DataAnnotations
    
    Public Class HorarioLivre
    
        <Key>
        Public Property ID As Integer
        Public Property AgendadoPara As Nullable(Of Date)
        Public Property LocalAtendimentoID As Nullable(Of Short)
        Public Property UnidadeAtendimentoID As Nullable(Of Short)
    
        Public Shared Function CreateDataRecord(id As Integer, record As IDataRecord) As HorarioLivre
            Return New HorarioLivre With {
                .ID = id,
                .AgendadoPara = If(IsDBNull(record("AgendadoPara")), Nothing, CDate(record("AgendadoPara"))),
                .LocalAtendimentoID = If(IsDBNull(record("LocalAtendimentoID")), Nothing, CShort(record("LocalAtendimentoID"))),
                .UnidadeAtendimentoID = If(IsDBNull(record("UnidadeAtendimentoID")), Nothing, CShort(record("UnidadeAtendimentoID")))
            }
        End Function
    
    End Class
    
    
    '********************* Class to access the data and return the list of records *********************
    Imports System.Data.SqlClient
    
    Public Class HorarioLivreDAO
    
        Public Shared Function GetAllHorarioLivre() As IEnumerable(Of HorarioLivre)
            Return Nothing
        End Function
    
        Public Shared Function ConsultaHorarioLivre(dataAgenda As Nullable(Of Date),
                                                    localAtendimento As Nullable(Of Short),
                                                    unidadeAtendimento As Nullable(Of Short)) As IEnumerable(Of HorarioLivre)
            If dataAgenda.HasValue AndAlso localAtendimento.HasValue AndAlso unidadeAtendimento.HasValue Then
                Dim Lista As New List(Of HorarioLivre)
                Using Conn = ConectaBD.Conexao
                    Using Cmd = Conn.CreateCommand
                        With Cmd
                            .CommandType = CommandType.StoredProcedure
                            .CommandText = "dbo.LaudosClinicos_Consulta_horario_livre"
                            .Parameters.AddWithValue("@DataAgenda", dataAgenda)
                            .Parameters.AddWithValue("@LocalAtendimento", localAtendimento)
                            .Parameters.AddWithValue("@UnidadeAtendimento", unidadeAtendimento)
                        End With
                        Try
                            Conn.Open()
                            Using Dr = Cmd.ExecuteReader
                                If Dr.HasRows Then
                                    Dim c As Integer = 1
                                    While Dr.Read
                                        Lista.Add(HorarioLivre.CreateDataRecord(c, Dr))
                                        c += 1
                                    End While
                                End If
                                Return Lista
                            End Using
                        Catch ex As Exception
                            Throw New Exception(ex.Message)
                        End Try
                    End Using
                End Using
            Else
                Return Nothing
            End If
        End Function
    
    End Class
    
    
    '********************* Class DomainService to expose the data *********************
    Option Compare Binary
    Option Infer On
    Option Strict On
    Option Explicit On
    
    Imports System
    Imports System.Collections.Generic
    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.Linq
    Imports System.ServiceModel.DomainServices.Hosting
    Imports System.ServiceModel.DomainServices.Server
    
    
    'TODO: Create methods containing your application logic.
    'TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    Public Class SnakDomainService
        Inherits DomainService
    
        'sobrescrever o metodo Count para que a paginação funcione perfeitamente
        Protected Overrides Function Count(Of T)(query As IQueryable(Of T)) As Integer
            Return query.Count()
        End Function
    
    
    	#Region "Horario livre"
    
        <Query(IsDefault:=True)>
        Public Function GetAllHorarioLivre() As IEnumerable(Of HorarioLivre)
            Return HorarioLivreDAO.GetAllHorarioLivre
        End Function
    
        <Query()>
        Public Function ConsultaHorarioLivre(dataAgenda As Nullable(Of Date),
                                            localAtendimento As Nullable(Of Short),
                                            unidadeAtendimento As Nullable(Of Short)) As IEnumerable(Of HorarioLivre)
            Return HorarioLivreDAO.ConsultaHorarioLivre(dataAgenda, localAtendimento, unidadeAtendimento)
        End Function
    
        Public Sub InsertConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
        Public Sub UpdateConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
        Public Sub DeleteConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
    #End Region
    
    End Class

    Friday, October 18, 2013 2:05 AM

All replies

  • Hi Mad,

    '********************* Explanation *********************
    '
    'Here is an example that I use in production to show
    'Results of a stored procedure
    'Create in separate files in your project WCF RIA Service
    'The classes below
    '
    
    
    
    
    '********************* Returns the connection from my config file *********************
    Imports System.Data.SqlClient
    
    Public Class ConectaBD
    
        Public Shared Function Conexao() As SqlConnection
            Return New SqlConnection(My.Settings.Snak_Empresarial_WCF_RIA_Service_ConnectionString)
        End Function
    
    End Class
    
    
    '********************* Class that will represent the result of my stored procedure *********************
    Imports System.ComponentModel.DataAnnotations
    
    Public Class HorarioLivre
    
        <Key>
        Public Property ID As Integer
        Public Property AgendadoPara As Nullable(Of Date)
        Public Property LocalAtendimentoID As Nullable(Of Short)
        Public Property UnidadeAtendimentoID As Nullable(Of Short)
    
        Public Shared Function CreateDataRecord(id As Integer, record As IDataRecord) As HorarioLivre
            Return New HorarioLivre With {
                .ID = id,
                .AgendadoPara = If(IsDBNull(record("AgendadoPara")), Nothing, CDate(record("AgendadoPara"))),
                .LocalAtendimentoID = If(IsDBNull(record("LocalAtendimentoID")), Nothing, CShort(record("LocalAtendimentoID"))),
                .UnidadeAtendimentoID = If(IsDBNull(record("UnidadeAtendimentoID")), Nothing, CShort(record("UnidadeAtendimentoID")))
            }
        End Function
    
    End Class
    
    
    '********************* Class to access the data and return the list of records *********************
    Imports System.Data.SqlClient
    
    Public Class HorarioLivreDAO
    
        Public Shared Function GetAllHorarioLivre() As IEnumerable(Of HorarioLivre)
            Return Nothing
        End Function
    
        Public Shared Function ConsultaHorarioLivre(dataAgenda As Nullable(Of Date),
                                                    localAtendimento As Nullable(Of Short),
                                                    unidadeAtendimento As Nullable(Of Short)) As IEnumerable(Of HorarioLivre)
            If dataAgenda.HasValue AndAlso localAtendimento.HasValue AndAlso unidadeAtendimento.HasValue Then
                Dim Lista As New List(Of HorarioLivre)
                Using Conn = ConectaBD.Conexao
                    Using Cmd = Conn.CreateCommand
                        With Cmd
                            .CommandType = CommandType.StoredProcedure
                            .CommandText = "dbo.LaudosClinicos_Consulta_horario_livre"
                            .Parameters.AddWithValue("@DataAgenda", dataAgenda)
                            .Parameters.AddWithValue("@LocalAtendimento", localAtendimento)
                            .Parameters.AddWithValue("@UnidadeAtendimento", unidadeAtendimento)
                        End With
                        Try
                            Conn.Open()
                            Using Dr = Cmd.ExecuteReader
                                If Dr.HasRows Then
                                    Dim c As Integer = 1
                                    While Dr.Read
                                        Lista.Add(HorarioLivre.CreateDataRecord(c, Dr))
                                        c += 1
                                    End While
                                End If
                                Return Lista
                            End Using
                        Catch ex As Exception
                            Throw New Exception(ex.Message)
                        End Try
                    End Using
                End Using
            Else
                Return Nothing
            End If
        End Function
    
    End Class
    
    
    '********************* Class DomainService to expose the data *********************
    Option Compare Binary
    Option Infer On
    Option Strict On
    Option Explicit On
    
    Imports System
    Imports System.Collections.Generic
    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.Linq
    Imports System.ServiceModel.DomainServices.Hosting
    Imports System.ServiceModel.DomainServices.Server
    
    
    'TODO: Create methods containing your application logic.
    'TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    Public Class SnakDomainService
        Inherits DomainService
    
        'sobrescrever o metodo Count para que a paginação funcione perfeitamente
        Protected Overrides Function Count(Of T)(query As IQueryable(Of T)) As Integer
            Return query.Count()
        End Function
    
    
    	#Region "Horario livre"
    
        <Query(IsDefault:=True)>
        Public Function GetAllHorarioLivre() As IEnumerable(Of HorarioLivre)
            Return HorarioLivreDAO.GetAllHorarioLivre
        End Function
    
        <Query()>
        Public Function ConsultaHorarioLivre(dataAgenda As Nullable(Of Date),
                                            localAtendimento As Nullable(Of Short),
                                            unidadeAtendimento As Nullable(Of Short)) As IEnumerable(Of HorarioLivre)
            Return HorarioLivreDAO.ConsultaHorarioLivre(dataAgenda, localAtendimento, unidadeAtendimento)
        End Function
    
        Public Sub InsertConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
        Public Sub UpdateConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
        Public Sub DeleteConsultaAgendaExame(T As HorarioLivre)
        End Sub
    
    #End Region
    
    End Class

    Friday, October 18, 2013 2:05 AM
  • Thanks - it works

    it would be useful if we didn't must write this code but can use also SELECT Stored Procedures as Datasources native in LS

    Friday, October 18, 2013 8:51 AM
  • Hello

    I have a Problem with the Datetime fields - if they are NULL they Display 01.01.0001 in the HTML Client...

    Why?

    .OrderDate=

    If(IsDBNull(record("OrderDate")), Nothing, CDate(record("OrderDate")))

    robert

    Monday, October 21, 2013 2:04 PM