Solved

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

Posted on 2010-08-12
3
1,193 Views
Last Modified: 2013-12-17
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
Comment
Question by:ARahimAwan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 33428627
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
 
LVL 4

Author Comment

by:ARahimAwan
ID: 33478685
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
 
LVL 30

Accepted Solution

by:
Alexandre Simões earned 500 total points
ID: 33488317
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question