insert from sql stored procedure via dapper returns -1 (not "working")

Question

I have a store procedure that works "manually" but when it's executed via dapper it returns a "-1" and doesn't error.

The sproc:

  ... PROCEDURE [ts].[uspAddClientForm](
   @ClientId int,
   @SystemFormId bigint,
   @Year int,
   @Name nvarchar(255),
   @CreatedBy nvarchar(2000),
   @CreateDate datetime = NULL,
   @Active bit = 1,
   @ClientFormId bigint OUTPUT
  )
  AS
  BEGIN
    SET NOCOUNT ON  
    INSERT INTO [ts].[ClientForm] ([ClientId], [SystemFormId], [Year] 
    ,[Name] ,[CreatedBy] ,[CreateDate], [Active]) 
    VALUES(@ClientId, @SystemFormId, @Year, @Name, @CreatedBy, GETDATE(),    @Active)

    SET @ClientFormId = SCOPE_IDENTITY() 
  END

The dapper related code:

   var formParams = new DynamicParameters();
       formParams.Add("@ClientId", clientForm.ClientId);
       formParams.Add("@SystemFormId", clientForm.SystemFormId);
       formParams.Add("@Year", clientForm.Year);
       formParams.Add("@Name", clientForm.Name);
       formParams.Add("@CreatedBy", clientForm.Username);
       formParams.Add("@CreateDate", DateTime.Now);
       formParams.Add("@Active", 1);
       formParams.Add("@ClientFormId", DbType.Int64, direction: ParameterDirection.Output);

       var result =  dbConnection.Execute("ts.uspAddClientForm", formParams, commandType: CommandType.StoredProcedure);

   // retrieve output value
   var clientId = formParams.Get<long>("@ClientFormId");

I also tried, unsuccessfully, removing the OUTPUT parameter using ExecuteScalar and Query returning the SCOPE_IDENTITY() in a SELECT. I'd prefer to use the OUTPUT way but I'm open to anything that works and generally subscribes to best practices.

Nayan Patel · Accepted Answer · 2017-03-21 09:55:26Z

2

Following Line not properly so modify and try it.

     formParams.Add("@ClientFormId", DbType.Int64, direction: ParameterDirection.Output);

To,

     formParams.Add("@ClientFormId","",dbType: DbType.Int64, direction: ParameterDirection.Output);

answered Mar 21, 2017 at 9:55

Nayan Patel

1547 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Razvan Dumitru · Accepted Answer · 2017-03-20 15:30:14Z

0

You can try to select identity as something:

 SELECT SCOPE_IDENTITY() AS ClientFormId

And in your dapper code: // assuming that your identity is an int .ExecuteScalar<int>("ts.uspAddClientForm", ...)

And also remove the output parameter, as you won't need that one.

answered Mar 20, 2017 at 15:30

Razvan Dumitru

12.5k5 gold badges38 silver badges55 bronze badges

1 Comment

Will Lopez Over a year ago

Thanks, that did it!

Collectives™ on Stack Overflow

insert from sql stored procedure via dapper returns -1 (not "working")

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related