Использование столбцов с автоинкрементом без конфликтов (ADO.NET)

@Bars4 · Регистрация: 14.03.2013

Author24 — интернет-сервис помощи студентам

Привет участникам форума !
Хочу обратиться за помощью по проблеме обновления DataTable c автоинкрементом.
Одно из решений повседневной задачи использования столбцов с автоинкрементом хорошо известно и состоит в настройке ключевого столбца в exampleDataSet.Tables("exampleDataTable").Columns("id") так:

VB.NET

1
2
3

exampleDataTable.Columns("id").AutoIncrement = True
exampleDataTable.Columns("id").AutoIncrementSeed = -1
exampleDataTable.Columns("id").AutoIncrementStep = -1

При этом подразумеваем, что на стороне SQL Server соответствующее ключевое поле настроено на возрастающий автоинкремент.
Тогда при выполнении обновления данных:

VB.NET

1	exampleDataAdapter.Update(exampleDataTable.GetChanges)

и последующим заполнением exampleDataTable обновленными на SQL Server строками exampleDataRows():

VB.NET

1	exampleDataAdapter.Fill(exampleDataTable)

получим новые (переназначенные) значения "id" ключевого столбца DataColumn("id"), которые заданы самим SQL сервером (SCOPE_IDENTITY (Transact-SQL)).
Проблема состоит в том, что поле заполнения таблицы exampleDataTable уже обновленными строками с SQL сервера в exampleDataTable остаются копии первоначальных строк с отрицательными значениями столбца "id". Вот как-то же exampleDataAdapter должен понимать, что это есть старые версии обновленных строк и автоматических их ликвидировать? Или нет?

@kodv · 18.02.2014, 07:27

Сообщение от Bars4

Вот как-то же exampleDataAdapter должен понимать, что это есть старые версии обновленных строк и автоматических их ликвидировать?

Можно настроить DataAdapter так, чтобы он он при вызове Fill перезаписывал старые строки новыми. Но вам это не поможет, так как идентификация таких строк происходит по первичному ключу, а у вас он заведомо ложный до создания записей в СУБД.

Сообщение от Bars4

Проблема состоит в том, что поле заполнения таблицы exampleDataTable уже обновленными строками с SQL сервера в exampleDataTable остаются копии первоначальных строк с отрицательными значениями столбца "id".

Данная проблема решается в одну строку:

C#

1 2	exampleDataTable.Clear(); // <<- Вот эта строка exampleDataAdapter.Fill(exampleDataTable);

Но вы все равно выбрали нерациональное решение. Попробуйте при создании вашего DataAdapter'а (или в конструкторе формы, если вы его добавили в форму черезе дизайнер) следующий код:

C#

exampleDataAdapter.RowUpdated += (sender, e) =>
{
    if (e.StatementType == StatementType.Insert)
        e.Row["id"] = (new SqlCommand("SELECT SCOPE_IDENTITY()", e.Command.Connection)).ExecuteScalar();
};

В этому случае у вас будут перегружаться

@Bars4 · 18.02.2014, 14:27 **[ТС]**

kodv, спасибо! Насчет нерациональности с ложными "id" согласен однозначно.
В варианте с exampleDataTable.Clear так же возникает дополнительная трудность, если exampleDataTable имеет exampleDataRelation. А что будет с данными, если в дочернюю таблицу examplechldDataTable будут добавлены записи еще до обновления родительской ?
Так же я пытался устанавливать действительно очередной "id" как-то так:

VB.NET

exampleDataAdapter.Fill(exampleDataTable)
exampleDataTable.Columns("id").AutoIncrement = True
exampleDataTable.Columns("id").AutoIncrementSeed = exampleDataTable.Compute("MAX(ID)", String.Empty)
exampleDataTable.Columns("id").AutoIncrementStep = 1

Однако, было замечено, что при таком методе со временем стали возникать "круглые" пропуски "id", например была такая генерация: 119, 120, 121, 1121, 1122, 1123.. 2123, 2124... и т.д.
Т.е. неслучайность последовательности налицо, но источник пропусков так и не смог объяснить.
Предложенный Вами код:

VB.NET

1	e.Row["id"] = (new SqlCommand("SELECT SCOPE_IDENTITY()", e.Command.Connection)).ExecuteScalar()

следует признать лучшим.
А такой вариант будет работать без пропусков "id" ?

