DataSet Transactions Help

[Brian Scott]

I currently have a defined Datalayer in a C# application which is filled
with data on startup. All forms in the system bind the to this dataset data
source. The datatables within the dataset have various datarelations setup
and as most of the table keys are Identity fields I had to write stored
procedures on the server to return the new identity upon insert. This
required creating a new SqlCommand object that points at the stored
procedure and setting the Dataadpter insert command to this.

The issue is that because I had to write specific stored procedures due to
using Identity fields I now get an exception when I dont attach the
transaction to the other Dataadapter commands such as its Read, Delete and
Update commands. But these are never set and I thereby get a null reference
exception. I do not wish to create these other commands manually, only the
insert as I need the identity to come back out of the SQL Server upon
insert.

How do I implement Transactions on Dataset inserts where the datatable
contains an Identity field?

Thanks.

 

[Guest]

I wrote an article in MSDN Mag a few months ago on how to take a DataSet with
parent and children DataTables related through key fields. The key fields
have IDENTITY attributes, which makes the work a bit more involved, but ti
can still be done without using extra procs. Take a look at this article
(source code is included) and let me know if you have any questions.

http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/


// John Papa
// http://codebetter.com/blogs/john.papa

 

[Brian Scott]

Cool, I will check that out tomorrow when I get a chance.

Does this also apply to Win Forms Datagrid principles?

 

[Guest]

Yes, this still applies to WIndows Forms with the obvious code changes for
the presentation layer. The idea behind the code sample in the article is
that the user enters a series of inserts, updates and deletes on rows in the
Customers, Orders and Order Details tables. THe code handles the grabbing of
the new Order records' ID fields.

Let me know if you have any questions.


// John Papa
// http://codebetter.com/blogs/john.papa

 

[Brian Scott]

John,



I read your articles with interest. I wondered though, how do I set a
transaction to be used with both the parent / child dataadapters when the
only method I have set explicitly is the insert command? The only reason I
have coded the insert command was because the underlying database contains
an identity field for the primary key. I wish to use the existing
commandbuilder to perform the additional commands for me without
unnecessarily coding the update and delete commands, there are many fields
in the tables and it makes maintaining the software more difficult.




Brian Scott,
Mosaic Software Ltd.



TEL +44 (0)1355 272641
FAX +44 (0)1355 230006



Website: http://www.mosaicsoftware.co.uk