none
Update data across servers RRS feed

  • Question

  • We have a .net application, using SQL server, for one table, when insert or update a row, the business requires to also do the same thing on a table in a oracle database of another application on the same domain. What's the best way of establish the coonection with oracle, and update the records there. In our environment,new don't use liked server for security reasons, any other better and safe ways? Thanks

    SQLFriend

    Saturday, April 27, 2013 3:20 PM

Answers

  • For accessing Oracle, you'll need to add a reference to the Oracle.DataAccess.dll to your project and use the Oracle.DataAccess.Client in your code. The OracleConnection, OracleCommand and OracleDataAdapter work exactly the same as their SQL counterparts.

    Wrap up the access to both your SQL Server and Oracle databases under one TransactionScope block, in order to be sure that you don't update one without updating the other.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by msloy Monday, April 29, 2013 9:15 PM
    Saturday, April 27, 2013 4:39 PM

All replies

  • For accessing Oracle, you'll need to add a reference to the Oracle.DataAccess.dll to your project and use the Oracle.DataAccess.Client in your code. The OracleConnection, OracleCommand and OracleDataAdapter work exactly the same as their SQL counterparts.

    Wrap up the access to both your SQL Server and Oracle databases under one TransactionScope block, in order to be sure that you don't update one without updating the other.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by msloy Monday, April 29, 2013 9:15 PM
    Saturday, April 27, 2013 4:39 PM
  • There is two ways to do this and it should be at the database level

    You can setup in the SQL server a linked server and update a table in the Oracle server based on a trigger on the SQL table that is updated

    OR

    You can create an exchange database where you update a table based on a trigger on the SQL table that is updated and then in the Oracle server create a job that will come read this table periodically.

    We have a customer like this where we exchange data between 3 databases, and use the two methods depending if the data needs to be live or not. Works perfetcly, never had a problem.

     
    Wednesday, May 1, 2013 12:04 PM