DAAB .Net 2 - @@identity

Topics: Data Access Application Block
Mar 27, 2007 at 5:41 PM
I am sure retrieving an identity value back from a stored proc is a common issue but the search is returning nothing.

Can someone point me in the right direcion here?
Mar 27, 2007 at 5:50 PM
Figured out a way...

Given a contrived proc like:
CREATE PROCEDURE InsertCompany(
@ID int = 0,
@CompanyID int = 0)
as
insert into company(companyid) values (@CompanyID)
select id = @@identity /* scope_identity() if SQL Server supports it */
return @@error

Code like the following works:
DbCommand cmd = db.GetStoredProcCommand("InsertCompany");
db.AddInParameter(cmd, "ID", DbType.Int32, _id);
db.AddInParameter(cmd, "CompanyID", DbType.Int32, _companyID);
_id = ConvertVal.ToInt32(db.ExecuteScalar(cmd));

Mar 27, 2007 at 10:17 PM
I have a tutorial here that may offer additional information:

Enterprise Library 2.0 DAAB - Retrieving PK and Timestamp When Inserting New Record

At the end of the tutorial there are additional posts that I have written about SCOPE_IDENTITY() in general using ADO.NET, etc.

Regards,

Dave

___________________________

David Hayden
Microsoft MVP C#