Solved

compare & update a MS access database through CSharp code

Posted on 2007-04-02
10
226 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

11 Experts available now in Live!

Get 1:1 Help Now