Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why, and when, to use Windows Workflow 1 31
Help with deleting records with two duplicate columns using VB.NET 12 43
Achieve json result 2 64
Syntax error 9 45
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now