Updating Dataset

[Guest]

Hi,

In my application I use typed datasets to persist the user input during the
life of the application until the user submits the data to the db. The
application can open a existing record from the db, add a record via another
source (integrated with another app via xml) or add a new blank record. The
only interaction with the db is at the beginning if opening an exsiting
record or at the end when either updating record or inserting new record.
Basically the application uses the db as a persisting object and nothing
else. The problem I am having is trying to use the dataadapter with the
dataset to update the db. I am trying to understand why do I have to create
3 command objects (update, insert and delete) when I have to insert a new
record? The db structure is similiar to Order-Order Details-Product however
it is more complex (many data relations). Another problem I am having is
there are times when I do not know if this record exists or not because not
always does this record come from the db. So, I do not know if I need to
use the update or insert stored procedure. I thought that the dataadapter
would know when to use either because I submit both commands however this is
not the case. Right it does not work in my testing. It works on the insert
however it does not work if the record already exists. Any ideas?

Thanks

 

[William Ryan eMVP]

Hi,

In my application I use typed datasets to persist the user input during the
life of the application until the user submits the data to the db. The
application can open a existing record from the db, add a record via another
source (integrated with another app via xml) or add a new blank record. The
only interaction with the db is at the beginning if opening an exsiting
record or at the end when either updating record or inserting new record.
Basically the application uses the db as a persisting object and nothing
else. The problem I am having is trying to use the dataadapter with the
dataset to update the db. I am trying to understand why do I have to create
3 command objects (update, insert and delete) when I have to insert a new
record?

You don't. All you technically need is a valid select command to get the
Adapter to work. However, dataadapter.Update goes through row by row, looks
at the rowstate and then calls the DeleteCommand for any row with a rowstate
of deleted, InsertCommand for any row with Inserted and UpdateCommand for
antyhing that's modified. If you have rows that are deleted and no delete
command, it will blow up but if you set ContinueUpdateOnError to true, then
it will continue. It's not a very clean way to handle the problem and I'm
not recommending it, I'm just pointing out that it can work in the absence
of a Delete command for instance

To that end, other than having to create the extra command objects, what
particular problem is it causing? You can use GetChages fo the datatable to
get the changed rows and then use the RowStateFilter to get a subset of only
the inserted rows, deleted rows and modified rows. You can call update
against any of these subsets.
http://www.knowdotnet.com/articles/dataviews1.html

So if you only want to call update against New rows, just filter the
rowstate to rows iwth a rowstate of inserted and call update against them.
Afterward, you can call acceptchanges (make sure your update worked though)
and you will unmark the rowstate of the deleted and modified rows (you can
do this for each subset, any one or two of the subsets or whatever you
please).

The db structure is similiar to Order-Order Details-Product however

it is more complex (many data relations). Another problem I am having is
there are times when I do not know if this record exists or not because not
always does this record come from the db. So, I do not know if I need to
use the update or insert stored procedure. I thought that the dataadapter
would know when to use either because I submit both commands however this is
not the case. Right it does not work in my testing. It works on the insert
however it does not work if the record already exists. Any ideas?

Thanks

If I undertsand the problem, the suggestion I mentioend above, filtering by
rowstate w/ the rowstate filter and then calling update on that subset
should solve this. As far as does it already exist...you'll get one of a
few exceptions depending on the situation..If the value already exists in
the DB and you call an insert command with it, you'll get a PrimaryKey
violation (SqlException for instance with a message that will have somethign
like Can't inset duplicate record...). You can trap this and just eat the
exception. Normally I don't think this is a good idea(just eating
exceptions) b/c it's inefficient, but the only alternative is to check and
see if the value exists first which entials a preliminary Select statement,.
This isn't efficient either. Or, you could write your insert logic such
that it has a subquery to check for the existence of the value beforehand.
If you are using Sql SErver or Oracle, you can easily implement this in a
Stored procedure. But if not, you could just eat the exception.

I think this should work if I understood your question correctly, but if
not, let me know and hopefully we can clear it up.

Cheers,

bill

 

[Guest]

William,

Thanks for the post. My application is different from typical db
applications because it deals with one record at a time. This means that
the datasets will only have one record at a time. It strictly uses the db
to persist the data, the application is a highly scientific application
where it uses user input to do calculations and then reports to the user.
So in this case filtering the dataset for modified rows is really not
needed. I was hoping to pass the dataset to my data access layer and save
it the db via the insert or update command. The application will never
delete a record this is action is only done by the DB admin. I have
designed my data access layer similiar to MS's data application block with
some minor tweaks. I used the UpdateDataSet method where you have to pass
in all three commands. I have some new questions:

1) If I pass the dataset to my update dataset with update and insert
commands and if the record exists will it automitically use the update
command?

2) I have many data relations in my db structure and I have mapped out my
business entities (typed datasets) to match these. For an example, I have a
products table which has 5 one to many relationships with 5 other tables and
I map these relationships in one typed dataset. There are times when not
every table will have data and each key is autoincremental. I understand if
I have these data relations defined each key will be defined where it is
used. This is the real reason I used typed datasets because of the
autoincremental management built in and also the optimistic concurrency
issue. The problem I am running into is the order I have to insert/update
the db. Since there are times when some tables are not needed to update the
insert/updating the db is not know at design time. It is dynamic at
runtime, the only way around this is testing to see if a datatable has been
modified (record added) and if so I update that table. Is there a better
way to do this via a dataset controlling it?