VB.NET

exampleDataTable.Columns("id").AutoIncrement = True
exampleDataTable.Columns("id").AutoIncrementSeed = (new SqlCommand("SELECT SCOPE_IDENTITY()", exampleConnection)).ExecuteScalar()
exampleDataTable.Columns("id").AutoIncrementStep = 1
exampleDataAdapter.Fill(exampleDataTable)

@kodv · 18.02.2014, 15:15

Сообщение от Bars4

А такой вариант будет работать без пропусков "id" ?

Такой вариант "подсасывает" иды новых строк из базы данных сразу после их вставки. Но, IDENTITY-столбцы гарантируют только уникальность значений; гарантии генерации идов по порядку нет. Дыры однозначно появятся в процессе использования: какие то строки удалятся, где то транзакция откатится и номер ида "проглотится". Поэтому рассчитывать на то, что IDENTITY-столбец будет порядковым номером вашей строки, не стоит.

@Bars4 · 18.02.2014, 17:27 **[ТС]**

Проблема возникла с тем, что столбец "id" является ключевым и соответственно обработчик события exampleDataAdapter_RowUpdated:

VB.NET

Private Sub exampleDataAdapter_RowUpdated(sender As Object, e As SqlRowUpdatedEventArgs) Handles exampleDataAdapter.RowUpdated
        If e.StatementType = StatementType.Insert Then
            e.Row.Item("id") = New SqlCommand("SELECT SCOPE_IDENTITY()", e.Command.Connection).ExecuteScalar
        End If
End Sub

вызывает исключение "Столбец "id" доступен только для чтения". Но он ведь и в самом деле помечен как ключевой при создании схемы exampleDataTable:

VB.NET

1	exampleDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Так же код явного включения автоинкремента столбц "id" я удалил (закомментировал).

VB.NET

1	'exampleDataTable.Columns("id").AutoIncrement = True

Однако столбец "id" все равно продолжает генериться, причем в нормальной последовательности, как будто ему был сообщен SCOPE_IDENTITY(). Не могу врубиться...

@kodv · 18.02.2014, 18:47

Bars4, чтобы внести ясность в терминологию.
Ключевое поле не является доступным только для чтения. Его можно менять сколько угодно, его единственное ограничение - это уникальность каждого значения среди остальных значений данного столбца.
Поле IDENTITY менять нельзя, но данное поле располагается в таблице СУБД. В объекте DataTable в .NET находится AutoIncrement-поле. Различия не только в название, в DataTable данное поле можно легко менять, чего в СУБД сделать не удастся. Специально не стал отвечать с телефона, а включил ноут и проверил данное утверждение.
У колонки в DataTable может быть задано свойство ReadOnly = true. Если это так, то поле в DataTable изменить уже не получится.
Так вот. Утверждать не буду, с высокой вероятностью исключение вы ловите из-за того, то сами изменили свойство ReadOnly для ключевых столбцов. Проверьте, и, если это так, то измените значение этого свойтсва на false.

PS: Только сейчас обратил, что вы на Visual Basic пишите, в том числе и в первом посте. По хорошему вас нужно было бы в другой раздел отправить - здесь зона C#.

@Bars4 · 18.02.2014, 23:41 **[ТС]**

Виноват, если разместился в разделе, не соответствующем тематике вопроса, но я ориентировался на ADO.NET. Прошу перенести тему в раздел по назначению.
И в действительности вы правы, дело не в ReadOnly столбца. Ваше решение верно на 100 % и его я уже где-то встречал в литературе.
В моем же случае ошибка возникает уже здесь:

VB.NET

1	Dim newID As Integer = CInt(New SqlCommand("SELECT SCOPE_IDENTITY()", sqlClientConnection).ExecuteScalar)

так как ExecuteScalar возвращает DBNull. Наверно проблема где-то в sqlClientConnection.

@kodv · 19.02.2014, 02:54

Bars4, возможно - это из-за scope_identity. Я обычно в таких случаях использую @@identity, но увидев, что вы пишите про scope_identity вставил его. @@identity ,может выдать не верный результат в случае наличия триггеров, а у scope_identity область действия - это пакет, функция или хп. То есть, в данном случае команда может восприниматься как в отдельном от вставки пакете. попробуйте использовать @@identity.

