CODEWITHSUNDEEP
c#
Dag
Dag

Reputation: 71

Updating two databases at the same time

I have an SQL Server instance where I have two databases attached. One is a MS SQL database and the other is a linked server(ODBC) which is an indexed file system (Vision). Let's say the Customer table exists in both db's and should be kept identical. I will populate fields in my application from the linked server, and if any changes are made they should be written to both databases. Field names may also be different in the two db's. I use ADO connections in the application and would normally use adapter.Update if I were working with only one db. As I will be doing quite a lot of db calls throughout the application, I would prefer to make a kind of data handling class which will take care of this and leave me with a simple call to this class. I was also thinking of making some kind of db-transaction to ensure both systems will stay identical. Does anybody have a suggestion on how to approach this?

Upvotes: 0

Views: 123

Answers (1)

dutzu
dutzu

Reputation: 3910

I'm thinking you can have 2 separate projects for handling the DataLayer (one for each db) and expose them through a Facade/Adapter that will handle delegating the CRUD operations to both of them, also handling the necessary conversions (you mentioned the fields are not named the same).

In the Facade/Adapter you can also implement Retry Logic and Transactions to ensure both data sources are in sync.

Upvotes: 1

Related Questions