Create Table structure
First we are going to create table in sql serverCREATE TABLE [dbo].[emp]( [eno] [int] NULL, [empname] [varchar](50) NULL, [sal] [bigint] NULL )
I have design webpage like below
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h3>
List View Example - With all operations Edit / Delete / Selected record View / Sorting / List view Paging </h3>
<br />
<asp:ListView ID="ListView1" runat="server" OnItemCommand="ListView1_ItemCommand"
InsertItemPosition="LastItem" OnItemEditing="ListView1_ItemEditing" OnItemCanceling="ListView1_ItemCanceling"
OnSorting="ListView1_Sorting" ItemPlaceholderID="itemPlaceholder"
onselectedindexchanged="ListView1_SelectedIndexChanged">
<LayoutTemplate>
<table border="0" cellpadding="1" width="800">
<tr style="background-color: #ACCDB9;">
<th style="color: #FFFFFF;">
<asp:LinkButton ID="lnkeno" runat="server" CommandName="Sort" CommandArgument="eno">Eno</asp:LinkButton>
</th>
<th>
<asp:LinkButton ID="lnkempname" runat="server" CommandName="Sort" CommandArgument="empname">Employee name</asp:LinkButton>
</th>
<th>
<asp:LinkButton ID="lnksal" runat="server" CommandName="Sort" CommandArgument="sal">Salary</asp:LinkButton>
</th>
<th>
Edit / Delete
</th>
<th>
View Details
</th>
</tr>
<tr id="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lbleno" runat="server" Text='<%#Eval("eno")%>'></asp:Label>
</td>
<td>
<asp:Label ID="lblempname" runat="server" Text='<%#Eval("empname")%>'></asp:Label>
</td>
<td>
<asp:Label ID="lblsal" runat="server" Text='<%#Eval("sal")%>'></asp:Label>
</td>
<td>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
<asp:LinkButton ID="lnkDelete" runat="server" CommandName="Del" OnClientClick="return confirm('Are you sure you want to delete this record?');"> Delete</asp:LinkButton>
</td>
<td>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="show" Text="View details"></asp:LinkButton>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr style="background-color: #E9E9E9;">
<td>
<asp:Label ID="lbleno" runat="server" Text='<%#Eval("eno")%>'></asp:Label>
</td>
<td>
<asp:Label ID="lblempname" runat="server" Text='<%#Eval("empname")%>'></asp:Label>
</td>
<td>
<asp:Label ID="lblsal" runat="server" Text='<%#Eval("sal")%>'></asp:Label>
</td>
<td>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
<asp:LinkButton ID="lnkDelete" runat="server" CommandName="Del">Delete</asp:LinkButton>
</td>
<td>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="show" Text="view details"></asp:LinkButton>
</td>
</tr>
</AlternatingItemTemplate>
<EditItemTemplate>
<td>
<asp:TextBox ID="txteno" runat="server" Text='<%#Eval("eno") %>' Enabled="false"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtempname" runat="server" Text='<%#Eval("empname") %>'></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtsal" runat="server" Text='<%#Eval("sal") %>'></asp:TextBox>
</td>
<td colspan="2">
<asp:LinkButton ID="lnkUpdate" runat="server" CommandName="Upd">Update</asp:LinkButton>
<asp:LinkButton ID="lnkCancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
</td>
</tr>
</EditItemTemplate>
<InsertItemTemplate>
<tr id="Tr1" runat="server">
<td>
<asp:TextBox ID="txtIeno" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtIempname" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtIsal" runat="server"></asp:TextBox>
</td>
<td colspan="2">
<asp:Button ID="InsertButton" runat="server" CommandName="Ins" Text="Insert" />
</td>
</tr>
</InsertItemTemplate>
</asp:ListView>
<br />
<br />
<asp:DataPager ID="ItemDataPager" runat="server" PageSize="3" OnPreRender="ItemDataPager_PreRender"
PagedControlID="ListView1">
<Fields>
<asp:NextPreviousPagerField ShowFirstPageButton="True" ShowNextPageButton="False" />
<asp:NumericPagerField />
<asp:NextPreviousPagerField ShowLastPageButton="True" ShowPreviousPageButton="False" />
</Fields>
</asp:DataPager>
<br />
<br />
<br />
<table width="300">
<tr>
<td colspan="2">
<b>Get values from list view and display details</b>
</td>
</tr>
<tr>
<td>
Employee no
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Employee name
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Employee sal
</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
How to insert / Update / Delete record thorugh List View
Here I have insert records to the database usinb ListView1 ItemCommand event. In this event I have check whether user is click which button based on that I have update / delete records.
protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e)
{
//Get command name and process based on that
//Get the commmand name and verify that is insert if yes then this block excecute
if (e.CommandName == "Ins")
{
TextBox txtIeno = (TextBox)e.Item.FindControl("txtIeno");
TextBox txtIempname = (TextBox)e.Item.FindControl("txtIempname");
TextBox txtIsal = (TextBox)e.Item.FindControl("txtIsal");
string insertCommand = "Insert into emp values('" + txtIeno.Text + "', '" + txtIempname.Text + "', '" + txtIsal.Text + "')";
ExecuteCommand(insertCommand);
LoadListView();
}
//Get the commmand name and verify that is udpate if yes then this block excecute
if (e.CommandName == "Upd")
{
TextBox txteno = (TextBox)e.Item.FindControl("txteno");
TextBox txtempname = (TextBox)e.Item.FindControl("txtempname");
TextBox txtsal = (TextBox)e.Item.FindControl("txtsal");
string updateCommand = "Update emp set empname='" + txtempname.Text + "', sal='" + txtsal.Text + "' where eno=" + Convert.ToInt32(txteno.Text) + ";";
ExecuteCommand(updateCommand);
ListView1.EditIndex = -1;
LoadListView();
}
//Get the commmand name and verify that is delete if yes then this block excecute
if (e.CommandName == "Del")
{
Label txteno = (Label)e.Item.FindControl("lbleno");
string deleteCommand = "delete from emp where eno=" + Convert.ToInt32(txteno.Text);
ExecuteCommand(deleteCommand);
LoadListView();
}
//Get the commmand name and verify that is view if yes then this block excecute and display details in the textbox
if (e.CommandName == "show")
{
//get the selected item
ListViewDataItem selectedItem = e.Item as ListViewDataItem;
TextBox1.Text = ((Label)selectedItem.FindControl("lbleno")).Text;
TextBox2.Text = ((Label)selectedItem.FindControl("lblempname")).Text;
TextBox3.Text = ((Label)selectedItem.FindControl("lblsal")).Text;
}
}
How to sorting listview data
Using sorting method I have sorted data like below
//Sorting Listview data using sorting event i have verify here previous status ascending or descending
protected void ListView1_Sorting(object sender, ListViewSortEventArgs e)
{
DataTable dataTable = (DataTable)Session["dt"];
if (dataTable != null)
{
Session["sort"] = "Y";
//Create new dataview instance and pass datatable
DataView dataView = new DataView(dataTable);
//get sort direction from the get sort direction method
string sortDirection = GetSortDirection();
//Sort dataview data based on the sort directin value
dataView.Sort = e.SortExpression + " " + sortDirection;
//Assign datasource and bind data to List view
ListView1.DataSource = dataView;
ListView1.DataBind();
}
}
Complete Source for code behind
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da;
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
//Intially load some data in the list view from database
if (!Page.IsPostBack)
{
LoadListView();
}
}
void LoadListView()
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("select * from emp", sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
Session["dt"] = dt;
ListView1.DataSource = dt;
ListView1.DataBind();
}
catch (Exception ex)
{
}
finally
{
sqlcon.Close();
}
}
//Set & get sort state in viewstate
private string GVSortDirection
{
get { return ViewState["SortDirection"] as string ?? "DESC"; }
set { ViewState["SortDirection"] = value; }
}
private string GetSortDirection()
{
switch (GVSortDirection)
{
//If previous sort direction if ascending order then assign new direction as descending order
case "ASC":
GVSortDirection = "DESC";
break;
//If previous sort direction if descending order then assign new direction as ascending order
case "DESC":
GVSortDirection = "ASC";
break;
}
return GVSortDirection;
}
protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e)
{
//Get command name and process based on that
//Get the commmand name and verify that is insert if yes then this block excecute
if (e.CommandName == "Ins")
{
TextBox txtIeno = (TextBox)e.Item.FindControl("txtIeno");
TextBox txtIempname = (TextBox)e.Item.FindControl("txtIempname");
TextBox txtIsal = (TextBox)e.Item.FindControl("txtIsal");
string insertCommand = "Insert into emp values('" + txtIeno.Text + "', '" + txtIempname.Text + "', '" + txtIsal.Text + "')";
ExecuteCommand(insertCommand);
LoadListView();
}
//Get the commmand name and verify that is udpate if yes then this block excecute
if (e.CommandName == "Upd")
{
TextBox txteno = (TextBox)e.Item.FindControl("txteno");
TextBox txtempname = (TextBox)e.Item.FindControl("txtempname");
TextBox txtsal = (TextBox)e.Item.FindControl("txtsal");
string updateCommand = "Update emp set empname='" + txtempname.Text + "', sal='" + txtsal.Text + "' where eno=" + Convert.ToInt32(txteno.Text) + ";";
ExecuteCommand(updateCommand);
ListView1.EditIndex = -1;
LoadListView();
}
//Get the commmand name and verify that is delete if yes then this block excecute
if (e.CommandName == "Del")
{
Label txteno = (Label)e.Item.FindControl("lbleno");
string deleteCommand = "delete from emp where eno=" + Convert.ToInt32(txteno.Text);
ExecuteCommand(deleteCommand);
LoadListView();
}
//Get the commmand name and verify that is view if yes then this block excecute and display details in the textbox
if (e.CommandName == "show")
{
//get the selected item
ListViewDataItem selectedItem = e.Item as ListViewDataItem;
TextBox1.Text = ((Label)selectedItem.FindControl("lbleno")).Text;
TextBox2.Text = ((Label)selectedItem.FindControl("lblempname")).Text;
TextBox3.Text = ((Label)selectedItem.FindControl("lblsal")).Text;
}
}
void ExecuteCommand(string qry)
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand(qry, sqlcon);
sqlcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
sqlcon.Close();
}
}
//Call editing event when user click edit
protected void ListView1_ItemEditing(object sender, ListViewEditEventArgs e)
{
ListView1.EditIndex = e.NewEditIndex;
LoadListView();
}
//Cancel editing when user do want to update details
protected void ListView1_ItemCanceling(object sender, ListViewCancelEventArgs e)
{
ListView1.EditIndex = -1;
LoadListView();
}
//Sorting Listview data using sorting event i have verify here previous status ascending or descending
protected void ListView1_Sorting(object sender, ListViewSortEventArgs e)
{
DataTable dataTable = (DataTable)Session["dt"];
if (dataTable != null)
{
Session["sort"] = "Y";
//Create new dataview instance and pass datatable
DataView dataView = new DataView(dataTable);
//get sort direction from the get sort direction method
string sortDirection = GetSortDirection();
//Sort dataview data based on the sort directin value
dataView.Sort = e.SortExpression + " " + sortDirection;
//Assign datasource and bind data to List view
ListView1.DataSource = dataView;
ListView1.DataBind();
}
}
protected void ItemDataPager_PreRender(object sender, EventArgs e)
{
try
{
if (Session["sort"] == null)
{
LoadListView();
}
else
{
Session.Remove("sort");
}
}
catch (Exception ex)
{
}
}
protected void ListView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
No comments:
Post a Comment