void returns?

[ssp]

Hi C#ers,

I have a stored proc. which is as follows. Supposedly returning the ID
(LocationID) of the row I've just inserted (scope identity):
=========================================
CREATE PROCEDURE [dbo].[LocationInsert]
@LocationName varchar(32),
@LocationDescription varchar(256),
@LocationID int OUTPUT,
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Location] (
[LocationName],
[LocationDescription],
) VALUES (
@LocationName,
@LocationDescription,
)

SET @LocationID = Scope_Identity()
END
========================================

I then have the following code to insert the data from my code:

========================================
public void Insert(LocationEntity locationEntity)
{
DBCommandWrapper dbCommandWrapper =
this.Db.GetStoredProcCommandWrapper("LocationInsert");

dbCommandWrapper.AddInParameter("@LocationName", DbType.AnsiString,
locationEntity.Name);
dbCommandWrapper.AddInParameter("@LocationDescription",
DbType.AnsiString, locationEntity.Description.Value);

this.ExecuteSPNonQuery(dbCommandWrapper);
locationEntity.LocationID =
(int)dbCommandWrapper.GetParameterValue("@LocationID");
}

========================================

My question:

The Insert function is labeled "void". Will it return the ID
(LocationID) of the row I've just inserted? Infact, will the stored
procedure itself return the LocationID that I need?

SSP

 

[Brendan Green]

I think you will need to add "@LocationID" as a parameter to
dbCommandWrapper, and set it's direction to "Output". Then you will be able
to read the value back after executing the SP.

 

[Bob Grommes]

ssp,

From the fact that Insert() is a void, you know that it will not return
anything ... not even if you wish it would ;-) Make it an int instead
of a void and return the value of the @LocationID parameter, if that's
what you want it to do.

In addition, the @LocationID parameter would need to be added as an
output parameter to the command object calling dbo.LocationInsert. I
recognize the MSFT Enterprise Library helper methods you're using but am
not familiar with them ... offhand I suppose there is an AddOutParameter
method for the DBCommandWrapper object that you need to call to add that
parameter.

--Bob

 

[ssp]

Some thing like this?

dbCommandWrapper.AddOutParameter("@LocationID", DbType.Int32, 4);

Thanks Brendan... it's working now.

 

[ssp]

Hi Bob,

Indeed I am using the MEL.

If you notice the Insert method, it takes an entity object as
parameter. So in this case I'll have to make sure it returns the
inserted entity with changed LocationID.

I've relabeled the Insert method as:

public LocationEntity Insert(LocationEntity locationEntity)
{
DBCommandWrapper dbCommandWrapper =
this.Db.GetStoredProcCommandWrapper("LocationInsert");

dbCommandWrapper.AddInParameter("@LocationName", DbType.AnsiString,
locationEntity.Name);
dbCommandWrapper.AddInParameter("@LocationDescription",
DbType.AnsiString, locationEntity.Description.Value);
dbCommandWrapper.AddOutParameter("@LocationID", DbType.Int32, 4);

this.ExecuteSPNonQuery(dbCommandWrapper);
locationEntity.LocationID =
(int)dbCommandWrapper.GetParameterValue("@LocationID");
}

This should do the trick... I hope

SSP