LINQ & SCOPE_IDENTITY

[James Page]

Hi all

I've a sproc:

ALTER PROCEDURE dbo.insertOrder
@orderDate As dateTime,
@customerId As UniqueIdentifier,
@subTotal As money,
@delivery As money,
@total As money,
@orderId int OUTPUT

AS
INSERT INTO dbo.orders (customerId, orderDate, subTotal, delivery,
total)
VALUES (@customerId, @orderDate, @subTotal, @delivery, @total)
SELECT SCOPE_IDENTITY() AS orderId

I can access this via linq in the usual way but in the helper class i've
created i want to insert the above records and then retrieve the id of that
inserted record row (orderId) for use later on in the class.
The linq I have is:

db.Insertorder(orderDate, UserId, subTotal, delivery, total)
but this returns an error (it wants a value for orderId).
The required parameters (orderDate etc) have been set elsewhere.

What am I missing here. My objective is to insert a new record, retrieve the
orderId, store the orderId in a public variable and reuse it later on in the
class.

Any pointers?

VB.NET ASP NET 3.5 SQL 2008

 

[bruce barker]

you have several mistakes:

1) you made @orderid a required parameter to the proc, so you need to
pass it when you call the proc. you also need to make it an output
parameter.

2) you never set @orderid to a value, it its completely useless.

3) you return the identity value as a result row, so you would need to
do a query and read the resultset to get the value.


-- bruce (sqlwork.com)

 

[James Page]

Thanks Bruce.

Thought I'd set the @order as output

I'm but I'm still not sure how to return via linq the id from the newly
inserted row.

Do I remove the parameter (orderId) from the sproc altogether?
If I do - how do I retrieve the new row id from linq and set a variable to
that returned value.

I've trawled google but can't find an appropriate example for LINQ!

Thanks

 

[Anthony Jones]

Thanks Bruce.

Thought I'd set the @order as output

I'm but I'm still not sure how to return via linq the id from the newly
inserted row.

Do I remove the parameter (orderId) from the sproc altogether?
If I do - how do I retrieve the new row id from linq and set a variable
to
that returned value.

I've trawled google but can't find an appropriate example for LINQ!

Do you have a reason why you have an SP doing this instead of letting the EF
do it?

 

[James Page]

Anthony

Old habits I suppose - I wanted to seperate out the data and code. However I
think I've found the solution:

Sproc =

ALTER PROCEDURE dbo.insertOrder
@orderDate dateTime,
@customerId UniqueIdentifier,
@subTotal money,
@delivery money,
@total money

AS
INSERT INTO dbo.orders (customerId, orderDate, subTotal, delivery,
total)
VALUES (@customerId, @orderDate, @subTotal, @delivery, @total)

select Id =SCOPE_IDENTITY()

linq =

orderDate = Now()
Dim var1 = From d In db.Insertorder(orderDate, UserId, subTotal,
delivery, total) Select d.Id
For Each var1Result In var1
orderId = var1Result
Next

Its a bit clunky but it does work. However I'm up for you to show me
alternatives.
(still finding my feet with linq!!)