CRUD Operations Using ASP.Net ListView

This is all about operations on SQL tables using Stored Procedures, so I will explain step-by-step how to insert, select, edit and delete operations with a ListView control.

Step 1

In a SQL Database first create a table in a database store to insert a data value into a table as follows.

Create Table EmployeeData.

Create Table EmployeeData
(
   EmpID int identity (1,1) Primary Key,
   EmpName varchar(30),
   Contact nchar(15),
   EmailId nvarchar(50)
)

In this new table the EmpID column is an auto-increment defined for Employee Identity.

Step 2

In Visual Studio create a UI design using the following procedure:

• Go to Solution Explorer.
• Right-click on the project and click the Add tab.
• Click the Add New Item as in the following:

Figure 1: Add Web Form

Now I will write the design code inside ListView.aspx. In this page add some TextBox controls, Buttons and a ListView Control.

First create a TextBox Control, Button Control, ListView Control and hidden field control. First add the TextBox control for the data to be filled on the UI page.

Also add the ListView control for the Bind Employee record from the SQL Database and display it in the ListView Control. Also add two button controls inside the ListView control for the ListView data row delete and edit. The following is the UI design code.

<%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="ListView.aspx.cs" Inherits="UI_ListView" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server" >

     <div>
        <fieldset style="width: 300px"><legend>ListView Example</legend>
        <asp:Table runat="server" >
            <asp:TableRow>
                <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtName"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtContact"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>Email</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtEmail"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
           <asp:TableRow>
               <asp:TableCell></asp:TableCell>
               <asp:TableCell>
                   <asp:Button runat="server" ID="btnSave" Text="Save" OnClick="btnSave_Click" />
                   <asp:Button runat="server" ID="btnUpdate" Text="Update" OnClick="btnUpdate_Click" />
               </asp:TableCell>
       </asp:TableRow>
      </asp:Table>
       </fieldset>
         </div>

       <asp:HiddenField ID="hfRecord" runat="server" />

<h3>Employee Information</h3>

        <asp:ListView ID="ListEmployee" runat="server" OnItemCommand="ListEmployee_ItemCommand" DataKeyNames="EmpID" >
        <itemtemplate>
       <table >
           <tr>
                        <td style =" width : 100px">
                            <b>No. :</b>
                            <%#Eval("EmpID") %>
                        </td>
                        <td style =" width : 200px">
                            <b>Name :</b>
                            <%#Eval("EmpName") %>
                        </td>
                        <td style =" width : 200px">
                            <b>Contact :</b>
                            <%#Eval("Contact") %>
                        </td >
                        <td style =" width : 250px">
                            <b>Email :</b>
                            <%#Eval("EmailId") %>
                        </td>
                    <td>
                        <asp:Button ID="btndel" runat="server" Text="Delete" tooltip="Delete a record" onclientclick="javascript:return confirm('Are you sure to delete record?')" CommandName="EmpDelete" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "EmpID") %>' />
                        <asp:Button ID="btnupdt" runat="server" Text="Edit" tooltip="Update a record" CommandName="EmpEdit" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "EmpID") %>' />
                    </td>
            </tr>
       </table>
       </itemtemplate>
    </asp:ListView>
</asp:Content>

Step 3

In the database, again go to the SQL Server and create an Insert procedure, a Delete procedure, a Select procedure and an Update procedure as in the following.

 

1. Select procedure for ListView control
   

   create procedure sp_FillData
   As
      Begin
      set nocount on;
      select EmpID, EmpName, Contact, EmailID from EmployeeData
   End

2. Insert procedure
   

   create procedure sp_InsertEmployeeData
      @EmpName varchar(30),
      @Contact nchar(15),
      @EmailId nvarchar(50)
   As
      Begin
      set nocount on;
      Insert into dbo.EmployeeData
         (EmpName,Contact,EmailId)
      values
         (@EmpName,@Contact,@EmailId)
   End

3. Delete procedure
   

   Create procedure sp_DeleteEmployeeData
      @EmpID int
   As
      Begin
      set nocount on;
      Delete from EmployeeData where EmpID=@EmpID
   End

