How to perform CRUD (Create Read Update & Delete) operation in ListView.

ListView

ListView control is the new and improved control introduced from ASP.NET 3.5 version. It is a template ased control that gives us better control over the layout. It can be treated as an alternative to the DataList control.

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In the previous article, we learnt about How to get the primary key value of the GridView rows in JavaScript or
popup page? In this article, we shall learn how to perform CRUD (Create Read Update & Delete) operation in
ListView.

 Video of this article

 You can watch the video of this article at  http://www.fundoovideo.com/video/120/how-to-perform-crud-operation-in-listview

ASPX PAGE

<p><asp:Label ID="lblMessage" runat="server" EnableViewState="false" /></p>
<asp:ListView runat="server" ID="ListView1" ItemPlaceholderID="itemPlaceHolder1"
OnItemEditing="EditRecord" OnItemCanceling="CancelEditRecord" DataKeyNames="AutoId"
OnItemInserting="InsertRecord" OnItemUpdating="UpdateRecord" InsertItemPosition="LastItem"
OnItemDeleting="DeleteRecord">
<LayoutTemplate>
<table border="1">
<tr>
<th>
Delete?
</th>
<th>
AutoId
</th>
<th>
First Name
</th>
<th>
Last Name
</th>
<th>
Age
</th>
<th>
Active
</th>
<th>
Edit
</th>
</tr>
<asp:PlaceHolder ID="itemPlaceHolder1" runat="server"></asp:PlaceHolder>
</table>
</LayoutTemplate>
 
<ItemTemplate>
<tr>
<td>
<span onclick="return confirm('Are you sure to delete?')">
<asp:LinkButton ID="lnkDel" runat="server" Text="Delete?"ForeColor="Red" CommandName="Delete" />
</span>
</td>
<td>
<%# Eval("AutoID") %>
</td>
<td>
<%# Eval("FirstName") %>
</td>
<td>
<%# Eval("LastName") %>
</td>
<td>
<%#Eval("Age") %>
</td>
<td>
<%# Eval("Active") %>
</td>
<td>
<asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" CommandName="Edit" />
</td>
</tr>
 
</ItemTemplate>
 
<EditItemTemplate>
<tr style="background-color: #efefef;">
<td colspan="7">
First name:
<asp:TextBox ID="txtEFName" runat="server" Text='<%# Eval("FirstName") %>' />
<br />
Last name:
<asp:TextBox ID="txtELName" runat="server" Text='<%#Eval("LastName") %>' />
<br />
Last name:
<asp:TextBox ID="txtEAge" runat="server" Text='<%# Eval("Age") %>' Columns="3" />
<br />
Active:
<asp:DropDownList ID="dropEACtive" runat="server" SelectedValue='<%# Eval("Active") %>'>
<asp:ListItem Text="Yes" Value="True" />
<asp:ListItem Text="No" Value="False" />
</asp:DropDownList>
<asp:LinkButton ID="lnkUpdate" runat="server" Text="Update" CommandName="Update" />
<asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" />
</td>
</tr>
 
</EditItemTemplate>
 
<InsertItemTemplate>
<tr style="background-color: yellow;">
<td>
&nbsp;
</td>
<td>
&nbsp;
</td>
<td>
<asp:TextBox ID="txtFName" runat="server" />
</td>
<td>
<asp:TextBox ID="txtLName" runat="server" />
</td>
<td>
<asp:TextBox ID="txtAge" runat="server" Columns="3" />
</td>
<td>
<asp:DropDownList ID="dropActive" runat="server">
<asp:ListItem Text="Yes" Value="True" />
<asp:ListItem Text="No" Value="False" />
</asp:DropDownList>
</td>
<td>
<span onclick="return confirm('Are you sure to insert?')">
<asp:LinkButton ID="lnkInser" runat="server" Text="Insert" CommandName="Insert" />
</span>
</td>
</tr>
</InsertItemTemplate>
</asp:ListView>
 

If you have gone through my earlier article of GridView you must be familiar with ItemTemplates. In ListView we have some different templates and I am going to explain those.

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateData();
}
}

