Example of Asp.net sorting upwards and downwards

Some frequently-used code is recorded at work, so that you can easily find it for your reference.

Not to mention, this is a function of sorting up and down. First, use the stored procedure as follows:

SQL:

-- ===================================================== ======
-- Author: <Author, Name>
-- Create date: <Create Date,>
-- Description: <Description,>
-- ===================================================== ======
Alter procedure [dbo]. [sp_BannerOrder]
-- Add the parameters for the stored procedure here
(
@ Tablename nvarchar (50), -- table name
@ Colname nvarchar (50), -- Sort Field
@ Keyid nvarchar (50), -- table primary key field
@ Keyidvalue int, -- table primary key field value 1
@ Order nvarchar (20), -- default list sorting method, asc or desc
@ OrderDirection nvarchar (20), -- sort direction, up or down
@ Where nvarchar (2000) -- Query Condition
)
AS
BEGIN
Declare @ ordertmp1 int; -- temporary sorting value id1
Declare @ ordertmp2 int; -- temporary sorting value id2
Declare @ tmpkeyidvaule nvarchar (50 );
Declare @ SQL nvarchar (2000 );
DECLARE @ ParmDefinition nvarchar (500 );
DECLARE @ ParmDefinition2 nvarchar (500 );

If @ order = 'asc'
Begin
SET @ SQL = n' SELECT @ ordertmp1OUT = '+ @ colname + 'from' + @ tablename + 'where' + @ keyid +' = '+ cast (@ keyidvalue as nvarchar (50 ));
SET @ ParmDefinition = n' @ ordertmp1OUT nvarchar (20) output ';
EXECUTE sp_executesql @ SQL, @ ParmDefinition, @ ordertmp1OUT = @ ordertmp1 OUTPUT;

If @ orderDirection = 'up'
Begin
SET @ SQL = n' SELECT top 1 @ ordertmp2OUT = '+ @ colname + ', @ tmpkeyidvauleOUT = '+ @ keyid + 'from' + @ tablename + 'where' + @ colname +' <'+ cast (@ ordertmp1 as nvarchar (50 )) + 'and' + @ where + 'ORDER BY' + @ colname + 'desc ';
End
Else
Begin
SET @ SQL = n' SELECT top 1 @ ordertmp2OUT = '+ @ colname + ', @ tmpkeyidvauleOUT = '+ @ keyid + 'from' + @ tablename + 'where' + @ colname +'> '+ cast (@ ordertmp1 as nvarchar (50 )) + 'and' + @ where + 'ORDER BY' + @ colname + 'asc ';
End

SET @ ParmDefinition = n' @ ordertmp2OUT nvarchar (20) OUTPUT, @ tmpkeyidvauleOUT nvarchar (20) output ';
EXECUTE sp_executesql @ SQL, @ ParmDefinition, @ ordertmp2OUT = @ ordertmp2 OUTPUT, @ tmpkeyidvauleOUT = @ tmpkeyidvaule OUTPUT;
End
Else
Begin
SET @ SQL = n' SELECT @ ordertmp1OUT = '+ @ colname + 'from' + @ tablename + 'where' + @ keyid +' = '+ cast (@ keyidvalue as nvarchar (50 ));
SET @ ParmDefinition = n' @ ordertmp1OUT nvarchar (20) output ';
EXECUTE sp_executesql @ SQL, @ ParmDefinition, @ ordertmp1OUT = @ ordertmp1 OUTPUT;
If @ orderDirection = 'up'
Begin
SET @ SQL = n' SELECT top 1 @ ordertmp2OUT = '+ @ colname + ', @ tmpkeyidvauleOUT = '+ @ keyid + 'from' + @ tablename + 'where' + @ colname +'> '+ cast (@ ordertmp1 as nvarchar (50 )) + 'and' + @ where + 'ORDER BY' + @ colname + 'asc ';
End
Else
Begin
SET @ SQL = n' SELECT top 1 @ ordertmp2OUT = '+ @ colname + ', @ tmpkeyidvauleOUT = '+ @ keyid + 'from' + @ tablename + 'where' + @ colname +' <'+ cast (@ ordertmp1 as nvarchar (50 )) + 'and' + @ where + 'ORDER BY' + @ colname + 'desc ';
End

SET @ ParmDefinition = n' @ ordertmp2OUT nvarchar (20) OUTPUT, @ tmpkeyidvauleOUT nvarchar (20) output ';
EXECUTE sp_executesql @ SQL, @ ParmDefinition, @ ordertmp2OUT = @ ordertmp2 OUTPUT, @ tmpkeyidvauleOUT = @ tmpkeyidvaule OUTPUT;
End

Set @ SQL = 'update' + @ tablename + 'set' + @ colname + '=' + cast (@ ordertmp2 as nvarchar (50 )) + 'where' + @ keyid + '=' + cast (@ keyidvalue as nvarchar (50 ));
Set @ SQL = @ SQL + 'update' + @ tablename + 'set' + @ colname + '=' + cast (@ ordertmp1 as nvarchar (50 )) + 'where' + @ keyid + '=' + cast (@ tmpkeyidvaule as nvarchar (50 ));

-- Select @ ordertmp1, @ ordertmp2, @ tmpkeyidvaule, @ SQL
Exec (@ SQL );
END

• Four pages in total:
• Previous Page
• 1
• 2
• 3
• 4
• Next Page