SqlDataAdapter не может получить строку после вставки

Я пытаюсь использовать SqlDataAdapter для вставки строки, а затем сразу же получить ту же строку. Я последовал совету в этом сообщении и использовал SCOPE_IDENTITY, но это не работает. Вот мой код...

using (var conn = _db.OpenConnection())
{
    var sqlQuery = "SELECT * FROM RotationItem WHERE [RotationItemId] = SCOPE_IDENTITY()";
    var adapter = new SqlDataAdapter(sqlQuery, conn);
    var builder = new SqlCommandBuilder(adapter);
    var dataSet = new DataSet();
    adapter.Fill(dataSet);
    Debug.WriteLine("First fill, rows " + dataSet.Tables[0].Rows.Count);
    var table = dataSet.Tables[0];
    table.Rows.InsertAt(table.NewRow(), 0);
    CopyJsonToRow(table, 0, item);
    if (adapter.Update(dataSet) != 1)
    {
        throw new InvalidOperationException("Insert failed");
    }
    // After insert, fetch the new record
    dataSet.Clear();
    adapter.Fill(dataSet);
    Debug.WriteLine("Second fill, rows " + dataSet.Tables[0].Rows.Count);
}

Мой вывод:

  • Первая заливка, строки 0
  • Второе заполнение, строки 0 ‹== это НЕ то, что я ожидаю

Почему вторая заливка не работает? Разве он не должен получить строку, которую я только что вставил?!

Я не использую никаких транзакций. Определение таблицы ниже...

CREATE TABLE [dbo].[RotationItem] (
    [RotationItemId] INT NOT NULL IDENTITY(1,1),
    [RotationScheduleId] INT NOT NULL,   
    [QuestionnaireId] INT NOT NULL,  
    [Order] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([RotationItemId] ASC)
);

c# sql-server ado.net sqldataadapter
person John Henckel    schedule 15.10.2018    source источник
comment
SCOPE_IDENTITY работает в том же сеансе. Я подозреваю, что ваша вставка может быть в другом сеансе, и поэтому последующему SCOPE_IDENTITY нечего будет возвращать. Вы можете проверить это, запустив трассировку профилировщика и посмотреть, что возвращают значения session_id. Лучшим способом было бы использовать хранимую процедуру для ВСТАВКИ вашей строки и возврата значения SCOPE_IDENTITY в качестве выходного параметра.   -  person Martin Cairney    schedule 15.10.2018
comment
@MartinCairney Я думаю, вы правы, потому что, когда я изменяю запрос, чтобы использовать IDENT_CURRENT('RotationItem') вместо SCOPE_IDENTITY(), он работает!! Однако часто это дает неправильный ответ, если несколько вставок выполняются одновременно в разных потоках. Это отстой.   -  person John Henckel    schedule 15.10.2018
comment
Вот страница документа о получении значений идентификаторов с помощью ADO.NET: docs.microsoft.com/en-us/dotnet/framework/data/adonet/   -  person David Browne - Microsoft    schedule 15.10.2018
comment
@DavidBrowne-Microsoft Спасибо, но эта страница мне не помогает, потому что я использую файл SqlCommandBuilder. Вы полагаете, что это невозможно с помощью SqlCommandBuilder? Я попытался добавить ;SELECT SCOPE_IDENTITY() в конец builder.GetInsertCommand().CommandText, но это не безрезультатно. Я пытался установить adapter.InsertCommand, но это не дало результата. Как только я создаю SqlCommandBuilder, я вообще не могу его настроить.   -  person John Henckel    schedule 15.10.2018

Ответы (1)


arrow_upward
0
arrow_downward

Вот что я нашел после нескольких часов возни.

  • Вы не должны использовать IDENT_CURRENT, потому что это очень ненадежно
  • Вы не можете использовать SCOPE_IDENTITY после SqlDataAdapter.Update (как в OP), потому что это закрывает область действия.
  • когда вы вызываете new SqlCommandBuilder(adapter), он делает секретное вуду для вашего адаптера, что делает невозможным настройку. В частности, любые изменения, которые вы вносите в InsertCommand, игнорируются.

Вот мой умный обходной путь (кто-то может сказать, ужасный хак). Это отлично работает, даже если я забиваю его многими одновременными клиентами.

using (var conn = _db.OpenConnection())
{
    var sqlQuery = "SELECT * FROM RotationItem WHERE [RotationItemId] = SCOPE_IDENTITY()";

    // Create a dummy adapter, so we can generate an appropriate INSERT statement
    var dummy = new SqlDataAdapter(sqlQuery, conn);
    var insert = new SqlCommandBuilder(dummy).GetInsertCommand();

    // Append the SELECT to the end of the INSERT command, 
    // and set a flag to copy the result back into the dataSet table row
    insert.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
    insert.CommandText += ";" + sqlQuery;

    // Now proceed as usual...
    var adapter = new SqlDataAdapter(sqlQuery, conn);
    adapter.InsertCommand = insert;
    var dataSet = new DataSet();
    adapter.Fill(dataSet);
    Debug.WriteLine("First fill, rows " + dataSet.Tables[0].Rows.Count);
    var table = dataSet.Tables[0];
    table.Rows.InsertAt(table.NewRow(), 0);
    CopyJsonToRow(table, 0, item);
    if (adapter.Update(dataSet) != 1)
    {
        throw new InvalidOperationException("Insert failed");
    }
    // After insert, the table AUTOMATICALLY has the new row ID (and any other computed columns)
    Debug.WriteLine("The new id is " + table.Rows[0].ItemArray[0]);
}

Отрицательные моменты этого хака в том, что мне нужно сделать два SqlDataAdapters, один из которых просто кормить SqlCommandBuilder. Также использование конкатенации строк для склеивания двух SQL-запросов очень хитроумно. Я не уверен, что внутренний аудит безопасности позволит мне это сделать из-за проблемы с инъекцией. У кого-нибудь есть идея получше?

person John Henckel    schedule 15.10.2018