c# - framework - ¿Cómo realizo una inserción y regreso la identidad insertada con Dapper?

idbconnection dapper

Question

¿Cómo realizo una inserción en la base de datos y regreso la identidad insertada con Dapper?

He intentado algo como esto:

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

Pero no funcionó.

@Marc Gravell gracias, por su respuesta. He intentado con tu solución, pero sigue el mismo rastro de excepción debajo

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

Answer 1

La InvalidCastException que recibe se debe a que SCOPE_IDENTITY es un Decimal(38,0) .

Puede devolverlo como un int arrojándolo de la siguiente manera:

string sql = @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int id = connection.Query<int>(sql, new { Stuff = mystuff}).Single();

Answer 2

No estoy seguro si fue porque estoy trabajando contra SQL 2000 o no, pero tuve que hacer esto para que funcione.

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

Answer 3

Si usas 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));
}
}

Answer 4

Una respuesta tardía, pero aquí hay una alternativa a las respuestas de SCOPE_IDENTITY() que terminamos usando: OUTPUT INSERTED

Solo devolución de ID del objeto insertado:

Le permite obtener todos o algunos atributos de la fila insertada:

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

Devuelve el objeto insertado con la ID:

Si lo desea, puede obtener Phone y Email o incluso toda la fila insertada:

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

Además, con esto puede devolver datos de filas eliminadas o actualizadas . Solo ten cuidado si estás usando disparadores porque:

Las columnas devueltas desde OUTPUT reflejan los datos tal como están después de que se haya completado la instrucción INSERT, UPDATE o DELETE, pero antes de que se ejecuten los desencadenadores.

Para los desencadenadores INSTEAD OF, los resultados devueltos se generan como si realmente se hubieran producido INSERTAR, ACTUALIZAR o ELIMINAR, incluso si no se producen modificaciones como resultado de la operación desencadenante. Si se usa una declaración que incluye una cláusula OUTPUT dentro del cuerpo de un desencadenante, los alias de tabla se deben usar para hacer referencia a las tablas de inserción y eliminación de desencadenante para evitar la duplicación de referencias de columna con las tablas INSERTED y DELETED asociadas a OUTPUT.

Más sobre esto en los documentos: link

Answer 5

KB:2019779 , "Puede recibir valores incorrectos al usar SCOPE_IDENTITY () y @@ IDENTITY", la cláusula OUTPUT es el mecanismo más seguro:

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

Answer 6

DynamicParameters parámetros de entrada / salida (incluido el valor RETURN ) si utiliza DynamicParameters , pero en este caso la opción más simple es simplemente:

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