Jak wykonać wstawienie i zwrócić wstawioną tożsamość w Dapper?


170

Jak wykonać wstawienie do bazy danych i zwrócić wstawioną tożsamość z Dapper?

Próbowałem czegoś takiego:

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

Ale to nie zadziałało.

@Marc Gravell dzięki za odpowiedź. Wypróbowałem twoje rozwiązanie, ale nadal ten sam ślad wyjątku znajduje się poniżej

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

Odpowiedzi:


286

Czyni wsparcia parametrów wejścia / wyjścia (w tym RETURNwartość), jeśli używasz DynamicParameters, ale w tym przypadku prostsza opcja to po prostu:

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

Zauważ, że w nowszych wersjach SQL Server możesz użyć OUTPUTklauzuli:

var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff])
OUTPUT INSERTED.Id
VALUES (@Stuff);", new { Stuff = mystuff});

11
@ppiotrowicz hmmm .... cholera SCOPEIDENTITY powróci numeric, co? Może użyj swojego oryginalnego kodu i select @id? (to tylko dodaje obsadę). Zrobię notatkę, aby upewnić się, że działa to automatycznie w przyszłych eleganckich kompilacjach. Inną opcją na razie jest select cast(SCOPE_IDENTITY() as int)- znowu trochę brzydka. Naprawię to.
Marc Gravell

2
@MarcGravell: Wow! Świetny Marc, to dobry! Nie zdawałem sobie sprawy, że scope_identitytyp zwrotu to numeric(38,0). +1 naprawdę dobre znalezisko. Nigdy o tym nie myślałem i jestem pewien, że nie jestem jedyny.
Robert Koritnik

5
Hej, ta odpowiedź jest hitem numer jeden, jeśli chodzi o odzyskanie wartości tożsamości z eleganckiego zapytania. Wspomniałeś, że jest to znacznie ulepszone podczas wiązania z obiektem; czy możesz edytować i aktualizować, jak chcesz to teraz zrobić? Sprawdziłem wersje w pliku Tests na githubie obok twojego komentarza z 26 listopada, ale nie widzę nic związanego z pytaniem: / Moje założenie jest takie, Query<foo>że wstawia wartości, a następnie wybiera * gdzie id = SCOPE_IDENTITY ().

2
@Xerxes, co sprawia, że ​​uważasz, że to narusza CQS? CQS nie dotyczy tego, czy operacja SQL zwraca siatkę. To jest rozkaz, czysty i prosty. To nie jest zapytanie w terminach CQS, pomimo użycia tego słowa Query.
Marc Gravell

3
Nitpicky, ale zamiast używać Queryi pobierać pierwszą wartość ze zwróconej kolekcji, myślę, że ExecuteScalar<T>ma to większy sens w tym przypadku, ponieważ zwykle zwracana jest co najwyżej jedna wartość.
Peter Majeed

53

KB: 2019779 , „Możesz otrzymać niepoprawne wartości, używając SCOPE_IDENTITY () i @@ IDENTITY”, Klauzula OUTPUT jest najbezpieczniejszym mechanizmem:

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

14
FYI, może to być wolniejsze niż użycie SCOPE_IDENTITY i zostało naprawione w aktualizacji nr 5 do SQL Server 2008 R2 z dodatkiem Service Pack 1.
Michael Silver,

2
@MichaelSilver, czy zalecamy używanie SCOPE_IDENTITY lub @@ IDENTITY przed wyjściem OUTPUT ? KB: 2019779 został NAPRAWIONY ?
Kiquenet

1
@Kiquenet, gdybym pisał kod na bazie danych, która nie została naprawiona, prawdopodobnie użyłbym klauzuli OUTPUT, aby upewnić się, że działa zgodnie z oczekiwaniami.
Michael Silver,

1
@ to działa świetnie do wstawiania pojedynczego rekordu, ale jeśli przejdę do kolekcji, dostajęAn enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context
MaYaN

44

Późna odpowiedź, ale tutaj jest alternatywa dla SCOPE_IDENTITY()odpowiedzi, których użyliśmy: OUTPUT INSERTED

Zwróć tylko ID wstawionego obiektu:

Pozwala uzyskać wszystkie lub niektóre atrybuty wstawionego wiersza:

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

Zwróć wstawiony obiekt z ID:

Jeśli chcesz, możesz dostać, Phonea Emailnawet cały wstawiony wiersz:

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

Dzięki temu możesz również zwrócić dane usuniętych lub zaktualizowanych wierszy. Po prostu bądź ostrożny, jeśli używasz wyzwalaczy, ponieważ:

Kolumny zwracane przez OUTPUT odzwierciedlają dane w takiej postaci, w jakiej są po wykonaniu instrukcji INSERT, UPDATE lub DELETE, ale przed wykonaniem wyzwalaczy.

W przypadku wyzwalaczy INSTEAD OF zwracane wyniki są generowane tak, jakby faktycznie wystąpiły operacje INSERT, UPDATE lub DELETE, nawet jeśli w wyniku operacji wyzwalacza nie nastąpiły żadne modyfikacje. Jeśli instrukcja zawierająca klauzulę OUTPUT jest używana w treści wyzwalacza, aliasy tabel muszą być używane do odwoływania się do tabel wstawionych i usuniętych wyzwalacza, aby uniknąć powielania odwołań do kolumn z tabelami INSERTED i DELETED skojarzonymi z tabelami OUTPUT.

Więcej na ten temat w docs: link


1
@Kiquenet TransactionScope do użycia z zapytaniem. Więcej można znaleźć tutaj: dapper-tutorial.net/transaction i tutaj: stackoverflow.com/questions/10363933/…
Tadija Bagarić

Czy możemy użyć „ExecuteScalarAsync <int>” zamiast „QuerySingle <int>”?
Ebleme

6

Wyjątek InvalidCastException, który otrzymujesz, wynika z tego, że SCOPE_IDENTITY jest wartością dziesiętną (38,0) .

Możesz zwrócić go jako int, rzutując go w następujący sposób:

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

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

4

Nie jestem pewien, czy to dlatego, że pracuję przeciwko SQL 2000, czy nie, ale musiałem to zrobić, aby działał.

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

2
Wypróbuj <code> select cast (SCOPE_IDENTITY () as int) </code> i powinno działać również w 2000 roku.
David Aleu

nie próbowałeś select cast(SCOPE_IDENTITY() as int)?
Kiquenet

1

Istnieje świetna biblioteka, która ułatwi Ci życie Dapper.Contrib.Extensions. Po uwzględnieniu tego możesz po prostu napisać:

public int Add(Transaction transaction)
{
        using (IDbConnection db = Connection)
        {
                return (int)db.Insert(transaction);
        }
}

0

Jeśli używasz 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));
        }
    }

Czym jest Dapper.SimpleSave?
Kiquenet

@Kirquenet, użyłem Dapper, Dapper.SimpleCRUD, Dapper.SimpleCRUD.ModelGenerator, Dapper.SimpleLoad i Dapper.SimpleSave w projekcie, nad którym pracowałem jakiś czas temu. Dodałem je za pomocą importu nuGet. Połączyłem je z szablonem T4, aby stworzyć szkielet całego DAO dla mojej witryny. github.com/Paymentsense/Dapper.SimpleSave github.com/Paymentsense/Dapper.SimpleLoad
Lodlaiden
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.