Re: Updating Dataset
From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 04/19/04
- Next message: Mervin Williams: "DataGrid does not display?"
- Previous message: William Ryan eMVP: "Re: why use the data command parameter collection"
- In reply to: eric: "Re: Updating Dataset"
- Next in thread: eric: "Re: Updating Dataset"
- Reply: eric: "Re: Updating Dataset"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 15:53:27 -0400
Hi Eric, and thanks for the compliment.
<eric> wrote in message news:umLAUJkJEHA.2580@TK2MSFTNGP12.phx.gbl...
> 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
>
> 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" <dotnetguru@comcast.nospam.net> wrote in message
> news:ul$buejJEHA.2556@TK2MSFTNGP11.phx.gbl...
> >
> > <eric> wrote in message news:#jGXlBjJEHA.3688@TK2MSFTNGP10.phx.gbl...
> > > 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
> >
> >
>
>
>
- Next message: Mervin Williams: "DataGrid does not display?"
- Previous message: William Ryan eMVP: "Re: why use the data command parameter collection"
- In reply to: eric: "Re: Updating Dataset"
- Next in thread: eric: "Re: Updating Dataset"
- Reply: eric: "Re: Updating Dataset"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
