Getting a stored procedure return value from an entity insert
(Originally coded in VS2008 using c#, .net framework 3.5, SQL Server 2005 back-end. Compatibility with VS2005/.net 2.0 unknown)
In this article, we’re going to be using an entityDataSource tied to a detailsView to insert a new “person” entity into our database. When the new entity is inserted, we want to use the new server-generated PK (identity) on our form, so we’ll be sending it back from the stored proc.
First things first, let’s look at our sample entity. This is a person in our Bus Line’s passenger database:
Simple person entity
To insert a new entity from our detailsView, we want to map an Insert stored proc, which looks like this:
CREATE PROCEDURE SP_BUSLINE_PERSON_INSERT @LAST_NAME varchar(25), @FIRST_NAME varchar(25), @DISPLAY_NAME varchar(65) AS BEGIN DECLARE @NEW_IDENTITY int; INSERT INTO BUSLINE_PERSONNEL(LAST_NAME, FIRST_NAME, DISPLAY_NAME) VALUES (@LAST_NAME, @FIRST_NAME, @DISPLAY_NAME) SET @NEW_IDENTITY = @@identity; SELECT @NEW_IDENTITY "PERSON_ID" END
Note that we’re not setting an “OUTPUT” parameter, but rather SELECTing a value.
Now that our SP is created, we need to map the insert function on our entity:
Insert Function Mapping for our Person entity
So, now that our insert is all set up (we’re going to ignore the fact that you’re currently required to map stored procs for all functions if you map for one), let’s take a look at the entity data source that feeds our detailsView:
<asp:EntityDataSourceID="entPersonSource" runat="server"
ConnectionString="name=BusLineEntities"
DefaultContainerName="BusLineEntities"
EntitySetName="People" EnableInsert="True"
OnInserted="eps_Inserted"
Where="it.Id = @PERSON_ID)">
<WhereParameters>
<asp:ControlParameter ControlID="txtPerson_hidden"
Type="Int32" Name="PERSON_ID" />
</WhereParameters>
</asp:EntityDataSource>
(Note you could have also set AutoGenerateWhereClause=”True” and renamed your whereParameter to “Id”)
For the record, we’re using a hiddenField as our ParameterSource because the user is typing into an autoComplete textbox that searches our People entitySet. If the name is found, the user selects it and can edit the details. If the name isn’t found, the name is put on our Details View for the user and they can choose to insert the new record. Obviously, there won’t be a person_id for the datasource to bind to before the entity is inserted, hence our needing the return value to databind our detailsView after insert.
So now let’s have a look at this detailsView (simplified):
<asp:DetailsView ID="dvPerson" runat="server"
DataSourceID="entPersonSource" DataKeyNames="Id">
<Fields>
<asp:TemplateField HeaderText="Display Name: ">
<InsertItemTemplate>
<asp:TextBox ID="txtDName" runat="server" Text='<%# Bind("DisplayName") %>' />
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDName" runat="server" Text='<%# Eval("DisplayName") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name: ">
<InsertItemTemplate>
<asp:TextBox ID="txtLName" runat="server" Text='<%# Bind("LastName") %>' />
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLName" runat="server" Text='<%# Eval("LastName") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="First Name: ">
<InsertItemTemplate>
<asp:TextBox ID="txtFName" runat="server" Text='<%# Bind("FirstName") %>' />
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="lblFName" runat="server" Text='<%# Eval("FirstName") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<InsertItemTemplate>
<asp:ImageButton ID="btnInsert" CommandName="Insert"
runat="server" ImageUrl="add.png" />
</InsertItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="btnEdit" CommandName="Edit"
CommandArgument='<%# Eval("Id") %>' runat="server" ImageUrl="edit.png" />
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
Now, since we’re supposed to be binding the detailsView to the entityDataSource where person_id = the person_id in the hidden field, the details view will disappear after an insert, which is why we put the following in our code-behind:
protected void eps_Inserted(object sender, EntityDataSourceChangedEventArgs e) { Person p = (Person)e.Entity; txtPerson_hidden.Value = p.Id.ToString(); }
You can now insert an entity using a stored proc and immediately use the return value. And notice that we didn’t have to go to the code-behind for our insert command on the detailsView (the same is true for update and delete, you just have to set your databindings correctly in the markup).
Leave your response!