Update Problem Please Help

[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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Seth]

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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Seth]

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

 

[William Ryan eMVP]

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/

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Seth]

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

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/

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

one

by and
is will
get

 

[William Ryan eMVP]

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/

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

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/

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 one call
the

table

to

 

[Seth]

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

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/

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

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/
How can I accomplish this if I pass one table at a time to update?

I

am

table

at confers
no added,
and table
one commands
and I
will table