@Bars4 · 19.02.2014, 23:01 **[ТС]**

kodv, за терпение спасибо.
Мои пробелы в знании матчасти очевидны. Однако выцыганивание ответов на форуме всегда интереснее, чем сухая техдокументация.
Попробовал, код:

VB.NET

1	Dim newID As Integer = CInt(New SqlCommand("SELECT @@IDENTITY", .Command.Connection).ExecuteScalar)

работает. Спасибо. Но при этом не понятно, что стало источником DBNull в случае:

VB.NET

1	Dim newID As Integer = CInt(New SqlCommand("SELECT SCOPE_IDENTITY()", e.command.Connection).ExecuteScalar)

Замена SCOPE_IDENTITY() на @@IDENTITY или ограничение использования триггеров это всего лишь уход от ситуации. Корень проблемы я не осознал.
Может (нужны танцы с бубнами) не допустимо/не рекомендуется выпонять команды Update и Fill для одного и того же DataTable на одном и том же экземпляре Connection, Command и Adapter ?
ЗЫ: в литературе я нигде не встречал обязательности установки значения свойства AutoIncrementSeed. Значит, хотите ставьте, хотите нет. При этом установка только AutoIncrement в тру, формирует на первый взгляд адэкватную последовательность id. Тогда зачем бы в книгах рекомендовали exampleDataAdapter_RowUpdated?
Если есть ИМХО по этому поводу, прошу поделиться.

@kodv · 20.02.2014, 10:35

Сообщение от Bars4

Мои пробелы в знании матчасти очевидны.

Матчасть полностью знать невозможно. Сам полез в MSDN, чтобы дать вам полноценный ответ и не соврать.

На самом деле в MS SQL Server есть еще и третья функция, для получения последнего вставленного идентификатора. Сначала цитата из MSDN:

IDENT_CURRENT возвращает последнее значение идентифицирующего столбца, созданное для конкретной таблицы в любом сеансе и области поиска.
@@IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе по всем областям поиска.
SCOPE_IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе по текущей области поиска.

Также на MSDN есть пример, который наглядно показывает различия этих трех функций:

Кликните здесь для просмотра всего текста

T-SQL

IF OBJECT_ID(N't6', N'U') IS NOT NULL 
    DROP TABLE t6;
GO
IF OBJECT_ID(N't7', N'U') IS NOT NULL 
    DROP TABLE t7;
GO
CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT 
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END;
GO
--End of trigger definition
SELECT id FROM t6;
--id is empty.
SELECT id FROM t7;
--ID is empty.
--Do the following in Session 1
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/
SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the 
INSERT statement two statements before this query.*/
SELECT IDENT_CURRENT('t7');
/* Returns value inserted into t7, that is in the trigger.*/
SELECT IDENT_CURRENT('t6');
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/
-- Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action 
up to this point in this session.*/
SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action 
up to this point in this scope in this session.*/
SELECT IDENT_CURRENT('t7');
/* Returns the last value inserted into t7.*/

Так вот, могу, конечно, и ошибаться, но, скорее всего область видимости в вашем случае ограничивается командой, которая отсылается к серверу. То есть, "INSERT INTO" и "SELECT SCOPE_IDENTITY()" находятся в одном сеансе, но в разных областях видимости. В области видимости "SELECT SCOPE_IDENTITY()" вставок в IDENTITY-столбцы не происходило, поэтому данное выражение в качестве результата возвращает NULL. В то же самое время "SELECT @@IDENTITY" распространяется не на область видимости, а на сеанс, поэтому он возвращает верное значение. Можно было бы еще в вашем случае использовать команду "SELECT IDENT_CURRENT('example')" и она бы работало.
Оптимальной функции для считывания последенго вставленного значения не существуют. У каждой есть свои подобные камни:
- IDENT_CURRENT возвращает id последней вставленной записи в конкретную таблицу, но он не дает гарантии, что будет ид именно той записи, которую вставили вы. Это может быть ид записи, который была вставлена после вашей, но до того, как вызвали функцию IDENT_CURRENT.
- @@IDENTITY возвращает id последней вставленной записи в рамках сеанса, но в случае совершения скрытых действий (триггеров) может вернутся id не той записи, которую вы ожидаете. Но если вы знаете. что у вас на нужной таблице нет и не будет триггеров, которые бы могли сбить с толку @@IDENTITY, то можно смело использовать его.
- SCOPE_IDENTITY возвращает нужный, ожидаемый id, но только в том случае, если команда вставки и считывания id'а будут выполнены в одном пакете. То есть, для обработки события RowUpdated она не подходит. Зато она лучше остальных подходит для второго способа получить идентификатор новой записи.

