Update Problem Please Help

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

Hi,

I am having a problem updating a dataset with modified, added, and deleted
rows contained in it. Since this dataset has 4 datarelations (parent-child)
contained in it. The problem I am running into is the different
combinations makes my updating logic (the order I update each table)
different everytime. What I do is pass one by one each table one by one to
a dataadapter which has the insert, update and delete commands and is done
uisng a transaction object. There can nulls in some of the foreign keys
columns in the tables!

For example, when I delete a row in the parent table I must call the update
on that
child table before I call the update on the parent table or I will get a
constraint error. However, if there is an added row in the parent table I
must call the update on the parent table before the child table to remove
the key pointing to that row.

So how do I deal with this? Do I have set flags in my business objects to
set the update order? Or is there a more elgant solution around this?

Thanks
 
I discuss this in my article on handling @@Identity issues. See
http://www.betav.com/msdn_magazine.htm.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I have seen that article however I do not think it helps me in my scenerio.
First of all I am using Guid as ID's which is generated by the application.
Also, I am using stored procedures (manual code) for updates (insert, delete
and update commands) and updating one table at a time. Am I missing
something from that article?

Thanks
 
Are you using the DataAdapter.Update method? If so, you still need to filter
your updates to insert parent rows first--then add children. Next, delete
child rows.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
How can I accomplish this if I pass one table at a time to update? I am not
updating the whole dataset by passing it to my data adapter but rather
passing the data table to my data access layer. So, I still am not getting
on how I can filter the update if I am updating the db one data table at a
time.

Thanks
 
Seth:

Bill's right but I think the thing that's throwing you is this. You can use
RowState enumeration to get rows that are Added, Modified, or Deleted . So
you can use .GetChanges for instance and use one of these filters to get
only the Deleted rows for the child table for instance.

so call you DAL method with something like

dataAccessMethod(dataSetName.Tables[whateverIndex].Select("", "",
DataViewRowState.Added)
I'm actually getting a little ahead of myself but there are two possible
filters. RowState and DataViewRowState, the latter of which will give you
original values, ModifiedCurrent and a few others. Anyway, by using those
filters you can pass just the rows you want, in whatever succession you want
to the update.

Remember though that depending on how you make the call, you will probably
need to call acceptchanges on those rows upon confirmation that the update
was good, otherwise the local rowstate won't correspond with what's in the
db.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
You use a DataView on the DataTable to filter the changes one type at a
time.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I understand how to get the rows however I does my dataadapter/dalc know
which stored procedure to call? Or create the correct command objects? My
data access layer is very similiar to the MS Data Application Block but
built for multiple data sources.

Thanks

William Ryan eMVP said:
Seth:

Bill's right but I think the thing that's throwing you is this. You can use
RowState enumeration to get rows that are Added, Modified, or Deleted . So
you can use .GetChanges for instance and use one of these filters to get
only the Deleted rows for the child table for instance.

so call you DAL method with something like

dataAccessMethod(dataSetName.Tables[whateverIndex].Select("", "",
DataViewRowState.Added)
I'm actually getting a little ahead of myself but there are two possible
filters. RowState and DataViewRowState, the latter of which will give you
original values, ModifiedCurrent and a few others. Anyway, by using those
filters you can pass just the rows you want, in whatever succession you want
to the update.

Remember though that depending on how you make the call, you will probably
need to call acceptchanges on those rows upon confirmation that the update
was good, otherwise the local rowstate won't correspond with what's in the
db.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Seth said:
How can I accomplish this if I pass one table at a time to update? I am not
updating the whole dataset by passing it to my data adapter but rather
passing the data table to my data access layer. So, I still am not getting
on how I can filter the update if I am updating the db one data table at a
time.

Thanks
Click to expand...
one
by and
is will
get
Click to expand...
Click to expand...
 
Modify it so it takes an enum for instance

public enum ProcedureAction: uint{
Update
, Insert
, Delete
}
Then just branch off or call the respective stored proc. This part isn't
much different than a regular adapter b/c all it does is check the rowstate,
then decide which command to fire (it rowstate isn't unchanged).

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Seth said:
I understand how to get the rows however I does my dataadapter/dalc know
which stored procedure to call? Or create the correct command objects? My
data access layer is very similiar to the MS Data Application Block but
built for multiple data sources.

Thanks

William Ryan eMVP said:
Seth:

Bill's right but I think the thing that's throwing you is this. You can use
RowState enumeration to get rows that are Added, Modified, or Deleted . So
you can use .GetChanges for instance and use one of these filters to get
only the Deleted rows for the child table for instance.

so call you DAL method with something like

dataAccessMethod(dataSetName.Tables[whateverIndex].Select("", "",
DataViewRowState.Added)
I'm actually getting a little ahead of myself but there are two possible
filters. RowState and DataViewRowState, the latter of which will give you
original values, ModifiedCurrent and a few others. Anyway, by using those
filters you can pass just the rows you want, in whatever succession you want
to the update.

Remember though that depending on how you make the call, you will probably
need to call acceptchanges on those rows upon confirmation that the update
was good, otherwise the local rowstate won't correspond with what's in the
db.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Seth said:
How can I accomplish this if I pass one table at a time to update? I
Click to expand...
am
not
updating the whole dataset by passing it to my data adapter but rather
passing the data table to my data access layer. So, I still am not getting
on how I can filter the update if I am updating the db one data table
Click to expand...
Click to expand...
at
a one call
the
Click to expand...
table
to
Click to expand...
Click to expand...
 
I understand what you are saying. However, I have 30 different stored
procedures it could call and I do want to put any logic into my data access
layer because it is self contained and reusable. From what I am gathering
there is not an elegant solution to this so I guess I am going to build the
logic into my business objects. I am going use flags to record when I
delete a row and when I add a row. Unless I am still not understanding what
you are trying to tell me!

Thanks again for your responses

William Ryan eMVP said:
Modify it so it takes an enum for instance

public enum ProcedureAction: uint{
Update
, Insert
, Delete
}
Then just branch off or call the respective stored proc. This part isn't
much different than a regular adapter b/c all it does is check the rowstate,
then decide which command to fire (it rowstate isn't unchanged).

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Seth said:
I understand how to get the rows however I does my dataadapter/dalc know
which stored procedure to call? Or create the correct command objects? My
data access layer is very similiar to the MS Data Application Block but
built for multiple data sources.

Thanks

William Ryan eMVP said:
Seth:

Bill's right but I think the thing that's throwing you is this. You
Click to expand...
can
use
RowState enumeration to get rows that are Added, Modified, or Deleted
Click to expand...
..
So
you can use .GetChanges for instance and use one of these filters to get
only the Deleted rows for the child table for instance.

so call you DAL method with something like

dataAccessMethod(dataSetName.Tables[whateverIndex].Select("", "",
DataViewRowState.Added)
I'm actually getting a little ahead of myself but there are two possible
filters. RowState and DataViewRowState, the latter of which will give you
original values, ModifiedCurrent and a few others. Anyway, by using those
filters you can pass just the rows you want, in whatever succession
Click to expand...
you
want
to the update.

Remember though that depending on how you make the call, you will probably
need to call acceptchanges on those rows upon confirmation that the update
was good, otherwise the local rowstate won't correspond with what's in the
db.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
How can I accomplish this if I pass one table at a time to update?
Click to expand...
Click to expand...
I
am
Click to expand...
table
at confers
no added,
and table
one commands
and I
will table
Click to expand...
Click to expand...
 
Back
Top