How to merge Oracle with Microsoft SQL using WCF RIA Service?

One of my clients requires me to synchronize their Oracle database to the MS SQL database that is used by the Microsoft Ligthswitch application.  Since the record for synchronization is more than 10,000 I have no choice but to merge the two database using WCF RIA Service.

Notice below that we have two datasources, oracle and pos_seaolympusData, these datasources are provided by two different RDBMS, one is Oracle and the other one is MS SQL.

I will not go into details anymore on the creation of a WCF RIA Service project, I have that documented in another post, but I will emphasize on modifying the Domain Service to accommodate two context.  But before you could modify the Domain Service class you need to attach the datasource used by your Microsoft Lightswitch project.

Once you’re done attaching the two datasource models from your Microsoft Ligthswitch project, It’s time to modify the Domain Service class and here are the steps:

Take note the every code written from 2-3 is inside the Domain Service class, please refer to my previous post.

1. Add a POCO Class

Public Class mstNewItemFromOracle
    <Key()>
    Public Property ItemCode As String
    Public Property ItemDescription As String
    Public Property Unit As String
End Class

2. Add a new context

What is important here is the builder provider, which is the only difference of the two context except for the name of data context.

    Private _context As pos_seaolympusData.Implementation.pos_seaolympusDataObjectContext
    Private _contextOracle As oracle.Implementation.oracleObjectContext

    Public ReadOnly Property Context As pos_seaolympusData.Implementation.pos_seaolympusDataObjectContext
        Get
            If _context Is Nothing Then
                Dim builder = New EntityConnectionStringBuilder
                builder.Metadata =
                    "res://*/pos_seaolympusData.csdl|res://*/pos_seaolympusData.ssdl|res://*/pos_seaolympusData.msl"
                builder.Provider = "System.Data.SqlClient"
                builder.ProviderConnectionString =
                    WebConfigurationManager.ConnectionStrings("pos_seaolympusData").ConnectionString

                _context =
                    New pos_seaolympusData.Implementation.pos_seaolympusDataObjectContext(
                      builder.ConnectionString)
            End If
            Return _context
        End Get
    End Property

    Public ReadOnly Property ContextOracle As oracle.Implementation.oracleObjectContext
        Get
            If _contextOracle Is Nothing Then
                Dim builder = New EntityConnectionStringBuilder
                builder.Metadata =
                    "res://*/oracle.csdl|res://*/oracle.ssdl|res://*/oracle.msl"
                builder.Provider = "Oracle.DataAccess.Client"
                builder.ProviderConnectionString =
                    WebConfigurationManager.ConnectionStrings("oracle").ConnectionString

                _contextOracle =
                    New oracle.Implementation.oracleObjectContext(
                      builder.ConnectionString)
            End If
            Return _contextOracle
        End Get
    End Property

3. Make a query

This is a standard default query which now uses the two tables situated on different context.

    <Query(IsDefault:=True)>
    Public Function GetmstNewItemFromOracleR001() As IQueryable(Of mstNewItemFromOracle)
        Return From s In Me.ContextOracle.INNOSOFT_ITEM_GROUPs
               Where Me.Context.mstItems.Where(Function(i) i.ItemCode = s.SEGMENT1).Count = 0
               Select New mstNewItemFromOracle With {
                        .ItemCode = s.SEGMENT1,
                        .ItemDescription = s.DESCRIPTION,
                        .Unit = s.PRIMARY_UOM_CODE
               }
    End Function

4. Consume the query into your Microsoft Lightswitch project

Again, to consume the WCF RIA Service just refer to my previous post.

This is big stuff for us Microsoft Access developers, why, because Microsoft Ligthswitch now can mimic multiple database link and then query from those link as if they were situated in one database software.

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in Lightswitch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: