Get inserted ID back from SqlConnection.Execute stored procedure

Question

I'm inserting data into my database like so:

using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Execute(storedProcedure, parameters, commandType: CommandType.StoredProcedure);
}

I've added select SCOPE_IDENTITY(); to my stored procedures so now the id is returned from the query. How can I access it?

If you're only inserting a single row, personally I would assign the value of `SCOPE_IDENTITY` to an output parameter, rather than selecting it. — Thom A, Aug 05 '20 at 13:19

score 1 · Accepted Answer · answered Aug 05 '20 at 13:19

This looks like Dapper, yes? So: instead of using Execute, use QuerySingle<T> for whatever T you are selecting - presumably int or long:

var id = conn.QuerySingle<int>(storedProcedure, parameters,
    commandType: CommandType.StoredProcedure);

As a side note: you may find it easier to use the OUTPUT clause during the INSERT.

score 0 · Answer 2 · answered Aug 05 '20 at 13:21

Use ExecuteScalar.

using (IDbConnection conn = new SqlConnection(connectionString))
{
   var command = conn.CreateCommand();
   command.CommandType = CommandType.StoredProcedure;
   command.CommandText = "MyProcedure";
   var id = Convert.ToInt32(command.ExecuteScalar());
}

Get inserted ID back from SqlConnection.Execute stored procedure

2 Answers2