locked
WCF Web service to access data from 2 databases RRS feed

  • Question

  • User1150754510 posted

    I've searched and haven't found a consistent answer.

    I'm creating a web service that will read data from an oracle DB, then pass the dataset by calling a stored procedure in a remote MS SQL server.  

    Key points:

    - Each record will only contain types of VARCHAR, NUMBER, and DATE
    - Each record will contail about 7 fields
    - There will be no more than 800 records passed 
    - The database servers are owned by our company though they may be on a different LAN. 

    My basic question is:
    1) What's the best way to access the disparate databases, particularly the remote SQL Server DB? I could just do it via ADO.NET using a connection string (preferred), or a web service, or something else? 
     

    Friday, July 19, 2013 12:24 PM

Answers

  • User-488622176 posted

    What is the input for the web service? If none, why are you using a web service?

    I'd consider these options:

    • Bulk read data from Oracle DB using DataReader and DTO's for in memory data storage (over Dataset for performance & memory consumption reasons)
    • Prepare the SQL statements and bulk insert them into SQL server
    • Put all in the same routine
    • Preferably run asynchronously (even if you need a webservice, use an anynchronous proxy !)
    • direct data access using a db connection is faster then doing things the wcf/service way.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 26, 2013 7:34 AM

All replies

  • User220959680 posted

    What's the best way to access the disparate databases, particularly the remote SQL Server DB? I could just do it via ADO.NET using a connection string (preferred), or a web service, or something else? 

    • Expose Oracle data throw the secured web service: http://download.oracle.com/oll/obe/EntityFrameworkWCF/WCFEntityFramework.htm
    • Simple console application that invokes above WCF service and utilise database objects (Stored proc or Function) to store data into SQL server database.
    • Schedule the console application using windows scheduler, so that it runs at desired day/timings.
    Thursday, August 22, 2013 5:36 AM
  • User-488622176 posted

    What is the input for the web service? If none, why are you using a web service?

    I'd consider these options:

    • Bulk read data from Oracle DB using DataReader and DTO's for in memory data storage (over Dataset for performance & memory consumption reasons)
    • Prepare the SQL statements and bulk insert them into SQL server
    • Put all in the same routine
    • Preferably run asynchronously (even if you need a webservice, use an anynchronous proxy !)
    • direct data access using a db connection is faster then doing things the wcf/service way.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 26, 2013 7:34 AM