CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresdapper
Will Lopez
Will Lopez

Reputation: 2119

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

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.

Upvotes: 2

Views: 2801

Answers (2)

Nayan Patel
Nayan Patel

Reputation: 154

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);

Upvotes: 2

Razvan Dumitru
Razvan Dumitru

Reputation: 12452

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.

Upvotes: 0

Related Questions