Just as a FYI, I am using MS SQL Server and I am going to take your advice
and redesign my insert stored procedure.

Thanks for all your help and I think your site is great.

 

[William Ryan eMVP]

Hi Eric, and thanks for the compliment.

William,

Thanks for the post. My application is different from typical db
applications because it deals with one record at a time. This means that
the datasets will only have one record at a time. It strictly uses the db
to persist the data, the application is a highly scientific application
where it uses user input to do calculations and then reports to the user.
So in this case filtering the dataset for modified rows is really not
needed. I was hoping to pass the dataset to my data access layer and save
it the db via the insert or update command. The application will never
delete a record this is action is only done by the DB admin. I have
designed my data access layer similiar to MS's data application block with
some minor tweaks. I used the UpdateDataSet method where you have to pass
in all three commands. I have some new questions:

1) If I pass the dataset to my update dataset with update and insert
commands and if the record exists will it automitically use the update
command?

If the rowstate is modified and your update logic is correct, then yes. It
totally depends on your update logic though. The values in the Where clause
of the update change drastically depending on how you implement concurrency
checks and you can write it so that it would catch the values or so that it
wouldn.t You couuld do it so the where Set PrimaryKeyValue =
WhateverNewValue WHERE PrimaryKeyValue = OriginalValue .

Now, if you added an and for each field in there other than the PK, you
could cause it to not be found, or you could write it so it finds the value.

The whole thing is that the command it chooses to use will depend
Exclusively on the rowstate. So if the rowstate is marked Inserted, it's
only going to try to run the update command against it. If it's marked as
Modified, it's going to try to run the Update Command against it. Rowstate
exclusively is going to cause the dataadapter to choose a given command for
it.

2) I have many data relations in my db structure and I have mapped out my
business entities (typed datasets) to match these. For an example, I have a
products table which has 5 one to many relationships with 5 other tables and
I map these relationships in one typed dataset. There are times when not
every table will have data and each key is autoincremental. I understand if
I have these data relations defined each key will be defined where it is
used. This is the real reason I used typed datasets because of the
autoincremental management built in and also the optimistic concurrency
issue. The problem I am running into is the order I have to insert/update
the db. Since there are times when some tables are not needed to update the
insert/updating the db is not know at design time. It is dynamic at
runtime, the only way around this is testing to see if a datatable has been
modified (record added) and if so I update that table. Is there a better
way to do this via a dataset controlling it?

