如果你使用DynamicParameters，它确实支持输入/输出参数(包括RETURN值)，但在这种情况下，更简单的选项是:

var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() as int)", new { Stuff = mystuff});

注意，在SQL Server(2005+)的最新版本中，你可以使用OUTPUT子句:

var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff])
OUTPUT INSERTED.Id
VALUES (@Stuff);", new { Stuff = mystuff});

不确定是不是因为我对SQL 2000工作，但我必须这样做才能让它工作。

string sql = "DECLARE @ID int; " +
             "INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); " +
             "SET @ID = SCOPE_IDENTITY(); " +
             "SELECT @ID";

var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();

你得到的InvalidCastException是由于SCOPE_IDENTITY是一个十进制(38,0)。

你可以把它转换成int类型，如下所示:

string sql = @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() AS INT)";

int id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();

KB:2019779，"当使用SCOPE_IDENTITY()和@@IDENTITY时，可能会收到不正确的值"， OUTPUT子句是最安全的机制:

string sql = @"
DECLARE @InsertedRows AS TABLE (Id int);
INSERT INTO [MyTable] ([Stuff]) OUTPUT Inserted.Id INTO @InsertedRows
VALUES (@Stuff);
SELECT Id FROM @InsertedRows";

var id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();

如果你使用Dapper。SimpleSave:

 //no safety checks
 public static int Create<T>(object param)
    {
        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            conn.Create<T>((T)param);
            return (int) (((T)param).GetType().GetProperties().Where(
                    x => x.CustomAttributes.Where(
                        y=>y.AttributeType.GetType() == typeof(Dapper.SimpleSave.PrimaryKeyAttribute).GetType()).Count()==1).First().GetValue(param));
        }
    }

一个迟来的答案，但这里有一个SCOPE_IDENTITY()答案的替代方案，我们最终使用:OUTPUT INSERTED

只返回插入对象的ID:

它允许你获得所插入行的全部或部分属性:

string insertUserSql = @"INSERT INTO dbo.[User](Username, Phone, Email)
                        OUTPUT INSERTED.[Id]
                        VALUES(@Username, @Phone, @Email);";

int newUserId = conn.QuerySingle<int>(
                                insertUserSql,
                                new
                                {
                                    Username = "lorem ipsum",
                                    Phone = "555-123",
                                    Email = "lorem ipsum"
                                },
                                tran);

返回带有ID的插入对象:

如果你想，你可以得到电话和电子邮件，甚至整个插入行:

string insertUserSql = @"INSERT INTO dbo.[User](Username, Phone, Email)
                        OUTPUT INSERTED.*
                        VALUES(@Username, @Phone, @Email);";

User newUser = conn.QuerySingle<User>(
                                insertUserSql,
                                new
                                {
                                    Username = "lorem ipsum",
                                    Phone = "555-123",
                                    Email = "lorem ipsum"
                                },
                                tran);

此外，您还可以使用它返回已删除或已更新行的数据。只是如果你使用触发器要小心，因为(从前面提到的链接):

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed. For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

更多信息见文档:link

有一个很棒的库可以让你的生活更轻松。在这之后，你可以这样写:

public int Add(Transaction transaction)
{
        using (IDbConnection db = Connection)
        {
                return (int)db.Insert(transaction);
        }
}

我看到sql server的答案，这里是MySql使用事务

    Dim sql As String = "INSERT INTO Empleado (nombres, apepaterno, apematerno, direccion, colonia, cp, municipio, estado, tel, cel, correo, idrol, relojchecadorid, relojchecadorid2, `activo`,`extras`,`rfc`,`nss`,`curp`,`imagen`,sueldoXHra, IMSSCotiza, thumb) VALUES (@nombres, @apepaterno, @apematerno, @direccion, @colonia, @cp, @municipio, @estado, @tel, @cel, @correo, @idrol, @relojchecadorid, @relojchecadorid2, @activo, @extras, @rfc, @nss, @curp, @imagen,@sueldoXHra,@IMSSCotiza, @thumb)"
    
            Using connection As IDbConnection = New MySqlConnection(getConnectionString())
                connection.Open()
                Using transaction = connection.BeginTransaction
                    Dim res = connection.Execute(sql, New With {reg.nombres, reg.apepaterno, reg.apematerno, reg.direccion, reg.colonia, reg.cp, reg.municipio, reg.estado, reg.tel, reg.cel, reg.correo, reg.idrol, reg.relojchecadorid, reg.relojchecadorid2, reg.activo, reg.extras, reg.rfc, reg.nss, reg.curp, reg.imagen, reg.thumb, reg.sueldoXHra, reg.IMSSCotiza}, commandTimeout:=180, transaction:=transaction)
                    lastInsertedId = connection.ExecuteScalar("SELECT LAST_INSERT_ID();", transaction:=transaction)
                    If res > 0 Then 
transaction.Commit()
return true
end if
                    
                End Using
            End Using

我使用的是。net core 3.1和postgres 12.3。基于塔迪加·巴加里克的回答，我得出了如下结论:

using (var connection = new NpgsqlConnection(AppConfig.CommentFilesConnection))
        {

            string insertUserSql = @"INSERT INTO mytable(comment_id,filename,content)
                    VALUES( @commentId, @filename, @content) returning id;";

            int newUserId = connection.QuerySingle<int>(
                                            insertUserSql,
                                            new
                                            {
                                                commentId = 1,
                                                filename = "foobar!",
                                                content = "content"
                                            }
                                            );

          


        }

其中AppConfig是我自己的类，它只是为我的连接细节设置了一个字符串。这是在Startup.cs ConfigureServices方法中设置的。