EntLib 5.0 AddParameter Discussion

Topics: Data Access Application Block
Oct 22, 2010 at 11:22 AM

I am  having a  bit trouble in handling  the AddParameter

Say  I  have  a  storedProcedure

Alter PROCEDURE [dbo].[sprocAddressInsertUpdateSingleItem]

    @id int,
    @street nvarchar (50),
    @houseNumber nvarchar (20),
    @zipCode nvarchar (12),
    @city nvarchar (50),
    @country nvarchar (30),
    @addressType int,
    @contactPersonId int

    AS

    DECLARE @ReturnValue  int

    IF (@id IS NULL) -- New Item
    BEGIN
        INSERT INTO Address
        (
            Street,
            HouseNumber,
            ZipCode,
            City,
            Country,
            AddressType,
            ContactPersonId
        )
        VALUES
        (
            @street,
            @houseNumber,
            @zipCode,
            @city,
            @country,
            @addressType,
            @contactPersonId
        )

        SELECT @ReturnValue = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
        UPDATE Address SET
            Street = @street,
            HouseNumber = @houseNumber,
            ZipCode = @zipCode,
            City = @city,
            Country = @country,
            AddressType = @addressType,
            ContactPersonId = @contactPersonId
        WHERE
            Id = @id

        SELECT @ReturnValue = @id
       
    END

    IF (@@ERROR != 0)
    BEGIN
        RETURN -1
    END
    ELSE
    BEGIN
        RETURN @ReturnValue 
    END

 

I WANT  TO   RECEIVE  THE   RETURN VALUE OF   THE STORED   PRCEDURE

So   if I  pass

 

Database defaultDB = EnterpriseLibraryContainer.Current.GetInstance<Database>("Something");
 using (

           DbCommand sprocCmd =

           defaultDB.GetStoredProcCommand("sprocAddressInsertUpdateSingleItem",

                                                                     new object[] {

                                                                                      _address.Id,       //parameter1 --  Sample  Value  35

                                                                                    _address.Street,   //parameter2  --  Sample  value "Some   Street"

                                                                                   _address.HouseNumber,  //parameter3  --  Sample   value  "#123"

                                                                                   _address.ZipCode,  //parameter4   --Sample Value     "23333"

                                                                                   _address.City,         //parameter5    -- Sample Value    "Some City"       

                                                                                  _address.Country,   //parameter6   --  SampleValue  "XXXX"

                                                                                     _addressType,      //parameter7   --  SampleValue  1          

                                                                                    _address.ContactPersonId   //parameter8  --Sample   value 10

                                                                                   }))
            {
                //Inorder   to   get      the    return   value   of  the stored  procedure  i    used    AddParameter   method   (   not  quite sure how to   use  it)
                defaultDB.AddParameter(sprocCmd, "@ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, "@ReturnValue", DataRowVersion.Default, default(Int32));
                defaultDB.ExecuteNonQuery(sprocCmd);
                _id = Convert.ToInt32(defaultDB.GetParameterValue(sprocCmd, "@ReturnValue"));
            }

But  the     return  value i   received is   always   zero   (0)   I    suppose  to   35,since I    am  updating  my  record (record  is   verified)   . What  I   am   missing   out  or  how to  rewrite my   code ?

Oct 22, 2010 at 11:59 AM

I  got  the  result.

When I  rewrite  the code

           using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("sprocAddressInsertUpdateSingleItem"))
            {
                defaultDB.AddInParameter(sprocCmd, "@Id", DbType.Int32,_address.Id);
                defaultDB.AddInParameter(sprocCmd, "@Street", DbType.String,_address.Street);
                defaultDB.AddInParameter(sprocCmd, "@HouseNumber", DbType.String,_address.HouseNumber);
                defaultDB.AddInParameter(sprocCmd, "@ZipCode", DbType.String,_address.ZipCode);
                defaultDB.AddInParameter(sprocCmd, "@City", DbType.String,_address.City);
                defaultDB.AddInParameter(sprocCmd, "@Country", DbType.String,_address.Country);
                defaultDB.AddInParameter(sprocCmd, "@AddressType", DbType.Int32,_address.AddressType);
                defaultDB.AddInParameter(sprocCmd, "@ContactPersonId", DbType.Int32,_address.ContactPersonId);
                defaultDB.AddParameter(sprocCmd, "@ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, string.Empty, DataRowVersion.Current, DBNull.Value);
                defaultDB.ExecuteNonQuery(sprocCmd);
                _id = Convert.ToInt32(defaultDB.GetParameterValue(sprocCmd, "@ReturnValue"));
            }

It is  working  fine.But  still  I  don't  know what  is wrong   with my  previous  version   :).

Oct 22, 2010 at 2:22 PM

The ExecuteNonQuery method doesn't return the return value of your stored procedure, it returns the number of affected rows.  Note that this behavior is not something that EntLib does but it is what the raw ADO.NET does.  In your case, it probably returns zero because your stored procedure has the statement SET NOCOUNT ON.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com