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

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

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

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

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

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

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

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

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