Using SCOPE_IDENTITY() with ObjectDataSource

Microsoft’s introduction of the ObjectDatasource in ASP.NET 2.0 was a great addition. It saves developers alot of time once they are familar with the controls. The problem is that what seemed like simple tasks when coding everything by hand sometimes became more difficult to figure out. For example, how do I return the identity seed from an Insert command? Let’s say that your basic stored procedure looked like this

ALTER PROCEDURE [dbo].[Insert] @Name varchar(255) AS INSERT INTO Table (Name) VALUES (@Name) SELECT SCOPE_IDENTITY()

That stored procedure is perfectly fine, but when you use an objectdatasource, you quickly realize that you can’t really return the indentity seed correctly in that format. The object datasource typically looks something like this. (I’ve excluded all the delete, select, etc properties to focus on the inserting. <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" InsertMethod="Insert"> <InsertParameters> <asp:Parameter Name="LineOfBusiness" Type="String" /> <asp:Parameter Direction="Output" Name="LOBID" Type="Int" /> </InsertParameters> </asp:ObjectDataSource>

The ObjectDataSource parameter for the output can’t pick up the SELECT SCOPE_IDENTITY(). The stored procedure needs to explicitly define the output parameter for the ObjectDataSource control to pick it up correctly. If you update the stored procedure to below it would work correctly ALTER PROCEDURE [dbo].[Insert] @Name varchar(255), @ItemId OUTPUT int AS INSERT INTO Table (Name) VALUES (@Name) SET @ItemID = SELECT ItemId FROM Table WHERE ItemId=SCOPE_IDENTITY()