C# Web API using Dapper to insert data using a stored procedure - unable to get inserted record Id

Question

I'm new to Dapper - please help me. How can I get the inserted record value after a successful insert?

Stored procedure:

ALTER PROCEDURE Sp_InsertTestData
    @Name varchar(50),
    @gender int,
    @refres int OUTPUT
AS
BEGIN
    INSERT INTO Test_Master (Name, Gender) 
    VALUES (@Name, @gender);

    SELECT @refres = SCOPE_IDENTITY()
    SELECT @refres as M_SID
END

When I execute this stored procedure in SQL like this:

DECLARE @refres INT
EXEC Sp_InsertTestData 'test12',1,@refres

I'm getting an output showing the last inserted row's value.

But when this stored procedure is executed from C# code, every time I'm getting a value of 1:

using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("DatabaseConnection")))
{
    con.Open();

    SqlTransaction sqltrans = con.BeginTransaction();

    var param = new DynamicParameters();
    param.Add("@Name", Bindtestmaster.Name);
    param.Add("@gender", Bindtestmaster.Gender);
    param.Add("@refres");

    res = con.Execute("Sp_InsertTestData", param, sqltrans, 0, CommandType.StoredProcedure);
}

Answer 1

That's because you are getting the result of the stored procedure call, which tells you the number of rows inserted (which is 1).

You want to read the output parameter @refres (and add it to your DynamicParameters as an output parameter)

/* ... */
param.Add("@refres", dbType: DbType.Int32, direction: ParameterDirection.Output);
con.Execute("Sp_InsertTestData", param, sqltrans,0,CommandType.StoredProcedure);
var yourId = param.Get<int>("@refres");

Btw, on your stored procedure instead of:

select @refres=SCOPE_IDENTITY()

You might want to prefer this:

SET @refres = SCOPE_IDENTITY() AS INT

And I'm not sure what that last SELECT is there for

Or directly output the inserted ID (using the OUTPUT SQL clause on the INSERT) and then you could read the result, and not have an output parameter at all.