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

SheoNarayan
Posted by in ASP.NET category on for Intermediate level | Points: 250 | Views : 12110 red flag
Rating: 5 out of 5  
 1 vote(s)

To perform data manipulation (CRUD - Create, Read, Update and Delete) in the ListView, we can follow this approach.

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.


In the above code snippet, we have a ListView with LayoutTemplate (used to specify the Layout), ItemTemplate – used to specify the items, EditItemTemplate – used to specify the item in edit mode, InsertItemTemplate – used to specify item in Insert mode. We have also kept Edit, Update, Cancel, Insert and Delete button in different templates accordingly. On click of Edit, Cancel, Update, Insert methods different events like OnItemEditing (fires EditRecord method), OnItemCanceling (fires CancelEditRecord) , OnItemUpdating(fires UpdateRecord) and OnItemInserting (fires InsertRecord) event fires because each button has respective CommandName.

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.

Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)