Solved

compare & update a MS access database through CSharp code

Posted on 2007-04-02
10
222 Views
Last Modified: 2008-01-09
I have 2 databases,old and the new.Now ,I need to compare the old from the new db & update the old from the new instead of overwriting the old db. db is the term used for db. I work with Ms access db's & C#.net .

Can anyone tell me how to i compare & update instead of overwrite the databases..is there any C# script to be written..

Thanx in advance
0
Comment
Question by:Anisha
  • 4
  • 3
10 Comments
 
LVL 41

Expert Comment

by:graye
ID: 18837211
Luckly, this scenario was anticipated by the folks who wrote ADO.Net...  

Here is how you'd do it...   You'd do a normal DataAdapter.Fill operation for each of the tables (the old and the new).   Then you'd do a DataSet.Merge operation.  This will create a DataTable what has rows that are marked for insertion, deletion,  or modification based  upon the old and new tables.   The last step is to save these changes to the underlying database with the DataAdapter.Update method.

http://msdn2.microsoft.com/en-us/library/system.data.dataset.merge.aspx
0
 

Author Comment

by:Anisha
ID: 18842598

Hi Graye,

I tried working with the merge functions....But this is not exactly what i want to do..

1)If new records are inserted into the New db,i would like the old db too to get the newly inserted record from the new db.

2)If i have to update records in the old db from the new db, that is with  the same primary key but with different data entry in the fields for that particular primary key record ....so i wud like the table to add a new record with an automated primary key ,to which the record could be added...instead of overwritting the existing record( same primary key record with different data) in the old db .

How do i go about with these problem?
0
 
LVL 41

Expert Comment

by:graye
ID: 18842942
Let me see if I understand...

1) an Insert in "New", should create an Insert in "Old"
2) an Update in "New", should create a new Insert in "Old"?

Is that it?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Anisha
ID: 18848679
Yes graye!!

1) an Insert in "New", should create an Insert in "Old"
2) an Update in "New", should create a new Insert in "Old"?
Thats right,
1) an Insert in "New", should create an Insert in "Old"1) an Insert in "New", should create an Insert in "Old"
2) an Update in "New", should create a new Insert in "Old"?
2) an Update in "New", should create a new Insert in "Old"?

Can u help out with a solution,,,,...or should i manually code each step to manually compare each record and each field & then insert records if required...which is going to be a tedious task..if there is any better solution to save time...kindly guide.

Thanx

0
 

Author Comment

by:Anisha
ID: 18855697
Yes graye!!

1) an Insert in "New", should create an Insert in "Old"
2) an Update in "New", should create a new Insert in "Old"?
Thats right,

Can u help out with a solution,,,,...or should i manually code each step to manually compare each record and each field & then insert records if required...which is going to be a tedious task..if there is any better solution to save time...kindly guide.

Thanx
0
 
LVL 41

Expert Comment

by:graye
ID: 18856980
I don't see any "easy way" to do with... although the "hard way" isn't all that bad.

I'd suggest you use the DataTable.Select method to find all of the rows that have been changed/added (via the DataViewRowState property).  Then you'd have to write code to perfom the inserts of those rows into the other table.

http://msdn2.microsoft.com/en-us/library/b5c0xc84.aspx
0
 
LVL 41

Accepted Solution

by:
graye earned 125 total points
ID: 18857276
Here is a quick-n-dirty example to demontrate the concepts

            foreach (DataRow dr_new in dt_new.Select(null, null, (DataRowState.Added | DataRowState.Modified)))
            {
                DataRow dr_old;
                // copy the rows from the one table to another
                dr_old = dt_old.NewRow();
                dr_old.ItemArray = dr_new.ItemArray;
                dt_old.Rows.Add(dr_old);
            }
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
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…

747 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

12 Experts available now in Live!

Get 1:1 Help Now