Update Question

  • Thread starter Thread starter ion
  • Start date Start date
I

ion

In my application I use typed datasets as my data container classes (data
persistence). My db structure has many table relations (mostly one to one).
I have main table that maps to 4 different tables using an ID which is auto
incremental. So, when I persist this info while the user inputs it, I do
not know these values until I update the db (use stored procedures and
return SCOPE_IDENTITY() ). I update the db asynchronously at the end of
the user input and the user submits the data to be saved. So, what I do is
break up the update in order and return each id and save to the main typed
dataset and then that will be the final update. My question is this the
best way to do this? I know I can let ADO.Net automatically update the
tables with data relation however should I trust this? It seems to me that
I have to update backwards by savings the outer tables before saving the
main table. Any suggestions on handling auto incremental values when
updating values on the client?

Thanks
 
You have a couple of choices on transactions.

1. Control from ADO.NET
2. Control from SQL Server through a stored procedure

In most cases, with Identity, I prefer stacking up the INSERT in a stored
procedure and allow SQL Server to handle the transaction. There is a
learning curve here, but once you are used to it, you get the following
benefits:

1. Easier to maintain
2. Better performance due to lower marshalling across process boundaries

When you handle everything in your .NET code, you end up bouncing back and
forth. While you can decrease some of the perf hit by running multiple
commands under one connection, you still have a bit more weight.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************************************************
Think outside the box!
***************************************************************
 
Thanks for the post. Could be more specific on what you mean stacking the
INSERT in a stored procedure and handling it on the SQL Server?

Thanks
 
My question is this the
best way to do this? I know I can let ADO.Net automatically update the
tables with data relation however should I trust this? It seems to me that
I have to update backwards by savings the outer tables before saving the
main table.
Click to expand...

No matter how you do it, you'll have to do the insert on the parent table
(generating the identity PK) before you can insert the FK on the child
table.

If you are updating your original dataset directly, ado.net seems to be
pretty good about pushing new PK's down as FK's in related tables in the
typed dataset. Here is a common way to mark an identity PK in the xsd file:

<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true"
msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" type="xs:int" />

If you are trying to update diffgram datasets (and anyone that is sending
datasets through web services, or is transactionally updating WinForm-bound
controls, probably should be), derived from your dataset via GetChanges and
Merge'ed back later, you are in for some real pain because it takes some
serious workarounds to get this working. I'm crafting a general solution,
and I'll probably post it here or CodeProject in a few weeks (these are
optimistic programmer weeks -- not to be confused with mythical man weeks).

If the way you are doing it is working, then I don't think there's a really
compelling reason to change, though.

Brad Williams
 
Back
Top