Update row when reordering ASP.Net GridView using jQuery Drag and Drop

Hi gkoutsiv,

Refer below sample.

HTML

Default.aspx

        <asp:GridView ID="gvLocations" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:TemplateField HeaderText="Id" ItemStyle-Width="30">
                    <ItemTemplate>
                        <%# Eval("Id") %>
                        <input type="hidden" name="LocationId" value='<%# Eval("Id") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Location" HeaderText="Location" ItemStyle-Width="150" />
                <asp:BoundField DataField="Preference" HeaderText="Preference" ItemStyle-Width="100" />
                <asp:BoundField DataField="UpdatedBy" HeaderText="UpdatedBy" ItemStyle-Width="100" />
            </Columns>
        </asp:GridView>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
        <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
        <script type="text/javascript">
            $(function () {
                $("[id*=gvLocations]").sortable({
                    items: 'tr:not(tr:first-child)',
                    cursor: 'pointer',
                    axis: 'y',
                    dropOnEmpty: false,
                    start: function (e, ui) {
                        ui.item.addClass("selected");
                    },
                    stop: function (e, ui) {
                        var id = $.trim(ui.item.find('td').eq(0).text());
                        var holidayLocationsPref = [];
                        $.each($(this).find('tr:has(td)'), function (item) {
                            var obj = {};
                            obj.Id = id;
                            obj.locationId = $.trim($(this).find("TD").eq(0).text());
                            obj.location = $.trim($(this).find("TD").eq(1).text());
                            obj.preference = $.trim($(this).find("TD").eq(2).text());
                            obj.UpdatedBy = $.trim($(this).find("TD").eq(3).text());
                            holidayLocationsPref.push(obj);
                        });
                        ui.item.removeClass("selected");
                        $.ajax({
                            type: "POST",
                            url: "Default.aspx/UpdatePreference",
                            data: JSON.stringify({ data: holidayLocationsPref }),
                            contentType: "application/json; charset=utf-8",
                            dataType: "json",
                            success: function (response) {
                                location.reload();
                            }, error: function (response) {
                            }
                        });
                    },
                    receive: function (e, ui) {
                        $(this).find("tbody").append(ui.item);
                    }
                });
            });
        </script>

Login.aspx

Name : <asp:TextBox runat="server" ID="txtUserName" /><br />
<asp:Button Text="Login" runat="server"  OnClick="Logging"/>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Web.Services;

VB.Net

Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.Services

Code

C#

Default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string query = "SELECT Id, Location, Preference,UpdatedBy FROM HolidayLocationsPref ORDER BY Preference";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvLocations.DataSource = dt;
                    gvLocations.DataBind();
                }
            }
        }
    }
}

[WebMethod]
public static void UpdatePreference(List<HlidayData> data)
{
    int preference = 1;
    foreach (var item in data)
    {
        UpdatePreference(item.LocationId, preference, item.Id);
        preference++;
    }
}

private static void UpdatePreference(int locationId, int preference, int id)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Parameters.AddWithValue("@Id", locationId);
                cmd.Parameters.AddWithValue("@Preference", preference);
                string query = "";
                if (locationId == id)
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id";
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session["User"].ToString());
                }
                else
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id";
                }
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

public class HlidayData
{
    public int Id { get; set; }
    public int LocationId { get; set; }
    public string Location { get; set; }
    public int Preference { get; set; }
    public string UpdatedBy { get; set; }

}

Login.aspx.cs

protected void Logging(object sender, EventArgs e)
{
    Session["User"] = txtUserName.Text;
    Response.Redirect("Default.aspx");
}

VB.Net

Default.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim query As String = "SELECT Id, Location, Preference,UpdatedBy FROM HolidayLocationsPref ORDER BY Preference"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvLocations.DataSource = dt
                    gvLocations.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

<WebMethod()>
Public Shared Sub UpdatePreference(ByVal data As List(Of HlidayData))
    Dim preference As Integer = 1
    For Each item In data
        UpdatePreference(item.LocationId, preference, item.Id)
        preference += 1
    Next
End Sub

Private Shared Sub UpdatePreference(ByVal locationId As Integer, ByVal preference As Integer, ByVal id As Integer)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Parameters.AddWithValue("@Id", locationId)
                cmd.Parameters.AddWithValue("@Preference", preference)
                Dim query As String = ""
                If locationId = id Then
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id"
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session("User").ToString())
                Else
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id"
                End If

                cmd.CommandText = query
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Using
End Sub

Public Class HlidayData
    Public Property Id As Integer
    Public Property LocationId As Integer
    Public Property Location As String
    Public Property Preference As Integer
    Public Property UpdatedBy As String
End Class

Login.aspx.vb

Protected Sub Logging(ByVal sender As Object, ByVal e As EventArgs)
    Session("User") = txtUserName.Text
    Response.Redirect("Default.aspx")
End Sub

Screenshot

Nevermind, I figured that out :

    Protected Sub gvLocations_RowDataBound(sender As Object, e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim myLabel As Label = CType(e.Row.FindControl("lblUp"), Label)
            Dim text As String = myLabel.Text
            If text = "" Then
                e.Row.BackColor = Nothing
            Else
                e.Row.BackColor = Drawing.Color.LightYellow
            End If
        End If
    End Sub