ExamGuru IT/.Net Guru : Parent / Child Dataset Update. Is there an easier Way? on ASPFriends.com 'aspngdata' list

.NETGURU

Parent / Child Dataset Update. Is there an easier Way?

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngdata' list.

Trevor Pinkney

Hi,

This question boils down to, "Is ado.net smart enough to use scope identity
and automatically write code to handle parent/child updates?"

that said, I have a screen where Parent rows can be dynamically added to a
dataset, then after the parent row is added child's can be attached to that
row. (before saving to the db)

Parent Row
    -Child
    -Child
    -Child

I set the ParentID column
    AutoIncrement = True,
    AutoIncrementSeed to -1
    AutoIncrementStep to -1. (this is recommended by m$)

I set up my relations etc. etc. Then parent rows are added and child rows
added... no problems yet...

Anyway when I do my update, the sqldataadapter detects the added rows and
called the insertcommand on my new Parent Rows... In the dataset,
parentRowID is set to -1... BUT... the dataadapter is smart enough to let
the autoidentity increment this column in the database... simply put, say
ParentRowID increment in the database is at 10, My new row gets inserted
with a parentRowID value of 11. NOW - the child row gets inserted and it
doesn't have a value of 11... (even though it references the parentRowID
column in the dataset) I get a foreign key constraint error. AND - I
understand why... It is because I don't have it set up where the NewRowID
inserted into the database is returned to the dataset.

SO - My question is... Is ADO.NET smart enough to handle this
situation... OR... do i have to manually use SCOPE_IDENTITY to get the
newID and then manually update each of the parent's child columns based on
that newID... Or not even use ADO.NET and create an XML Insert/Update
stored proc?

Just want to make sure I'm not missing anything.

thanks,

-Trevor

Reply to this message...

Andy Smith

yes, using some method, you have to get the updated indentity back to =
the parent row in the dataset. however, if you have the relation set to =
cascade updates... then the child rows will be automagically updated =
with the new value.

one way to do this is to hook into the adapter's RowUpdated event to get =
the new identity, like thus:

currentTable =3D myDataSet.Tables("ParentTable")
da =3D New SqlDataAdapter("Select * From " & =
currentTableName.ToString(), myConnection)
AddHandler da.RowUpdated, AddressOf MyRowUpdatedEventHandler
cb =3D New SqlCommandBuilder(da)
da.Update(currentTable)

Private Sub MyRowUpdatedEventHandler( _
ByVal sender As Object, _
ByVal e As SqlRowUpdatedEventArgs _
)
Dim da As SqlDataAdapter =3D CType(sender, SqlDataAdapter)
Dim cn As SqlConnection =3D da.SelectCommand.Connection
Dim cmd As New SqlCommand("Select @@Identity", cn)
Dim receivedIdentity As Object
Dim newIdentity As Int32
Try
receivedIdentity =3D cmd.ExecuteScalar()
Catch ex As Exception
Throw New InvalidOperationException("Could Not Retrieve =
Autonumber", ex)
End Try

Try
newIdentity =3D Int32.Parse(receivedIdentity.ToString())
Catch ex As Exception
Throw New InvalidCastException("Could Not Create an Int32 =
from autonumber '" & receivedIdentity.ToString() & "'", ex)
End Try

Try
e.Row.Item(e.Row.Table.PrimaryKey(0).ColumnName) =3D =
newIdentity
e.Row.AcceptChanges()
Catch ex As Exception
' Do something
End Try
End Sub

now after the Update call to the ParentTable, the child table will have =
been updated with the real parent identities.

__
Andy Smith
Keyboard Jockey #3a7-2.78.1

[Original message clipped]

Reply to this message...

Mark Feinholz

If the update stored procedure's parameter that is mapped to the key
column is an output or input/output parameter, then when you do the
update, the DataAdapter will automatically move the returned value back
to the table, and the cascade will get it down to the children - so you
don't have to write any code. Just make your update stored proc a bit
smarter.

-----Original Message-----
From: Andy Smith [mailto:Click here to reveal e-mail address]
Sent: Friday, June 28, 2002 8:47 AM
To: aspngdata
Subject: [aspngdata] RE: Parent / Child Dataset Update. Is there an
easier Way?

yes, using some method, you have to get the updated indentity back to
the parent row in the dataset. however, if you have the relation set to
cascade updates... then the child rows will be automagically updated
with the new value.

one way to do this is to hook into the adapter's RowUpdated event to get
the new identity, like thus:

currentTable = myDataSet.Tables("ParentTable")
da = New SqlDataAdapter("Select * From " & currentTableName.ToString(),
myConnection)
AddHandler da.RowUpdated, AddressOf MyRowUpdatedEventHandler
cb = New SqlCommandBuilder(da)
da.Update(currentTable)

Private Sub MyRowUpdatedEventHandler( _
ByVal sender As Object, _
ByVal e As SqlRowUpdatedEventArgs _
)
Dim da As SqlDataAdapter = CType(sender, SqlDataAdapter)
Dim cn As SqlConnection = da.SelectCommand.Connection
Dim cmd As New SqlCommand("Select @@Identity", cn)
Dim receivedIdentity As Object
Dim newIdentity As Int32
Try
receivedIdentity = cmd.ExecuteScalar()
Catch ex As Exception
Throw New InvalidOperationException("Could Not Retrieve
Autonumber", ex)
End Try

Try
newIdentity = Int32.Parse(receivedIdentity.ToString())
Catch ex As Exception
Throw New InvalidCastException("Could Not Create an Int32
from autonumber '" & receivedIdentity.ToString() & "'", ex)
End Try

Try
e.Row.Item(e.Row.Table.PrimaryKey(0).ColumnName) =
newIdentity
e.Row.AcceptChanges()
Catch ex As Exception
' Do something
End Try
End Sub

now after the Update call to the ParentTable, the child table will have
been updated with the real parent identities.

__
Andy Smith
Keyboard Jockey #3a7-2.78.1

[Original message clipped]

| [aspngdata] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngdata.asp = JOIN/QUIT
| http://www.asplists.com/search = SEARCH Archives

Reply to this message...

	System.Data.Common.DataAdapter
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlCommandBuilder
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.Data.SqlClient.SqlRowUpdatedEventArgs
	System.Int32
	System.InvalidCastException
	System.InvalidOperationException

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification