SQL CRUD Operations Using DataList Control in ASP.Net

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

Step 1: SQL Database

First create a table in a database store to insert a data value into an EmployeeData table as follows.

Table: EmployeeData

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

Stored Procedure:

Also create an insert procedure, a select procedure, an update procedure and a delete procedure as in the following.  

1. Select procedure for DataList 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 2: Maintain database connection

Provide the database connection string inside the project's Web.Config as in the following:

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

Step 3: UI design

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

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

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

Also add two button controls and TextBox controls inside the DataList data row update and delete with DataList event Item commands. The following is the UI design code.

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

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

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <div>
        <fieldset style="width: 269px" ><legend><b>DataList Example</b></legend>
            <div style="width: 250px; background-color: #99FF66;">
        <asp:Table runat="server">
            <asp:TableRow>
                <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server"></asp:TextBox ></asp:TableCell>
            </asp:TableRow>

            <asp:TableRow>
                <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox ID="txtContact" runat="server"></asp:TextBox></asp:TableCell>
            </asp:TableRow>

            <asp:TableRow>
                <asp:TableCell>Email id</asp:TableCell><asp:TableCell><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></asp:TableCell>
            </asp:TableRow>

            <asp:TableRow>
                <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnSave" Text="Add Record" runat="server" OnClick="btnSave_Click" /></asp:TableCell>
            </asp:TableRow>
        </asp:Table>
        </div>
        </fieldset>
        <br />
    <fieldset style="width: 535px"><legend><b>Employee Information</b></legend>
    <div style="background-color: #66FF66">
    <asp:DataList ID="DataListEmp" runat="server"
             DataKeyField="EmpID"
             OnDeleteCommand="DataListEmp_DeleteCommand"
             OnEditCommand="DataListEmp_EditCommand"
             OnUpdateCommand="DataListEmp_UpdateCommand"
             OnCancelCommand="DataListEmp_CancelCommand" Width="527px" >
            <HeaderTemplate>
            <table><tr style="background-color: #800000; color: #FFFFFF">
            <th>Name</th><th>Contact</th><th>Email ID</th><th>Action</th></tr>
            </HeaderTemplate>
            <ItemTemplate>
            <tr >
            <td><%# DataBinder.Eval(Container.DataItem, "EmpName")%></td>
            <td><%# DataBinder.Eval(Container.DataItem,"Contact")%></td>
            <td><%# DataBinder.Eval(Container.DataItem, "EmailId")%></td>
            <td><asp:Button ID="imgbtnedit" runat="server" Text="Edit"  ToolTip="Edit" CommandName="Edit"/></td>
            <td><asp:Button ID="btndelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete"/></td>
            </tr>
            </ItemTemplate>
            <EditItemTemplate>
            <tr>
             <td><asp:TextBox BackColor="Yellow" Font-Bold="true"  ID="txtName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox></td>
             <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtContact" runat="server" Text='<%# Eval("Contact") %>'></asp:TextBox></td>
             <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtEmail" runat="server" Text='<%# Eval("EmailId") %>'></asp:TextBox></td>
             <td><asp:Button ID="btnupdate" runat="server"  ToolTip="Update" Text="Update" CommandName="Update" /></td>
             <td><asp:Button ID="btncancel" runat="server"  ToolTip="Cancel" Text="Cancel" CommandName="Cancel" /></td>
            </tr>
            </EditItemTemplate>
        </asp:DataList>
        </div>
        </fieldset>
        </div>
</asp:Content>

Step 4: UI code

Here is all about an operation on a DataList row records.

In this first write code for the TextBox value insertion into the SQL table by the preceding Insert Procedure, then write code for the inserted record display in the DalaList control. Write the code for two buttons, one is the delete and the second is an update button code.

Now create an update command, an edit command, a cancel command, an Item command according to the following Figure 3 and also maintain a datakeyfield in the property data section as in Figure 2.


Figure 2: Add Data Key Field


Figure 3: Add Action Command

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_DataList : System.Web.UI.Page
{
    string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetEmpDataList();
        }
    }
    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;

                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();

                Clear();
                Response.Write("<script type=\"text/javascript\">alert('Record Inserted Successfully');</script>");
                GetEmpDataList();
            }
        }
    }
    void Clear()
    {
        txtName.Text = String.Empty;
        txtContact.Text = String.Empty;
        txtEmail.Text = String.Empty;
    }
    private void GetEmpDataList()
    {
        using (SqlConnection con = new SqlConnection(connection))
        {
            SqlDataAdapter sd = new SqlDataAdapter("sp_FillData", con);
            sd.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();

            sd.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                DataListEmp.DataSource = dt;
                DataListEmp.DataBind();
            }
         }
    }
    protected void DataListEmp_DeleteCommand(object source, DataListCommandEventArgs e)
    {
       int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());

       using (SqlConnection con = new SqlConnection(connection))
       {
           using (SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@EmpID",EmpID);

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

               Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully');</script>");
               GetEmpDataList();
           }
       }
    }
    protected void DataListEmp_EditCommand(object source, DataListCommandEventArgs e)
    {
        DataListEmp.EditItemIndex = e.Item.ItemIndex;
        GetEmpDataList();
    }
    protected void DataListEmp_CancelCommand(object source, DataListCommandEventArgs e)
    {
        DataListEmp.EditItemIndex = -1;
        GetEmpDataList();
    }
    protected void DataListEmp_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());

        TextBox txtName = (TextBox)e.Item.FindControl("txtName");
        TextBox txtContact = (TextBox)e.Item.FindControl("txtContact");
        TextBox txtEmail = (TextBox)e.Item.FindControl("txtEmail");

        using (SqlConnection con = new SqlConnection(connection))
        {
            using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@EmpID", EmpID);
                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();

                Clear();
                Response.Write("<script type=\"text/javascript\">alert('Record Update Successfully');</script>");
                DataListEmp.EditItemIndex = -1;
                GetEmpDataList();
            }
        }
    }
}

Step 5: Browser Side

Now run your new page in the browser.


Figure 4: Fill Record and save


Figure 5: Display Record

In this information two employee contact numbers are the same. So that it's updated with the edit button as in the following figure.


Figure 6: Record Update and Delete

Click the Edit button and replace the new contact number in the TextBox and update that record contact as in the following. If any record is deleted then press the delete button as in the following:


Figure 7: contact Update and 1 record delete.

I hope you understood how to work with the DataList control Action Command Event and SQL Procedures.

Have a nice day.