Немножко про второй способ. Он менее универсальный, чем первый, но для SQL Server'а подходит. Можно для вставки написать хранимую процедуру, или составить параметризированный запрос, который вместе со вставкой записи будет возвращать значение нового идентификатора в OUTPUT параметре. После этого вам останется у этого OUTPUT параметра указать SourceColumn = "id", и SqlDataAdapter сам автоматически обновит все ваши иды после вставки записей в базу данных. Внутри этой хранимой процедуру можно смело использовать SCOPE_IDENTITY() для получения вставленного id'а.

@Bars4 · 20.02.2014, 16:00 **[ТС]**

kodv, спасибо за развернутый ответ. Доходчиво и исчерпывающе разжевано.

Добавлено через 4 часа 13 минут
Так же считаю последний ответ kodv следует поместить в зеленую рамку и закрепить на форуме, т.к. в нем подведены толковые резюме и пояснения по поводу работы с IDENTITY на ADO.NET. Многим это будет полезным.

Использование столбцов с автоинкрементом без конфликтов (ADO.NET)

Решение

Решение

@kodv 1449 / 1121 / 347 Регистрация: 11.04.2011 Сообщений: 2,621
	18.02.2014, 15:15	4
	Сообщение от Bars4 А такой вариант будет работать без пропусков "id" ? Такой вариант "подсасывает" иды новых строк из базы данных сразу после их вставки. Но, IDENTITY-столбцы гарантируют только уникальность значений; гарантии генерации идов по порядку нет. Дыры однозначно появятся в процессе использования: какие то строки удалятся, где то транзакция откатится и номер ида "проглотится". Поэтому рассчитывать на то, что IDENTITY-столбец будет порядковым номером вашей строки, не стоит. 1

@kodv 1449 / 1121 / 347 Регистрация: 11.04.2011 Сообщений: 2,621
	18.02.2014, 18:47	6
	Bars4, чтобы внести ясность в терминологию. Ключевое поле не является доступным только для чтения. Его можно менять сколько угодно, его единственное ограничение - это уникальность каждого значения среди остальных значений данного столбца. Поле IDENTITY менять нельзя, но данное поле располагается в таблице СУБД. В объекте DataTable в .NET находится AutoIncrement-поле. Различия не только в название, в DataTable данное поле можно легко менять, чего в СУБД сделать не удастся. Специально не стал отвечать с телефона, а включил ноут и проверил данное утверждение. У колонки в DataTable может быть задано свойство ReadOnly = true. Если это так, то поле в DataTable изменить уже не получится. Так вот. Утверждать не буду, с высокой вероятностью исключение вы ловите из-за того, то сами изменили свойство ReadOnly для ключевых столбцов. Проверьте, и, если это так, то измените значение этого свойтсва на false. PS: Только сейчас обратил, что вы на Visual Basic пишите, в том числе и в первом посте. По хорошему вас нужно было бы в другой раздел отправить - здесь зона C#. 1

@kodv 1449 / 1121 / 347 Регистрация: 11.04.2011 Сообщений: 2,621
	19.02.2014, 02:54	8
	Bars4, возможно - это из-за scope_identity. Я обычно в таких случаях использую @@identity, но увидев, что вы пишите про scope_identity вставил его. @@identity ,может выдать не верный результат в случае наличия триггеров, а у scope_identity область действия - это пакет, функция или хп. То есть, в данном случае команда может восприниматься как в отдельном от вставки пакете. попробуйте использовать @@identity. 1

@Bars4 68 / 8 / 1 Регистрация: 14.03.2013 Сообщений: 116
	20.02.2014, 16:00 [ТС]	11
	kodv, спасибо за развернутый ответ. Доходчиво и исчерпывающе разжевано. Добавлено через 4 часа 13 минут Так же считаю последний ответ kodv следует поместить в зеленую рамку и закрепить на форуме, т.к. в нем подведены толковые резюме и пояснения по поводу работы с IDENTITY на ADO.NET. Многим это будет полезным. 0