如果你使用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});

我使用的是。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方法中设置的。

如果你使用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();

一个迟来的答案，但这里有一个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