Solved

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

6 Experts available now in Live!

Get 1:1 Help Now