Please refer this code.
HTML
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<asp:Button ID="btnUp" Text="Up" runat="server" OnClick="MoveUp" />
<asp:Button ID="btnDown" Text="Down" runat="server" OnClick="MoveDown" />
</td>
<td>
<asp:DropDownList runat="server" ID="ddlCities" OnSelectedIndexChanged="OnSelectedIndexChanged"
AutoPostBack="true">
</asp:DropDownList>
</td>
</tr>
</table>
Namespace
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.PopulateDropDownList();
this.btnUp.Enabled = false;
}
}
private void PopulateDropDownList()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Cities", conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
ddlCities.DataTextField = "CityName";
ddlCities.DataValueField = "CityId";
ddlCities.DataSource = dt;
ddlCities.DataBind();
ViewState["DataTable"] = dt;
}
}
}
}
protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
if (this.ddlCities.SelectedIndex == this.ddlCities.Items.Count - 1)
{
this.btnDown.Enabled = false;
this.btnUp.Enabled = true;
}
if (this.ddlCities.SelectedIndex == 0)
{
this.btnUp.Enabled = false;
this.btnDown.Enabled = true;
}
if ((this.ddlCities.SelectedIndex != this.ddlCities.Items.Count - 1) && (this.ddlCities.SelectedIndex != 0))
{
this.btnUp.Enabled = true;
this.btnDown.Enabled = true;
}
}
protected void MoveUp(object sender, EventArgs e)
{
this.UpdateCityIndex("Up");
this.PopulateDropDownList();
}
protected void MoveDown(object sender, EventArgs e)
{
this.UpdateCityIndex("Down");
this.PopulateDropDownList();
}
private void UpdateCityIndex(string action)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("UpdateCityPositions", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CityName", this.ddlCities.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Action", action);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
SQL
CREATE TABLE [Cities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[CityName] [varchar](50) NULL,
[RowIndex] [int] NULL
) ON [PRIMARY]
GO
-------------------------------------
INSERT INTO [Cities]
([CityName]
,[RowIndex])
VALUES
('Mumbai'
,1)
GO
INSERT INTO [Cities]
([CityName]
,[RowIndex])
VALUES
('Kolkata'
,2)
GO
INSERT INTO [Cities]
([CityName]
,[RowIndex])
VALUES
('Delhi'
,3)
GO
INSERT INTO [Cities]
([CityName]
,[RowIndex])
VALUES
('pune'
,4)
GO
-----------------------------------------------
CREATE PROCEDURE UpdateCityPositions
@CityName VARCHAR(50),
@Action VARCHAR(15)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentIndex INT
DECLARE @NewRowIndex INT
DECLARE @ReplacingCityName VARCHAR(50)
SET @CurrentIndex = (SELECT RowIndex
FROM Cities
WHERE CityName = @CityName)
IF LOWER(@Action) = 'up'
BEGIN
SET @NewRowIndex = (SELECT RowIndex
FROM Cities
WHERE RowIndex = @CurrentIndex - 1)
SET @ReplacingCityName = (SELECT CItyName
FROM Cities
WHERE RowIndex = @NewRowIndex)
UPDATE Cities SET CityName = @CityName WHERE RowIndex = @NewRowIndex
UPDATE Cities SET CityName = @ReplacingCityName WHERE RowIndex = @CurrentIndex
END
IF LOWER(@Action) = 'down'
BEGIN
SET @NewRowIndex = (SELECT RowIndex
FROM Cities
WHERE RowIndex = @CurrentIndex + 1)
SET @ReplacingCityName = (SELECT CityName
FROM Cities
WHERE RowIndex = @NewRowIndex)
UPDATE Cities SET CityName = @CityName WHERE RowIndex = @NewRowIndex
UPDATE Cities SET CityName = @ReplacingCityName WHERE RowIndex = @CurrentIndex
END
END
GO
Screenshot
After Clicking Up Button.