protected void InsertRecord(object sender, ListViewInsertEventArgs e)
{
ListViewItem item = e.Item;
TextBox tF = (TextBox)item.FindControl("txtFName");
TextBox tL = (TextBox)item.FindControl("txtLName");
TextBox tA = (TextBox)item.FindControl("txtAge");
DropDownList dropEA = (DropDownList)item.FindControl("dropActive");
using (SqlConnection conn = new SqlConnection(_connStr))
{
string sql = "Insert Into PersonalDetail (FirstName, LastName, Age, Active) VALUES (" + "@FirstName, @LastName, @Age, @Active)";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@FirstName", tF.Text.Trim());
cmd.Parameters.AddWithValue("@LastName", tL.Text.Trim());
cmd.Parameters.AddWithValue("@Age", tA.Text.Trim());
cmd.Parameters.AddWithValue("@Active", dropEA.SelectedValue);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lblMessage.Text = "Record inserted successfully !";
this.PopulateData();
}

protected void EditRecord(object sender, ListViewEditEventArgs e)
{
ListView1.EditIndex = e.NewEditIndex;
this.PopulateData();
}
 
protected void UpdateRecord(object sender, ListViewUpdateEventArgs e)
{
int autoId = int.Parse(ListView1.DataKeys[e.ItemIndex].Value.ToString());
ListViewItem item = ListView1.Items[e.ItemIndex];
TextBox tF = (TextBox)item.FindControl("txtEFName");
TextBox tL = (TextBox)item.FindControl("txtELName");
TextBox tA = (TextBox)item.FindControl("txtEAge");
DropDownList dropEA = (DropDownList)item.FindControl("dropEActive");
using (SqlConnection conn = new SqlConnection(_connStr))
{
string sql = "Update PersonalDetail set FirstName = @FirstName, LastName=@LastName, Age= @Age, Active = @Active" + " where AutoId = @AutoId";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@FirstName", tF.Text.Trim());
cmd.Parameters.AddWithValue("@LastName", tL.Text.Trim());
cmd.Parameters.AddWithValue("@Age", tA.Text.Trim());
cmd.Parameters.AddWithValue("@Active", dropEA.SelectedValue);
cmd.Parameters.AddWithValue("@AutoId", autoId);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lblMessage.Text = "Record updated successfully !";
ListView1.EditIndex = -1;
// repopulate the data
this.PopulateData();
}
 
protected void CancelEditRecord(object sender, ListViewCancelEventArgs e)
{
ListView1.EditIndex = -1;
this.PopulateData();
}

protected void DeleteRecord(object sender, ListViewDeleteEventArgs e)
{
var autoid = ListView1.DataKeys[e.ItemIndex].Value.ToString();
using (SqlConnection conn = new SqlConnection(_connStr))
{
string sql = "Delete from PersonalDetail " + " where AutoId = @AutoId";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@AutoId", autoid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lblMessage.Text = "Record delete successfully !";
// repopulate the data
this.PopulateData();
}
 
private void PopulateData()
{
DataTable table = new DataTable();
using (SqlConnection conn = new SqlConnection(_connStr))
{
string sql = "Select * from PersonalDetail";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(table);
}
}
}
ListView1.DataSource = table;
ListView1.DataBind();
}
 

In the code behind, the PopulateData method retrieves the records from the database and populates to the ListView. On clicking of the Edit button, ListView is brought into the edit mode (because of EditRecord method) as displayed in the picture below. Clicking on Cancel brings the ListView in the normal mode. Update button fires UpdateRecord method that gets the AutoId (primary key value) and then finds the respective TextBoxes and DropDownList, uses ADO.NET to update the record into the database.

Similarly, InsertRecord method finds all the respective TextBoxes from the InsertItemTemplate and uses their values into the insert SQL Statements and saves into the database.

OUTPUT

Thanks for reading, hope you liked it.

Keep reading my forth coming articles. To read my series of articles on ASP.NET,click here.