CODEWITHSUNDEEP
c#dapper
blue piranha
blue piranha

Reputation: 3876

Getting stored procedure returned value instead of row affected

I have a stored procedure which does an insert and returns (for e.g. StudentID) of the last row inserted.

 create procedure insertStudent
 @...
 insert into ... values...
 set @StudentId = scope_identity();
 select StudentId from ... where StudentId = @StudentId

In the code, I have

var sql = ...;
using(var connection = new SqlConnection(connectionString)
{
  var parameters = new DynamicParameters();
  parameters.Add ...;
  ...
  int studentId = connection.Execute(sql, parameters, commandType : CommandType.StoredProcedure);
  ...
}

connection.Execute returns the number of rows affected.

How can I get the StudentId which the stored procedure is returning?

Upvotes: 3

Views: 1621

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131641

Use Query, not Execute. Execute is meant for queries that don't return values.

The docs in Dapper's repository show how to call a stored procedure that returns results :

var user = cnn.Query<User>("spGetUser", new {Id = 1}, 
    commandType: CommandType.StoredProcedure).SingleOrDefault();

You can simplify your stored procedure too. INSERT, DELETE and UPDATE have an OUTPUT clause that can be used to return the original and replaced values by querying the inserted and deleted virtual tables :

 insert into ... 
 OUTPUT inserted.StudentID
 values...

Your code could be simplified to :

  int studentId = connection.Query<int>("insertStudent", parameters, commandType : CommandType.StoredProcedure)
                            .SingleOrDefault();

Upvotes: 3

Related Questions