4. Select procedure
   

   create procedure sp_SelectEmployeeData
      @EmpID int
   As
      Begin
      set nocount on;
      select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID
   End

5. Update procedure
   

   Create procedure sp_UpdateEmployeeData
      @EmpID int,
      @EmpName varchar(30),
      @Contact nchar(15),
      @EmailId nvarchar(50)
   As
      Begin
      set nocount off;
      UPDATE Employeedata SET EmpName=@EmpName,Contact=@Contact,EmailId=@EmailId WHERE EmpID=@EmpID
   End

Step 4

Inside the project's Web.Config, provide the database connection string as in the following:

<connectionStrings>
   <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>
</connectionStrings>

Step 5

Now go to the UI code side section. In this first write code for the TextBox value insertion in the SQL table by the preceding Insert Procedure, then write code for the inserted record display in the ListView control. Write the code for two buttons, one the delete and the second is an edit button code. Write it for the data delete and update in the database records. Both code writes an Itemcommand on the ListView Control. An Itemcommand is created as in the following figure:


Figure 2: Add an Item Command

Create two action commands, an EmpEdit for a record update and an EmpDelete for a record delete, inside the ItemCommand Event.

This is a UI back side code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class UI_ListView : System.Web.UI.Page
{
    string connection = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetEmployeeDetail();
        }
    }
    void GetEmployeeDetail()
    {
        SqlConnection con = new SqlConnection(connection);

        SqlCommand cmd = new SqlCommand("sp_FillData", con);
        cmd.CommandType = CommandType.StoredProcedure;

        con.Open();
        ListEmployee.DataSource = cmd.ExecuteReader();
        ListEmployee.DataBind();
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(connection))
        {
            using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
                cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
                cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
                cmd.ExecuteNonQuery();
                con.Close();

                txtName.Text = String.Empty;
                txtContact.Text = String.Empty;
                txtEmail.Text = String.Empty;
            }
        }
        GetEmployeeDetail();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpID", Convert.ToInt32(hfRecord.Value));
        cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
        cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
        cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        GetEmployeeDetail();

        btnSave.Visible = true;
        btnUpdate.Visible = false;

        hfRecord.Value = string.Empty;
        txtName.Text = String.Empty;
        txtContact.Text = String.Empty;
        txtEmail.Text = String.Empty;
    }
    protected void ListEmployee_ItemCommand(object sender, ListViewCommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case ("EmpDelete"):
                int EmpID = Convert.ToInt32(e.CommandArgument);
                deleteEmployee(EmpID);
                break;
            case ("EmpEdit"):
                EmpID = Convert.ToInt32(e.CommandArgument);
                UpdateEmployeeDetail(EmpID);
                break;
        }
    }
   void deleteEmployee(int EmpID)
    {
        SqlConnection con = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpID", EmpID);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        GetEmployeeDetail();
   }
    void UpdateEmployeeDetail(int EmpID)
    {
        SqlConnection con = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("sp_SelectEmployeeData", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@EmpID", EmpID);

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            hfRecord.Value = dr["EmpID"].ToString();
            txtName.Text = dr["EmpName"].ToString();
            txtContact.Text = dr["Contact"].ToString();
            txtEmail.Text = dr["EmailId"].ToString();
        }
        dr.Dispose();

        con.Close();
        btnSave.Visible = false;
        btnUpdate.Visible = true;
        GetEmployeeDetail();
    }

}

Step 6

For the browser side, run your new page in the web browser.


Figure 3: Fill Record and Save

Press the Save button and save the data into the table and the inserted record is displayed in the browser using the ListView control as in the following.


Figure 4: Display Inserted Record


Figure 5: Check in Database

Now delete record number 68 by deleting the button.


Figure 6: Record Delete Message

Now you will see that the deleted record is not available in the database and not displayed on the page.

Now Update Record Process Record Number 66. In this change the Emailid.


Figure 7: Record Update

And also check in the data table.


Figure 8: Record Update and Delete in SQL

As you can see, inside the database, the deleted record is not there and the Employee Emailid is updated.

I hope you understood how to work with the ListView control and SQL Procedures.

Have a nice day.