• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1214
  • Last Modified:

How to use Entity Framework 4 to copy data between two database

Hi,

I have to copy records  from DB A on server A to DB B on server B. The schema of both databases is almost identical  with little difference in table and column names (each db has it's own prefix with table names).  How can I do the following:

1. Use single set of entity classes for both databases.
2. Read data from db A and insert it to Db B maintaining the foreign key relationship. Please note each db has it own foreign key I.e. If it wer product record in db A the foreign key for category 'beverages' could be different than in db B.
3. Read data from db A and update data in db B. It seems challenging as both DBs have differnet auto generated primary key for same record although there are other unique keys abailabe which should be used to updat the data.

Any help in right direction is highly appreciated.
 
0
ARahimAwan
Asked:
ARahimAwan
  • 2
1 Solution
 
Alexandre SimõesManager / Technology SpecialistCommented:
This task is a batch.. you don't want to add extra weight on top of that.
You don't need any of the EF4 features, you just want to execute a plain SQL INSERT INTO SELECT instruction, so why run away from it?
I don't think it's even possible because you actually want to use a single edmx...

My advise, forget about it.

Write a stored procedure that does that, it may even receive parameter to allow some level of configuration and you're done.
0
 
ARahimAwanAuthor Commented:
Infact the same task has to be done for different set of tables that have similar hirarechy.  It's just like differen sub types of a super type. Like today we have to do this task for cars (sub type of vehicle) n tomorrow we'll need to do the same for buses n then maybe for trucks n trailers. So if this task could be done using some framework that could save us writing similar kind of insert/update checks for tables with lot of fields in them.  
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
That is some kind of ETL process that may be executed whenever you want.
So I keep my advise, forget about making it part of your DAL project, do it somewhere else.

If you're using SQL Server you can create an SSIS package that let you visually make create the mappings.
You can then execute this package whenever you want.

As a last option, you could do this using Reflection, but it must be done on its own project anyway.
This project must have access to all the objects involved and using reflection you can inspect the objects and compare the properties by name and type, if they match copy the "subtype" property value to the main type property.
At the end just call the main type save method.
This is the only way I can see that you'll never have to touch this code again. You can even change the objects properties, it will only work with the properties that match.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now