Update DataTable with unique index using DataAdapter

[Guest]

Hi

I am having a problem updating a DataTable that has a unique index on an
editable column.

For example, say there is a table with an integer field called [Value] that
has a SQL unique index. This column is in addition to the primary key.

Say that the table has five rows with values:-

PK Value
1 10
2 20
3 30
4 40
5 50

I fill a DataTable with these rows and let the user edit them to:-

PK Value
1 30
2 45
3 35
4 40
5 50

When these changes get submitted using a DataAdpater, they get sent to the
server row by row. The update of row 1 will fail because of the unique index
on the value column, there being already a row with value 30.

To update successfully the rows must be submitted in a specific order to
avoid the index violation. We can visually inspect the example above and
easily determine what the correct order is. This is not so easy in the
application.

Does anyone know a neat solution to this issue?

 

[Jon Skeet [C# MVP]]

I am having a problem updating a DataTable that has a unique index on an
editable column.

For example, say there is a table with an integer field called [Value] that
has a SQL unique index. This column is in addition to the primary key.

Say that the table has five rows with values:-

PK Value
1 10
2 20
3 30
4 40
5 50

I fill a DataTable with these rows and let the user edit them to:-

PK Value
1 30
2 45
3 35
4 40
5 50

When these changes get submitted using a DataAdpater, they get sent to the
server row by row. The update of row 1 will fail because of the unique index
on the value column, there being already a row with value 30.

To update successfully the rows must be submitted in a specific order to
avoid the index violation. We can visually inspect the example above and
easily determine what the correct order is. This is not so easy in the
application.

Does anyone know a neat solution to this issue?

Rather than relying on a specific order, wouldn't putting the whole
thing into a transaction sort the problem out? That way all the updates
should happen simultaneously. Maybe I'm overly hopeful about how clever
transactions are, of course... (I really must learn more about the
details some time...)

 

[Guest]

Thanks for the reply Jon

I dont think a transaction would help. Each row update would still be
subject to the index as it is submitted. The transaction would simply enable
you to roll back all updates if you encountered some failures.

I am having a problem updating a DataTable that has a unique index on an
editable column.

For example, say there is a table with an integer field called [Value] that
has a SQL unique index. This column is in addition to the primary key.

Say that the table has five rows with values:-

PK Value
1 10
2 20
3 30
4 40
5 50

I fill a DataTable with these rows and let the user edit them to:-

PK Value
1 30
2 45
3 35
4 40
5 50

When these changes get submitted using a DataAdpater, they get sent to the
server row by row. The update of row 1 will fail because of the unique index
on the value column, there being already a row with value 30.

To update successfully the rows must be submitted in a specific order to
avoid the index violation. We can visually inspect the example above and
easily determine what the correct order is. This is not so easy in the
application.

Does anyone know a neat solution to this issue?

Rather than relying on a specific order, wouldn't putting the whole
thing into a transaction sort the problem out? That way all the updates
should happen simultaneously. Maybe I'm overly hopeful about how clever
transactions are, of course... (I really must learn more about the
details some time...)

 

[Jon Skeet [C# MVP]]

Thanks for the reply Jon

I dont think a transaction would help. Each row update would still be
subject to the index as it is submitted. The transaction would simply enable
you to roll back all updates if you encountered some failures.

Have you tried it, out of interest? It's a shame if you can't go
through constraint-violating states in a transaction, even if you end
up with a valid state by the time you commit.

Maybe I'll try it myself

 

[John Saunders]

Hi

I am having a problem updating a DataTable that has a unique index on an
editable column.

For example, say there is a table with an integer field called [Value]
that
has a SQL unique index. This column is in addition to the primary key.

Say that the table has five rows with values:-

PK Value
1 10
2 20
3 30
4 40
5 50

I fill a DataTable with these rows and let the user edit them to:-

PK Value
1 30
2 45
3 35
4 40
5 50

When these changes get submitted using a DataAdpater, they get sent to the
server row by row. The update of row 1 will fail because of the unique
index
on the value column, there being already a row with value 30.

To update successfully the rows must be submitted in a specific order to
avoid the index violation. We can visually inspect the example above and
easily determine what the correct order is. This is not so easy in the
application.

Does anyone know a neat solution to this issue?

You'll have to perform the Update repeatedly, until all rows get updated or
until the situation stops improving (and only up to a certain maximum number
of iterations).

Or, perhaps this isn't a good candidate for batch update?

John Saunders

 

[Guest]

Hi John

Yes, I thought that was a solution and it does work quite happily. It just
seems a bit, well, untidy.

I was thinking someone might reply:- Yes, this a well known scenario. You do
it this way.

Surely it is not an unusual problem?

Hi

I am having a problem updating a DataTable that has a unique index on an
editable column.

For example, say there is a table with an integer field called [Value]
that
has a SQL unique index. This column is in addition to the primary key.

Say that the table has five rows with values:-

PK Value
1 10
2 20
3 30
4 40
5 50

I fill a DataTable with these rows and let the user edit them to:-

PK Value
1 30
2 45
3 35
4 40
5 50

When these changes get submitted using a DataAdpater, they get sent to the
server row by row. The update of row 1 will fail because of the unique
index
on the value column, there being already a row with value 30.

To update successfully the rows must be submitted in a specific order to
avoid the index violation. We can visually inspect the example above and
easily determine what the correct order is. This is not so easy in the
application.

Does anyone know a neat solution to this issue?

You'll have to perform the Update repeatedly, until all rows get updated or
until the situation stops improving (and only up to a certain maximum number
of iterations).

Or, perhaps this isn't a good candidate for batch update?

John Saunders

 

[John Saunders]

Hi John

Yes, I thought that was a solution and it does work quite happily. It just
seems a bit, well, untidy.

I was thinking someone might reply:- Yes, this a well known scenario. You
do
it this way.

Surely it is not an unusual problem?

I'm sure it's not an unusual problem. But then again, iteration is not an
unusual solution...


John Saunders