我如何执行插入和返回插入的身份与Dapper?

我如何执行插入数据库和返回插入的身份与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();

但这并没有起作用。

谢谢你的回复。我已经尝试了您的解决方案，但仍然相同的异常跟踪如下

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

当前回答

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

2021-02-06 09:59:32

其他回答

我看到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

2020-11-23 20:27:23

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

2011-11-25 14:07:03

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

2021-02-06 09:59:32

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

2012-04-18 21:01:07

你得到的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();

2012-06-28 20:24:11

我如何执行插入和返回插入的身份与Dapper?

推荐文章

最新文章

标签