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

I'm not familiar with this but perhaps res is a count of the number of records updated which will be one. What does the database hold after insert — Peter Smith
– Peter Smith, Commented Apr 8, 2021 at 7:51
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Apr 8, 2021 at 8:24

marc_s · Accepted Answer · 2021-04-08 08:25:11Z

3

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.

edited Apr 8, 2021 at 8:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 8, 2021 at 7:46

Jcl

28.4k5 gold badges66 silver badges98 bronze badges

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

Comments

Marc Gravell · Accepted Answer · 2021-04-08 07:53:52Z

1

Since your stored procedure also selects the output:

select @refres as M_SID

An easier way of doing this might be:

var id = con.ExecuteScalar<int>("Sp_InsertTestData", new {
    Name = Bindtestmaster.Name,
    gender = Bindtestmaster.Gender
}, sqltrans, 0, CommandType.StoredProcedure);

and forget DynamicParameters etc. You could also consider using the OUTPUT clause in the SQL, to simplify it:

ALTER PROCEDURE Sp_InsertTestData
    @Name varchar(50), @gender int
AS
BEGIN
    INSERT INTO Test_Master(Name, Gender)
    OUTPUT INSERTED.Id -- whatever the column is here
    VALUES (@Name, @gender);
END

answered Apr 8, 2021 at 7:53

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related