Как выполнить вставку и возврат вставленной идентичности с помощью 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();

Но это не сработало.

@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

Ответ 1

Он поддерживает параметры ввода/вывода (включая значение RETURN), если вы используете DynamicParameters, но в этом случае более простой вариант:

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

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

Ответ 2

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

Ответ 3

Не уверен, что это было потому, что я работаю против 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();

Ответ 4

Полученное вами InvalidCastException связано с тем, что SCOPE_IDENTITY является Decimal (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();

Ответ 5

Если вы используете 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));
        }
    }

Ответ 6

Вот альтернатива ответам SCOPE_IDENTITY(), которые мы закончили с помощью: OUTPUT INSERTED

Возвращает только идентификатор вставленного объекта:

Он позволяет получить все или некоторые атрибуты вставленной строки:

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

Возвращенный вставленный объект с идентификатором:

Если вы хотите, вы можете получить 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, используется внутри тела триггер, псевдонимы таблицы должны использоваться для ссылки на введенный триггер и удаленные таблицы, чтобы избежать дублирования ссылок столбцов с помощью INSERTED и DELETED, связанные с OUTPUT.

Подробнее об этом в документах: ссылка