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.
