locked
How to create a WCF service using POCO in Lightswitch in VB .NET? RRS feed

  • Question

  • Need a sample that contains at least two related tables, eg

    Category and Product representation in sql server

    category 

     - ID 

     - Name

    product 

     - ID 

     - Name 

     - CategoryID

    how can I do the same thing using WCF RiaServices and POCO to use in LightSwitch

    if you do not like being done in VB to be in C # that after I convert the code. Thank you all.

    Thursday, July 19, 2012 11:18 AM

Answers

  • I did it!
    I'm doing an integration between systems that use different databases. 
     - Sql Server 
     - Oracle
    The problem is that the oracle have tables with more than one hundred fields.
    could not use the entity framework for oracle because it always returns me
    a hundred fields of the table. the only option I have left is to implement a
    wcf service with ADO using OracleDataAccess.dll and write the query according to my need.
    you guys can see the result below.
    ps: the performance is amazing!

    Imports System.ComponentModel.DataAnnotations
    Imports System.Text
    Imports Oracle.DataAccess.Client
    
    Public Class DbamvSetor
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
    
        <Association("Localidade_Setor_FK", "ID", "SetorID", IsForeignKey:=False)>
        Public Property DbamvLocalidades As IQueryable(Of DbamvLocalidade)
    
        Public Shared Function CreateDataRecord(Record As IDataRecord) As DbamvSetor
            Return New DbamvSetor With {
                .ID = Record("CD_SETOR"),
                .Nome = Record("NM_SETOR")
            }
        End Function
    
        Public Shared Function GetAllDbamvSetor() As IQueryable(Of DbamvSetor)
            Dim Lista As New List(Of DbamvSetor)
            Dim StrConn As String = My.Settings.MvProducao
            'criar o comando
            Dim Sql As New StringBuilder
            Sql.Append("SELECT CD_SETOR, NM_SETOR FROM DBAMV.SETOR ORDER BY NM_SETOR")
            'atribuir os parâmetros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Using Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                While Dr.Read
                                    Lista.Add(CreateDataRecord(Dr))
                                End While
                            End If
                            Return Lista.AsQueryable
                        End Using
                    Catch ex As Exception
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Function
    
    End Class
    
    
    Imports System.ComponentModel.DataAnnotations
    Imports System.Text
    Imports Oracle.DataAccess.Client
    
    Public Class DbamvLocalidade
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
        <Required()>
        Public Property SetorID As Integer
    
        <Association("Localidade_Setor_FK", "SetorID", "ID", IsForeignKey:=True)>
        Public Property DbamvSetor As DbamvSetor
    
        Public Shared Function CreateDataRecord(Record As IDataRecord) As DbamvLocalidade
            Return New DbamvLocalidade With {
                .ID = Record("CD_LOCALIDADE"),
                .Nome = Record("DS_LOCALIDADE"),
                .SetorID = Record("CD_SETOR")
            }
        End Function
    
        Public Shared Function GetAllDbamvLocalidade() As IQueryable(Of DbamvLocalidade)
            Dim Lista As New List(Of DbamvLocalidade)
            Dim StrConn As String = My.Settings.MvProducao
            'criar o comando
            Dim Sql As New StringBuilder
            Sql.Append("SELECT CD_LOCALIDADE, DS_LOCALIDADE, CD_SETOR FROM DBAMV.LOCALIDADE ORDER BY DS_LOCALIDADE")
            'atribuir os parâmetros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Using Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                While Dr.Read
                                    Lista.Add(CreateDataRecord(Dr))
                                End While
                            End If
                            Return Lista.AsQueryable
                        End Using
                    Catch ex As Exception
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Function
    
    End Class
    
    
    
    Imports System.ServiceModel.DomainServices.Server
    
    Public Class DbamvDomainService
        Inherits DomainService
    
        <Query(IsDefault:=True)>
        Public Function GetDbamvSetor() As IQueryable(Of DbamvSetor)
            Return DbamvSetor.GetAllDbamvSetor
        End Function
    
        <Query(IsDefault:=True)>
        Public Function GetDbamvLocalidade() As IQueryable(Of DbamvLocalidade)
            Return DbamvLocalidade.GetAllDbamvLocalidade
        End Function
    
        Protected Overrides Function Count(Of T)(query As System.Linq.IQueryable(Of T)) As Integer
            Return query.Count
        End Function
    
    End Class
    this is just a small part of the code to be an example for those who are in need.

    • Edited by Marden LR Thursday, July 19, 2012 4:14 PM
    • Marked as answer by Marden LR Thursday, July 19, 2012 4:14 PM
    Thursday, July 19, 2012 4:12 PM

All replies

  • This technique might help: WCF RIA Service: Combining Two Tables. Even though it was for V1, the idea should be the same.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Thursday, July 19, 2012 1:07 PM
  • Hi Yann thanks for the help,

    I had already seen this article but not help me
    I've managed to do half the job.  
     - I can create the wcf service 
     - Care for the lightswitch 
     - Create screens with tables
     
    The only thing I'm not able to do is relate the tables in the wcf service
    I need that the relationship is one to many

    see what I've done in wcf service:

    Public Class DbamvSetor
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
    
    End Class
    
    Public Class DbamvLocalidade
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
        <Required()>
        Public Property DbamvSetorID As Integer
    
    End Class

    how can I relate these two classes in one to many relationship?
    Thursday, July 19, 2012 3:22 PM
  • I did it!
    I'm doing an integration between systems that use different databases. 
     - Sql Server 
     - Oracle
    The problem is that the oracle have tables with more than one hundred fields.
    could not use the entity framework for oracle because it always returns me
    a hundred fields of the table. the only option I have left is to implement a
    wcf service with ADO using OracleDataAccess.dll and write the query according to my need.
    you guys can see the result below.
    ps: the performance is amazing!

    Imports System.ComponentModel.DataAnnotations
    Imports System.Text
    Imports Oracle.DataAccess.Client
    
    Public Class DbamvSetor
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
    
        <Association("Localidade_Setor_FK", "ID", "SetorID", IsForeignKey:=False)>
        Public Property DbamvLocalidades As IQueryable(Of DbamvLocalidade)
    
        Public Shared Function CreateDataRecord(Record As IDataRecord) As DbamvSetor
            Return New DbamvSetor With {
                .ID = Record("CD_SETOR"),
                .Nome = Record("NM_SETOR")
            }
        End Function
    
        Public Shared Function GetAllDbamvSetor() As IQueryable(Of DbamvSetor)
            Dim Lista As New List(Of DbamvSetor)
            Dim StrConn As String = My.Settings.MvProducao
            'criar o comando
            Dim Sql As New StringBuilder
            Sql.Append("SELECT CD_SETOR, NM_SETOR FROM DBAMV.SETOR ORDER BY NM_SETOR")
            'atribuir os parâmetros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Using Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                While Dr.Read
                                    Lista.Add(CreateDataRecord(Dr))
                                End While
                            End If
                            Return Lista.AsQueryable
                        End Using
                    Catch ex As Exception
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Function
    
    End Class
    
    
    Imports System.ComponentModel.DataAnnotations
    Imports System.Text
    Imports Oracle.DataAccess.Client
    
    Public Class DbamvLocalidade
    
        <Key()>
        Public Property ID As Integer
        <Required()>
        Public Property Nome As String
        <Required()>
        Public Property SetorID As Integer
    
        <Association("Localidade_Setor_FK", "SetorID", "ID", IsForeignKey:=True)>
        Public Property DbamvSetor As DbamvSetor
    
        Public Shared Function CreateDataRecord(Record As IDataRecord) As DbamvLocalidade
            Return New DbamvLocalidade With {
                .ID = Record("CD_LOCALIDADE"),
                .Nome = Record("DS_LOCALIDADE"),
                .SetorID = Record("CD_SETOR")
            }
        End Function
    
        Public Shared Function GetAllDbamvLocalidade() As IQueryable(Of DbamvLocalidade)
            Dim Lista As New List(Of DbamvLocalidade)
            Dim StrConn As String = My.Settings.MvProducao
            'criar o comando
            Dim Sql As New StringBuilder
            Sql.Append("SELECT CD_LOCALIDADE, DS_LOCALIDADE, CD_SETOR FROM DBAMV.LOCALIDADE ORDER BY DS_LOCALIDADE")
            'atribuir os parâmetros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Using Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                While Dr.Read
                                    Lista.Add(CreateDataRecord(Dr))
                                End While
                            End If
                            Return Lista.AsQueryable
                        End Using
                    Catch ex As Exception
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Function
    
    End Class
    
    
    
    Imports System.ServiceModel.DomainServices.Server
    
    Public Class DbamvDomainService
        Inherits DomainService
    
        <Query(IsDefault:=True)>
        Public Function GetDbamvSetor() As IQueryable(Of DbamvSetor)
            Return DbamvSetor.GetAllDbamvSetor
        End Function
    
        <Query(IsDefault:=True)>
        Public Function GetDbamvLocalidade() As IQueryable(Of DbamvLocalidade)
            Return DbamvLocalidade.GetAllDbamvLocalidade
        End Function
    
        Protected Overrides Function Count(Of T)(query As System.Linq.IQueryable(Of T)) As Integer
            Return query.Count
        End Function
    
    End Class
    this is just a small part of the code to be an example for those who are in need.

    • Edited by Marden LR Thursday, July 19, 2012 4:14 PM
    • Marked as answer by Marden LR Thursday, July 19, 2012 4:14 PM
    Thursday, July 19, 2012 4:12 PM
  • Nice solution, only issue I can spot that you need to be aware of is that your data reader will read ALL the database records into the list and then perform a query on that in-memory list and return only the page you want. That means, as the underlying table gets larger and larger, the list will get bigger and bigger, consuming more and more memory.

    Unfortunately, there is no easy solution to the problem that I'm aware of. Ideally you'd like to be able to take advantage of .Take() and .Skip() inside that method, but I've not yet found a way to execute generic SQL or a stored procedure and return an IQueryable where you only load the required page from the database. I've also not found a way to find out what the .Skip() and .Take() values are inside a typical RIA queary method.

    One solution might be to implement your own IQueryable provider (I seem to recall reading about that at some point) or the other way might be to used Table-Valued functions in the new EF, although not sure that is supported by Oracle? Some more information can be read here: Table valued function support in EF

    Regards


    Xander

    Thursday, July 19, 2012 10:30 PM
  • Hi novascape,

    Thanks for the warning.

    comments or ideas like yours are very welcome to help us in a better strategy for our application.

    I was using the entity framework for oracle but the problem is
    that the database has over 4000 tables.
    it is not possible to map through the lightswitch.
    then I tried using the EntityDataModel and selected only the tables I needed.
    falls to another problem.
    have tables with more than 100 columns and EntityDataModel not let me select
    only the columns I need.
    I thought I'd create a view containing only the fields I need, and mapped them
    by EntityDataModel then I could use the resources of the entity framework.
    I'll try to do this and then put the result here.

    Thank you.

    Friday, July 20, 2012 4:49 PM