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>
