> > The Microsoft recommendation is to autogenerate a -1 number
> with a -1
> > increment . (search on Autoincrement identity) . When you pass the
> > data do NOT pass the indentity let the DB generate a new identiy
> > during inserts. The insert shoucl do a select at the end and
> > re-populate the record for any changes and the Constraints
> will update
> > all foreiign keys. Unlike DB's you can easily change autoincrement
> > identity collumsn in datasets.
>
> I can see how that might work for inserting rows into a
> single table, but I don't understand how it addresses the
> poster's original quetion, which involved adding a *new*
> parent row, and a *related* child row. Since the DB will
> invent its own identity column, how on earth do you indicate
> that the newly-added child should be related to the newly added child.
I've done this, and if you have set up a ForeignKeyConstraint (FKC) between
the two tables, and you update the PK field in the dataset during the insert
(I used an output param in the INSERT SQLCommand mapped against the PK to do
that), then the DataTable value is updated, then any FKCs are used to update
the related child records
Then the child DataTable is updated (using the *correct* FK values)
> Is the normal technique to do several round trips to the DB?
> (I.e. update the parent, retrieve the new key, update the
> DataSet to reflect the chosen key, then update the child
> table.) Is that workable?
In the case where you are not sending both datatables to the DB using XML,
it's mainly done for you
In that case you need to call the Update method for both DataTables, so you
are already doing the round trips, and including an out param in the SP and
mapping that to the PK, does the new key retrieval, and DataSet updates
automatically
> Also, what does the SELECT statement that retrieves the key
> that the DB chose look like in practice? If the DB has
> invented a new primary key, how on earth do I tell it which
> record I'm looking for? Don't I need to know what the new
> primary key was before I can ask for the record? Or are you
> using the magic @@IDENTITY value? (And how are you pushing
> it back to the DataSet? Manually? Or is there a way of
> automating this with a
> DataAdapter?)
First define the DataSet:
Dim ds As New DataSet()
Dim dtParent As New DataTable("Parent")
With dtParent.Columns
Dim dcPK(0) As DataColumn
dcPK(0) = .Add("parentid",
System.Type.GetType("System.Int32"))
dtLicence.PrimaryKey = dcPK
.Add("Name", System.Type.GetType("System.String")).MaxLength
= 60
End With
Dim dtChild As New DataTable("Child")
With dtChild.Columns
.Add("childid", System.Type.GetType("System.Int32"))
.Add("parentid", System.Type.GetType("System.Int32"))
.Add("childname",
System.Type.GetType("System.String")).MaxLength = 50
End With
'add the tables to the dataset
With ds.Tables
.Add(dtParent)
.Add(dtChild)
End With
'set up foreign key constraints
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("FK",
dtParent.Columns("parentid"), _
dtChild.Columns("childid"))
fk.UpdateRule = Rule.Cascade
fk.AcceptRejectRule = AcceptRejectRule.None
dtSchools.Constraints.Add(fk)
fk = Nothing
'set up relationships
ds.Relations.Add("Parent", dtParent.Columns("parentid"), _
dtChild.Columns("childid"), False)
then populate the tables using negative values for the parent table PK, and
use the same values for the FK in the child table, etc (as you would
normally)
Then to update...
Public Sub Update(ByVal theDS As DataSet)
Dim conn As New SqlConnection(conString)
'add parent records
'(parent ID field currently has negative numbers in it )
Dim cmdInsert As New SqlCommand("spCreateParent", conn)
cmdInsert.CommandType = CommandType.StoredProcedure
With cmdInsert.Parameters
.Add("@ParentID", SqlDbType.Int, 4, "parentid").Direction =
ParameterDirection.Output
.Add("@Name", SqlDbType.VarChar, 10, "name")
'.......
End With
Dim cmdInsert As New SqlCommand("spCreateChild", conn)
cmdInsert.CommandType = CommandType.StoredProcedure
With cmdInsert.Parameters
.Add("@ChildID", SqlDbType.Int, 4, "childid").Direction =
ParameterDirection.Output
.Add("@ParentID", SqlDbType.Int, 4, "parentid")
.Add("@Name", SqlDbType.VarChar, 50, "name")
End With
Dim da As New SqlDataAdapter()
da.InsertCommand = cmdInsertLicence
da.Update(theDS.Tables("Parent"))
da.Dispose()
da = Nothing
da = New SqlDataAdapter()
da.InsertCommand = cmdInsertSchool
da.Update(theDS.Tables("Child"))
da.Dispose()
da = Nothing
End Sub
The SP spCreateParent would look something like
CREATE PROCEDURE spCreateParent
(
@parentID INT OUTPUT,
@name VARCHAR(10),
.....
)
AS
INSERT INTO
ParentTable
(
name,
.......
)
VALUES
(
@Name,
........
)
SET @parentID = @@IDENTITY
It's probably not the best way of doing it (I was flailing a bit in ADO.NET
docs whilst writing that code <g>), but it does the job. I'll revisit it if
it causes any problems
HTH
Merak
You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced
DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
