none
Merge 2 like db's using LINQ RRS feed

  • Question

  • I have 2 identical databases with the same table structures.  I need to merge them all.  Here is the schema (bottom up):

    SigFiles table (identity field)

    Signature table (identity field) foreign key Signature.ImgFile = SigFiles.ID

    Letters table (identity field) foreign key Letters.SignId = Signature.ID

    Records table (uniqueid) foreign keys Records.LetterId = Letters.Id AND Recrods.CustId = CustomerInfo.ID

    CustomerInfo table (identity field) foreign key CustomerInfo.ID = Records.CustID

    All other columns will be a straight copy.


    *** Please allow me to mark threads as answered and I will, Thank you ***
    Monday, August 8, 2011 1:21 PM

All replies

  • Hi ski,

    Welcome!

    According to your description, if you want to use LINQ to merge the two databases I think you can Detach all records from one context then Attach them to another one. There is a Union method to merge to collection(http://msdn.microsoft.com/en-us/vcsharp/aa336761.aspx#union1).

    But I don't think LINQ is a good way to handle this scenario, My suggestion is use Ado.net Bulk Copy(http://msdn.microsoft.com/en-us/library/7ek5da1a.aspx) or Dataset.Merge(http://msdn.microsoft.com/en-us/library/system.data.dataset.merge.aspx)

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 9, 2011 7:43 AM
    Moderator
  • my biggest problem is matching the keys back up with all the tables.  I would be appending the 2nd tables data into the first.
    *** Please allow me to mark threads as answered and I will, Thank you ***
    Tuesday, August 9, 2011 12:02 PM
  • Hi ski,

    The databases' structure is same, we just need to transfer the data from one database to another. Key is the structrue level of datatable.

    Please feel free to let me know your feedback, I'm not very clear about your question.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 24, 2011 6:04 AM
    Moderator
  • my biggest problem is matching the keys back up with all the tables.  I would be appending the 2nd tables data into the first.
    *** Please allow me to mark threads as answered and I will, Thank you ***


    Am I right in thinking that you are worried that the same 'CustomerInfo.ID' (for example) might appear in both databases but representing different entities? Therefore when you merge the Records table, its Records.CustId could refer to the wrong CustomerInfo - so you would have to renumber the foreign keys to match the newly assigned numbers for the transferred customers.

    If so, you could achieve it with Linq but it would be a bit cumbersome (unless an expert knows a better way). It basically involves making new copies of each of the existing objects from the source database and linking them to each other before inserting them into the destination database.

    So declare two DataContexts, one for the source database and the other for the destination. The code would look something like this...

    foreach (CustomerInfo sourceCustInfo in sourceDb.CustomerInfos)
    {
    CustomerInfo destCustInfo = new CustomerInfo();
    
    // populate destCustInfo with the attribute properties of sourceCustInfo
    
    
     foreach(Record sourcRecord in sourceCusstInfo.Records)
     {
     Record destRecord = new Record();
     // populate this with the attribute properties of sourceRecord
     
     // Associate the new destRecord with the new destCustInfo
     // This will make the foreign key right.
     destRecord.CustomerInfo = destCustInfo
     destDb.InsertOnSubmit(destRecord);
     
     foreach (Letter .....)
     {
      //etc.
     }
     }
    
    destDb.InsertOnSubmit(destCustInfo);
    
    db.SubmitChanges();
    
    }
    

     


    Does that help?





     

    Wednesday, August 24, 2011 7:59 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 1, 2011 6:15 AM
    Moderator