Solved

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

Posted on 2010-08-12
3
1,182 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
  • 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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