The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

slindee (28) [Avatar] Offline
#1
How would Linq be coded to return the Identity value generated in this sp:
ALTER Procedure [dbo].[InsertPerson]
@LastName varchar (35),
@FirstName varchar (25),
@PersonID int OUTPUT
AS
INSERT INTO tblPerson
(LastName,
FirstName)
VALUES @LastName,
@FirstName)
SET @PersonID = @@IDENTITY
-----------------------------------------------------------------
For starters:
Dim dc As New dcPersonDataContext
Dim insert As New tblPerson
With insert
.LastName = LastName
.FirstName = FirstName
.PersonID = ?
End With
dc.tblPersons.InsertOnSubmit(insert)
dc.SubmitChanges(Data.Linq.ConflictMode.FailOnFirstConflict)
Lee Dumond (29) [Avatar] Offline
#2
Re: How to return Identity value in Insert
After the call to SubmitChanges, you still have the reference to the inserted instance available. Therefore, you can access its ID after it has been inserted.

For example:

Public Function InsertPerson(ByVal firstName As String, ByVal lastName As String) As Integer

Dim insert As New tblPerson()
insert.FirstName = firstName
insert.Lastname = lastName

Dim dc As New TBHDataContext
dc.tblPersons.InsertOnSubmit(insert)
dc.SubmitChanges()

Return insert.PersonID

End Function
Martillo (4) [Avatar] Offline
#3
Re: How to return Identity value in Insert
Lee,

Very cool!

Also, see this ScottGu post for another approach, especially if you want to call the stored procedure and retrieve the output parameter:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx