How to Rearrange Rows in a Gridview using Drag and Drop?

Gridview is an awesome Control to display data in Tabular shape. Let’s assume while displaying records in a Gridview Customer wants to re-order the records. In such scenario row Drag and Drop feature works great. In back-end to maintain Order of records here I am with a column in Excel. Using order by with a SQL query I am displaying the records in Sequence.

In this example I am with a Gridview control “gvLocations”. Which displaying data from an excel file using OLEDB connection. Code for Gridview is as below.

Gridview

<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="Order" HeaderText="Order" ItemStyle-Width="100" />
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Update Preference" runat="server" OnClick="UpdatePreference" />

To implement Drag and Drop over a Gridview. In the below section I am using jQuery. To add this in your app, embed the jQuery library files in your app and Copy the below JQ Function to the header part of your aspx page.

Drag and Drop using Jquery

<script type="text/javascript" src="https://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">
$(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) {
ui.item.removeClass("selected");
},
receive: function (e, ui) {
$(this).find("tbody").append(ui.item);
}
});
});
</script>

In Code behind I am 3 subroutines BindGrid, UpdatePreference and UpdatePreference with different signature. Whie page load if page is not postback I am calling the BindGrid() subroutine. Which help to display records in the Grid. Drag and Drop happening using the above Jquery Code. Now after re-ordering while I am updating the Excel. For this I have a button “Update Preference” in my aspx page. Onclick of this button I am calling UpdatePreference() subroutine. From 2 UpdatePreference() subroutine first one is responsible to extract id and order. Then from this I am calling the another UpdatePreference(locationId, order). Which updating records in Excel File using the Loop defined at UpdatePreference.

Code-behind

Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Imports System.Configuration

Partial Class _Default
Inherits System.Web.UI.Page

Dim FilePath As String = "C:\Users\biswabhusan_panda\Documents\Visual Studio 2013\WebSites\KShop\Files\temp.xlsx"
Dim Extension As String = ".xlsx"

Dim ConStr As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString()

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

Private Sub BindGrid(ByVal FilePath As String, ByVal Extension As String)

ConStr = String.Format(ConStr, FilePath)

Dim connExcel As New OleDbConnection(ConStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()

cmdExcel.Connection = connExcel

'Get the name of First Sheet 
connExcel.Open()

Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

connExcel.Close()

'Read Data from First Sheet 
connExcel.Open()

cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"

oda.SelectCommand = cmdExcel
oda.Fill(dtbl)
connExcel.Close()

'Bind Data to GridView 
gvLocations.DataSource = dtbl
gvLocations.DataBind()

End Sub

Protected Sub UpdatePreference(sender As Object, e As EventArgs)
Dim locationIds As String() = (From p In Request.Form("LocationId").Split(",") Select p).ToArray()
Dim order As Integer = 1

For Each locationId As Integer In locationIds
Me.UpdatePreference(locationId, order)
order += 1
Next

Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Private Sub UpdatePreference(locationId As Integer, order As Integer)

ConStr = String.Format(ConStr, FilePath)

Dim connExcel As New OleDbConnection(ConStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dtbl As New DataTable()

cmdExcel.Connection = connExcel

'Get the name of First Sheet 
connExcel.Open()

Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

connExcel.Close()

'Read Data from First Sheet
connExcel.Open()

cmdExcel.CommandText = "UPDATE [" & SheetName & "] SET Order='" & order & "' WHERE ID=" & locationId & ""
cmdExcel.ExecuteNonQuery()
connExcel.Close()

End Sub
End Class

Web.Config

<connectionStrings>
<!--<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0'" />-->
<add name ="ConnString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0'"/>
</connectionStrings>