Geek asks and answers

Reservation in place of the primary key column

I have a table with a composite primary key:

table MyTable 
(
    some_id smallint not null,
    order_seq smallint not null,
    --other columns
)

... where some_idthey order_seqmake up the composite primary key. The column is order_seqused to determine the display order in other parts of my C # 3.5 / ASP.NET application.

I am setting up an admin page where users can shuffle these lines around by changing several lines at once order_seq:

screenshot

All items on the screen are the same some_id. When the user clicks the submit button, the new values order_seqmust be saved in the database.

My problem is that it order_seqis part of the PC, so the task of shuffling these things becomes difficult. Because some_idthe same thing, I have no way to identify them, except for the order_seqone that changes when I go. Also, I have to make sure that the values order_seqremain unique (presumably with some kind of temporary value).

The best idea I have is to use some kind of in-memory collection to keep track of the changes I have made so far. However, I have problems implementing it. How can I do local repetition of multiple lines at once? I would be fine with a C # or SQL solution.

EDIT: , , . PK .

+3
3

, , . order_seq - , ( ). .

, , , , . , , :

UPDATE Table SET order_seq = @newval + BIG_OFFSET WHERE order_seq = @oldval

UPDATE Table SET order_seq = order_seq - BIG_OFFSET WHERE order_seq > BIG_OFFSET

, , reset . .

+2

PK ( ). , , . PK .

: , , .. PK PK, , , , , . Id orderId, , . , . .

+1

SP, XML order_seq order_seq.

create table MyTable
(
  some_id int,
  order_seq int,
  name varchar(10)
  primary key(some_id, order_seq)
)

insert into MyTable values
(1, 1, '1_1'),
(1, 2, '1_2'),
(1, 3, '1_3'),
(1, 4, '1_4'),
(2, 1, '2_1'),
(2, 2, '2_2')

proc

create procedure SetOrder
  @some_id int,
  @new_order xml
as

;with cte as
(
  select 
    X.N.value('@OldSeq', 'int') as OldSeq,
    X.N.value('@NewSeq', 'int') as NewSeq
  from @new_order.nodes('/i') as X(N)
)  
update T
  set order_seq = C.NewSeq
from MyTable as T  
  inner join cte as C
    on T.order_seq = C.OldSeq
where T.some_id = @some_id

some_id = 1, SP :

exec SetOrder 1, 
  '<i OldSeq="1" NewSeq="4"/>
   <i OldSeq="2" NewSeq="3"/>
   <i OldSeq="3" NewSeq="2"/>
   <i OldSeq="4" NewSeq="1"/>'

order_seq xml, .

+1

More articles:


All Articles