Well, assuming you are using SQL Server, I would use a stored procedure with
parameters (if you are just getting started with ADO.Net, a good book is
invaluable, it's a vast topic). Use your DataAdapterConfigWizard to create
most of the stored procedures and the parameter code for you. You might use
Bill Vaughn's article to get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/commandbuilder.asp
Essentially what you will do is:
1. Do the Insert on table A
2. At the time of the Insert, retrieve the @@Identity value. This is the
"ReturnValue" in your parameters collection.
3. The relevant point here is that your table A Insert sproc will have
returned the Identity of the new Insert to table A, and so now your
datatable will contain the NEW Identity value. Use that value to do the
Insert on table B.
Here is a somewhat lengthy example that I have written recently. I've taken
out most of the error handling, checks for other events to occur, and just
used one field. I am creating a new Contact, then adding that the Referrals
table with the ContactID as a foreign key. The ContactSelectCommand pulls a
schema for the Contacts table. The ContactInsertCommand and
ReferralInsertCommand are created by the DACW, then tweaked. I'm showing
only the ContactInsertCommand and the ins_Contact sproc, but the Referrals
(table "B") code/sproc are identical in structure.
Private Sub btnAdd_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
AddNewContact()
AddNewReferral()
End sub
Private Sub AddNewContact()
Dim strSQLServer As New SqlConnection(strConn)
Dim da As New SqlDataAdapter
da.SelectCommand = ContactSelectCommand()
da.FillSchema(ds, SchemaType.Source, "dtContactInfo")
da.Fill(ds)
Dim drNew As DataRow
drNew = ds.Tables("dtContactInfo").NewRow
If txtFirstName.Text <> "" Then
drNew("FirstName") = txtFirstName.Text
End If
'the rest of the contact info will follow
...........
ds.Tables("dtContactInfo").Rows.Add(drNew)
'this command calls ANOTHER stored proc to do the update
da.InsertCommand = ContactInsertCommand()
'using FirstReturnedRecord so we will have the newly returned ContactID
'to Insert into our Referral source
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
da.Update(ds, "dtContactInfo")
strSQLServer.Close()
End Sub
****************************************************
'now we can Insert to "table B" as we have the returned ContactID
Private Sub AddNewReferral()
Dim strSQLServer As New SqlConnection(strConn)
Dim da As New SqlDataAdapter
da.SelectCommand = ReferralSelectCommand()
da.FillSchema(ds, SchemaType.Source, "dtRef")
da.Fill(ds)
Dim drNew As DataRow
drNew = ds.Tables("dtRef").NewRow
If CInt(ds.Tables("dtContactInfo").Rows(0)("ContactID")) > 0 Then
drNew("ContactID") = CInt(ds.Tables("dtContactInfo").Rows(0)("ContactID"))
End If
'here we would go ahead and add all the other data to the "table B"
...........
ds.Tables("dtRef").Rows.Add(drNew)
da.InsertCommand = ReferralInsertCommand()
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
da.Update(ds, "dtRef")
End Sub
****************************************************
Private Function ContactInsertCommand() As SqlCommand
Dim strSQLServer As New SqlConnection(strConn)
Dim cmd As New SqlCommand("ins_Contact", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As SqlParameterCollection = cmd.Parameters
'setup Parameters collection. All are input parameters
pc.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
pc.Add(New System.Data.SqlClient.SqlParameter("
@Firstname",
System.Data.SqlDbType.VarChar, 25, "FirstName"))
'... other contact fields
Return cmd
End Function
****************************************************
'now the Contact Insert sproc (the Referral Insert sproc is similar, just
diff fields and params)
ALTER PROCEDURE dbo.ins_Contact
(
....
@Firstname varchar(25),
'all the other contact params
.....
)
AS
SET NOCOUNT OFF;
INSERT INTO Contacts(FirstName, ... the rest of the columns)
VALUES (
@Firstname, ... the rest of the params);
SELECT ContactID, FirstName, ... the rest of the columns
FROM Contacts WHERE (ContactID = @@IDENTITY)