I've lost you here, but let could you exlain it with real values for
instance, i promise I'll do what I can. For the record, you don't need to
use a Strongly Typed dataset to take advantage of Autoincrement values. I
have a few articles there on using DataRelations and using AutoIncrement
values. You can combine these two and keep the values synced (although
you'll have to requery the db after the update). Set teh Autoincrement
property of the DataColumn that's your PK to true. Set the
AutoINcrementValue = -1 and the seed to 0. This way, the value submitted to
the db will always be negative so the DB will always step in and assign it.
That way two versions of the app won't ever step on each other. Also, if
you use the Refresh DataSet option with the DataAdapter configuration
wizard, you'll see that it adds an addiotnal select command to your Insert
Command. This is how it refreshes the data. Then the mappings kick in and
the values will update themselves. You may think that an additional SELECT
statement isn't the most efficient things in the world, and I'd agree with
you. Howver, I think this is the best way there is at the moment.

I still think I may be minunderstanding things, but I'll do my best to help
you. I think the whole issue of rowstate is the problem here. If you
'update' a value that doesn't exist in the db and you call dataadapter
update. then it will fire the update statement of the adatper against this
row which will not work. If you add a row to the dataset that already
exists in the db, the dataadapter will fire an insert command against it and
it won't work either. So having the rowstate match the baackend or HAVING
update/insert logic that can check and respond to this stuff is probably the
real solution.

Let me know and I'll do what I can.
Cheers,

Bill

 

[Guest]

William,

Thanks for the help. For some reason I cannot get the data adapter to
insert a new record, it does not error out. I can manually insert a new
record with the data adapter. I am thinking about just manually inserting
and updating the database without using the data adapter and passing the
dataset. I was hoping I could get this to work because it would save
sometime having to pass all the values.

Thanks

 

[William Ryan eMVP]

Hi Eric:

William,

Thanks for the help. For some reason I cannot get the data adapter to
insert a new record, it does not error out.

The whole thing depends on if you have rows that are marked Added. If you
use the overloaded version of HasChanges, you can make this determination:

bool b = myDataSet.HasChanges(DataRowState.Added);

Now, if this is false, then whatever you use for your Insert command will
Never get fired. You can use a similar overload for GetChanges to get just
the rows that are added and call update against it, but remember, if the
above line evaluates to false, no inserts are going to happen with the
DataAdapter's update method:
myNewDataSet = myDataSet.GetChanges(DataRowState.Added);

If you have changes, you can call
myDataAdapter.Update(myNewDataSet.Tables[0]) and if your Insert command is
configured correctly, it will insert these rows.

you can do the same with Modified rows.

But let's say that you know for a fact that a row that was Added to your
dataset already exists in the DB but isn't in the dataset until you just
added it. The rowstate will be Added. It will use the Insert command to
fire the update. However, since it already exists you'll either get a key
violation or it will add it again if no violation exists. However, one
thing you WON'T Get is the existing value being updated.
The way DataAdapter's determine what action to take probably doesn't lend
itself well to the scenario you have set up.


I can manually insert a new

record with the data adapter. I am thinking about just manually inserting
and updating the database without using the data adapter and passing the
dataset. I was hoping I could get this to work because it would save
sometime having to pass all the values.

I think this is probably the best way to handle this and you don't need to
pass 'all of the values' Remember that you can filter the rowstate so you
pass only what was added/modified deleted etc. In this instance, rows that
are added may exist already so you can use the overloaded
getchanges(RowState.Added) to get those rows. Then you can have a loop that
first uses the value of the key field to check if it exists. If it does you
could branch down into an Update command, if it doesn't you could call the
Insert command.

I believe also (but I haven't done it) that if you knew that all of the rows
that were added locally already existed in the DB, you can probably use
write your Insert Command with Update syntax instead of insert syntax.
Hence, your Insert Command would look like "UPDATE myTable Set FirstField =
@FirstField, SeconedField = @SecondField" etc Typically you wouldn't want to
do this b/c rows with a rowstate of Added wouldn't exist in the DB so if the
insert command used Update instead of Insert, the update would fail and
nothing would happen. It's possible that an Update command needs the word
Update in it, but I really doubt it since you can use Stored procs for
updates and all the Adapter seems to care about is that it has an
Update/Insert command.

This would essentially fool the adapter but if that's the way your data will
always come in, it may be a legit way to do it.

HTH,

Bill

 

[Guest]

Thanks again for all your help. I have one more question regarding the
creating the command objects:

In creating the insert, update and delete commands I have to supply the
source columns that maps to the stored procedure paramaters. Right now I am
harding coding the columns, is there a better way to do this? If not, why
is this better that just manually passing the paramters?

And by the way I got the Insert working.

Thanks

Hi Eric:

William,

Thanks for the help. For some reason I cannot get the data adapter to
insert a new record, it does not error out.

The whole thing depends on if you have rows that are marked Added. If you
use the overloaded version of HasChanges, you can make this determination:

bool b = myDataSet.HasChanges(DataRowState.Added);

Now, if this is false, then whatever you use for your Insert command will
Never get fired. You can use a similar overload for GetChanges to get just
the rows that are added and call update against it, but remember, if the
above line evaluates to false, no inserts are going to happen with the
DataAdapter's update method:
myNewDataSet = myDataSet.GetChanges(DataRowState.Added);

If you have changes, you can call
myDataAdapter.Update(myNewDataSet.Tables[0]) and if your Insert command is
configured correctly, it will insert these rows.

you can do the same with Modified rows.

But let's say that you know for a fact that a row that was Added to your
dataset already exists in the DB but isn't in the dataset until you just
added it. The rowstate will be Added. It will use the Insert command to
fire the update. However, since it already exists you'll either get a key
violation or it will add it again if no violation exists. However, one
thing you WON'T Get is the existing value being updated.
The way DataAdapter's determine what action to take probably doesn't lend
itself well to the scenario you have set up.


I can manually insert a new

record with the data adapter. I am thinking about just manually inserting
and updating the database without using the data adapter and passing the
dataset. I was hoping I could get this to work because it would save
sometime having to pass all the values.

I think this is probably the best way to handle this and you don't need to
pass 'all of the values' Remember that you can filter the rowstate so you
pass only what was added/modified deleted etc. In this instance, rows that
are added may exist already so you can use the overloaded
getchanges(RowState.Added) to get those rows. Then you can have a loop that
first uses the value of the key field to check if it exists. If it does you
could branch down into an Update command, if it doesn't you could call the
Insert command.

I believe also (but I haven't done it) that if you knew that all of the rows
that were added locally already existed in the DB, you can probably use
write your Insert Command with Update syntax instead of insert syntax.
Hence, your Insert Command would look like "UPDATE myTable Set FirstField =
@FirstField, SeconedField = @SecondField" etc Typically you wouldn't want to
do this b/c rows with a rowstate of Added wouldn't exist in the DB so if the
insert command used Update instead of Insert, the update would fail and
nothing would happen. It's possible that an Update command needs the word
Update in it, but I really doubt it since you can use Stored procs for
updates and all the Adapter seems to care about is that it has an
Update/Insert command.

This would essentially fool the adapter but if that's the way your data will
always come in, it may be a legit way to do it.

HTH,

Bill

Thanks

the and
save block
with to
pass

yes.

It that
it

marked

as command
for out
my I
have when
not

it

is

need

to

values.

I submitted assign
it. in
and it
and probably
the have
to insert objects,
what subset
of I
need on
the one
of logic
such this
in but