asked on

How to update two different database with one dataset

hi,
i hava a datase in sql server and one in access as the same structure. I want to update this 2 database's the same named tables with the same dataset.

when i wrote my code like this:
daMusteriFilter.Update(dsMusteri.Tables["tblMusteriFilter"]);
oleDbDataAdapter1.Update(dsMusteri.Tables["tblMusteriFilter"]);

it updates only sqlserver.

if i wrote

oleDbDataAdapter1.Update(dsMusteri.Tables["tblMusteriFilter"]);
daMusteriFilter.Update(dsMusteri.Tables["tblMusteriFilter"]);

it only updates access. I can't understand what'is the problem.

I want to ask this: is it possible to update 2 database with one dataset. If yes how?

Thanks.

ASKER CERTIFIED SOLUTION

gena17

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sercanparlak

ASKER

is this code is possible :

DataSet ds = new DataSet();
ds=dsMusteri;

daMusteriFilter.Update(ds);
oleDbDataAdapter1.Update(dsMusteri.Tables["tblMusteriFilter"]);

i tried but it didn't worked too. Where is the problem. Is it changing the status again?

gena17

Yes, it changes the status on each Update.
Think about it - it wants to store the synchronization status between your local DataSet and the database.
When you run: --- daMusteriFilter.Update(ds); --- all the rows in the DataSet are updated and get the status Unchanged, because everything is synchronized.

sercanparlak

ASKER

but i'm creating a new dataset object and assign my dataset to created dataset :

ds=dsMusteri;

After this, i am updating adapters with different datasets. the first update realy affects the other dataset, so it doesn't updates other database?

gena17

> ds=dsMusteri;

You don't create a new DataSet object here.
Since DataSet is reference type, you only create another pointer to your DataSet.
So, you have one object and two pointers to this object.

Can you give a few words about what are you doing? Why do you manage two databases?

sercanparlak

ASKER

i wrote a program that users will change the table's records. fill method of dataadapter will work local, that gets records from access database on their computer. I'm trying to update that access database and an other database(sql server) which is on my computer away from users.

Is it possible to save datarows's state after update?

gena17

I don't think it is matter of possible. I think it is incorrect.
Let's say loaded a row from user's local db. And this user updated the row.
The row gets Updated status.
If you call da.Update() it will try to update the row in the DB.
And if this rows doesn't exist in your server DB? It probably will be the case since each user works with his local database.
You need some other mechanism to update your server database. But you didn't tell what do you want this database to store exactly.

sercanparlak

ASKER

there will be more than 10 departments will use this program. Each department will have their own database(ms access). I'm getting all records from them. I will use this for reports. Don't worry about if rows doesn't exist. My database will be synchronized.

gena17

If your databases will be synchronized, why do you need double update?

sercanparlak

ASKER

double update will be the part of synchronizing.

gena17

In this case I recommend you to delete all the rows you have in the dataset from your server database and insert them from the dataset back.

sercanparlak

ASKER

I think it isn't a good way. it will use large bandwith wich is very important in this project. Delete and insert operation will reduce my server's performance. Records count will be increasing day by day. I don't want to think this operation if i have a huge database.

I desinged an algorithm for synchronization, i'm trying to code this algorithm.

gena17

Ok, you can create the second update manually:
Copy your dataset.
Save rows statuses in some place.
Manually run INSERT, DELETE, and UPDATE statements based on statuses you saved.

sercanparlak

ASKER

DataSet ds = new DataSet();
ds=dsMusteri;

dsMusteri is the original dataset and ds is copied dataset. i copied with this code, but it didn't worked. I guess i copied dataset wrongly. Where is my wrong?

gena17

> ds=dsMusteri;

You don't create a new DataSet object here.
Since DataSet is reference type, you only create another pointer to your DataSet.
So, you have one object and two pointers to this object.

Learn here how to actually copy:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclasscopytopic.asp

sercanparlak

ASKER

thank you so much gena. it's now working. i am very grateful.

gena17

Great!
Good luck!