• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

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.
0
sercanparlak
Asked:
sercanparlak
  • 9
  • 8
1 Solution
 
gena17Commented:
Each DataRow in a DataTable has its own status - DataRowState (Added, Deleted, Detached, Modified, or Unchanged).
Based on this status each row is updated during the first dataAdapter.Update() method.
After the first update all rows get the status Unchanged, thus there is no database update in the second time.
You should update exactly the database that you loaded data from, otherwise it could be a problem.
But, if you want to update 2 databases, I think you should load data 2 times from 2 different databases.
0
 
sercanparlakAuthor Commented:
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?
0
 
gena17Commented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sercanparlakAuthor Commented:
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?
0
 
gena17Commented:
> 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?
0
 
sercanparlakAuthor Commented:
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?
0
 
gena17Commented:
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.
0
 
sercanparlakAuthor Commented:
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.
0
 
gena17Commented:
If your databases will be synchronized, why do you need double update?
0
 
sercanparlakAuthor Commented:
double update will be the part of synchronizing.
0
 
gena17Commented:
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.
0
 
sercanparlakAuthor Commented:
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.
0
 
gena17Commented:
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.
0
 
sercanparlakAuthor Commented:
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?
0
 
gena17Commented:
> 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
0
 
sercanparlakAuthor Commented:
thank you so much gena. it's now working. i am  very grateful.
0
 
gena17Commented:
Great!
Good luck!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now