如何使用Dapper执行插入并返回插入的标识?

本文关键字：插入 标识 返回 Dapper 执行 何使用 | 更新日期: 2023-09-27 18:15:38

如何执行插入数据库并返回插入的身份与Dapper?

我试过这样做:

string sql = "DECLARE @ID int; " +
             "INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); " +
             "SELECT @ID = SCOPE_IDENTITY()";
var id = connection.Query<int>(sql, new { Stuff = mystuff}).First();

但是没有成功。

@Marc Gravell谢谢你的回复。我已经尝试过你的解决方案，但是，仍然相同的异常跟踪是低于

System.InvalidCastException: Specified cast is not valid
at Dapper.SqlMapper.<QueryInternal>d__a`1.MoveNext() in (snip)'Dapper'SqlMapper.cs:line 610
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in (snip)'Dapper'SqlMapper.cs:line 538
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param) in (snip)'Dapper'SqlMapper.cs:line 456

如何使用Dapper执行插入并返回插入的标识?

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

一个迟来的答案，但这里有一个替代我们最终使用的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为

的插入对象

如果你想，你可以得到Phone和Email，甚至整个插入的行:

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

还可以返回删除的或更新的行的数据。如果您正在使用触发器，请小心，因为(来自前面提到的链接):

从OUTPUT返回的列反映数据，因为它位于INSERT、UPDATE或DELETE语句已完成，但尚未触发执行。

对于INSTEAD OF触发器，生成的返回结果就像实际上已经发生了INSERT、UPDATE或DELETE操作，即使没有修改作为触发器操作的结果发生。如果一个包含OUTPUT子句的语句在触发器、表别名必须用于引用插入的触发器并删除表，以避免与插入和删除与OUTPUT相关的表。

更多信息见文档:link

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

您正在获得的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();

不确定是否因为我正在针对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();

我使用。net core 3.1与postgres 12.3。根据Tadija bagariki的回答，我最后写了:

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方法中设置。

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

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

如果您使用的是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));
        }
    }
相关文章：