Post
Ask Question

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

  1. Create Table EmployeeData (  
  2. EmpID int identity (1, 1) Primary Key,  
  3. EmpName varchar(30),  
  4. Contact nchar(15),  
  5. EmailId nvarchar(50)  
  6. )  
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.
    1. create procedure sp_FillData  
    2. As  
    3. Begin  
    4. set nocount on;  
    5. select EmpID, EmpName, Contact, EmailID from EmployeeData  
    6. End  
  2. Insert procedure.
    1. create procedure sp_InsertEmployeeData @EmpName varchar(30),  
    2. @Contact nchar(15),  
    3. @EmailId nvarchar(50) As Begin  
    4. set  
    5. nocount on;  
    6. Insert into dbo.EmployeeData (EmpName, Contact, EmailId)  
    7. values  
    8. (@EmpName, @Contact, @EmailId) End  
  3. Delete procedure.
    1. Create procedure sp_DeleteEmployeeData  
    2. @EmpID int  
    3. As  
    4. Begin  
    5. set nocount on;  
    6. Delete from EmployeeData where EmpID=@EmpID  
    7. End  
  4. Select procedure.
    1. create procedure sp_SelectEmployeeData  
    2. @EmpID int  
    3. As  
    4. Begin  
    5. set nocount on;  
    6. select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID  
    7. End  
  5. Update procedure.
    1. Create procedure sp_UpdateEmployeeData @EmpID int,  
    2. @EmpName varchar(30),  
    3. @Contact nchar(15),  
    4. @EmailId nvarchar(50) As Begin  
    5. set  
    6. nocount off;  
    7. UPDATE  
    8. Employeedata  
    9. SET  
    10. EmpName = @EmpName,  
    11. Contact = @Contact,  
    12. EmailId = @EmailId  
    13. WHERE  
    14. EmpID = @EmpID End  

Step 2: Maintain database connection

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

  1. <connectionStrings>  
  2.    <add name="connstring" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
  3. </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:

    Add web form
    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.

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="DataList.aspx.cs" Inherits="UI_DataList" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4.      
  5. </asp:Content>  
  6.   
  7. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  8.     <div>  
  9.         <fieldset style="width: 269px" ><legend><b>DataList Example</b></legend>  
  10.             <div style="width: 250px; background-color: #99FF66;">  
  11.         <asp:Table runat="server">  
  12.             <asp:TableRow>  
  13.                 <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server"></asp:TextBox ></asp:TableCell>  
  14.             </asp:TableRow>  
  15.   
  16.             <asp:TableRow>  
  17.                 <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox ID="txtContact" runat="server"></asp:TextBox></asp:TableCell>  
  18.             </asp:TableRow>  
  19.   
  20.             <asp:TableRow>  
  21.                 <asp:TableCell>Email id</asp:TableCell><asp:TableCell><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></asp:TableCell>  
  22.             </asp:TableRow>  
  23.   
  24.             <asp:TableRow>  
  25.                 <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnSave" Text="Add Record" runat="server" OnClick="btnSave_Click" /></asp:TableCell>  
  26.             </asp:TableRow>  
  27.         </asp:Table>  
  28.         </div>  
  29.         </fieldset>  
  30.         <br />  
  31.     <fieldset style="width: 535px"><legend><b>Employee Information</b></legend>  
  32.     <div style="background-color: #66FF66">  
  33.     <asp:DataList ID="DataListEmp" runat="server"   
  34.              DataKeyField="EmpID"   
  35.              OnDeleteCommand="DataListEmp_DeleteCommand"   
  36.              OnEditCommand="DataListEmp_EditCommand"  
  37.              OnUpdateCommand="DataListEmp_UpdateCommand"   
  38.              OnCancelCommand="DataListEmp_CancelCommand" Width="527px" >  
  39.             <HeaderTemplate>  
  40.             <table><tr style="background-color: #800000; color: #FFFFFF">  
  41.             <th>Name</th><th>Contact</th><th>Email ID</th><th>Action</th></tr>  
  42.             </HeaderTemplate>  
  43.             <ItemTemplate>  
  44.             <tr >  
  45.             <td><%# DataBinder.Eval(Container.DataItem, "EmpName")%></td>  
  46.             <td><%# DataBinder.Eval(Container.DataItem,"Contact")%></td>  
  47.             <td><%# DataBinder.Eval(Container.DataItem, "EmailId")%></td>  
  48.             <td><asp:Button ID="imgbtnedit" runat="server" Text="Edit"  ToolTip="Edit" CommandName="Edit"/></td>  
  49.             <td><asp:Button ID="btndelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete"/></td>  
  50.             </tr>            
  51.             </ItemTemplate>  
  52.             <EditItemTemplate>             
  53.             <tr>  
  54.              <td><asp:TextBox BackColor="Yellow" Font-Bold="true"  ID="txtName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox></td>  
  55.              <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtContact" runat="server" Text='<%# Eval("Contact") %>'></asp:TextBox></td>  
  56.              <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtEmail" runat="server" Text='<%# Eval("EmailId") %>'></asp:TextBox></td>  
  57.              <td><asp:Button ID="btnupdate" runat="server"  ToolTip="Update" Text="Update" CommandName="Update" /></td>  
  58.              <td><asp:Button ID="btncancel" runat="server"  ToolTip="Cancel" Text="Cancel" CommandName="Cancel" /></td>  
  59.             </tr>  
  60.             </EditItemTemplate>  
  61.         </asp:DataList>  
  62.         </div>  
  63.         </fieldset>  
  64.         </div>  
  65. </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.

Add Data Key Field
Figure 2: Add Data Key Field

Add Action Command
Figure 3: Add Action Command

Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Configuration;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10.   
  11. public partial class UI_DataList : System.Web.UI.Page  
  12. {  
  13.     string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;  
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.         if (!IsPostBack)  
  17.         {  
  18.             GetEmpDataList();  
  19.         }  
  20.     }  
  21.     protected void btnSave_Click(object sender, EventArgs e)  
  22.     {  
  23.         using (SqlConnection con = new SqlConnection(connection))  
  24.         {  
  25.             using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))  
  26.             {  
  27.                 cmd.CommandType = CommandType.StoredProcedure;  
  28.   
  29.                 cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
  30.                 cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
  31.                 cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
  32.   
  33.                 con.Open();  
  34.                 cmd.ExecuteNonQuery();  
  35.                 con.Close();  
  36.   
  37.                 Clear();  
  38.                 Response.Write("<script type=\"text/javascript\">alert('Record Inserted Successfully');</script>");  
  39.                 GetEmpDataList();  
  40.             }  
  41.         }  
  42.     }  
  43.     void Clear()  
  44.     {  
  45.         txtName.Text = String.Empty;  
  46.         txtContact.Text = String.Empty;  
  47.         txtEmail.Text = String.Empty;  
  48.     }  
  49.     private void GetEmpDataList()  
  50.     {  
  51.         using (SqlConnection con = new SqlConnection(connection))  
  52.         {  
  53.             SqlDataAdapter sd = new SqlDataAdapter("sp_FillData", con);  
  54.             sd.SelectCommand.CommandType = CommandType.StoredProcedure;  
  55.             DataTable dt = new DataTable();  
  56.   
  57.             sd.Fill(dt);  
  58.   
  59.             if (dt.Rows.Count > 0)  
  60.             {  
  61.                 DataListEmp.DataSource = dt;  
  62.                 DataListEmp.DataBind();  
  63.             }  
  64.          }  
  65.     }  
  66.     protected void DataListEmp_DeleteCommand(object source, DataListCommandEventArgs e)  
  67.     {  
  68.        int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
  69.   
  70.        using (SqlConnection con = new SqlConnection(connection))  
  71.        {  
  72.            using (SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con))  
  73.            {  
  74.                cmd.CommandType = CommandType.StoredProcedure;  
  75.                cmd.Parameters.AddWithValue("@EmpID",EmpID);  
  76.   
  77.                con.Open();  
  78.                cmd.ExecuteNonQuery();  
  79.                con.Close();  
  80.   
  81.                Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully');</script>");  
  82.                GetEmpDataList();  
  83.            }  
  84.        }  
  85.     }  
  86.     protected void DataListEmp_EditCommand(object source, DataListCommandEventArgs e)  
  87.     {  
  88.         DataListEmp.EditItemIndex = e.Item.ItemIndex;  
  89.         GetEmpDataList();  
  90.     }  
  91.     protected void DataListEmp_CancelCommand(object source, DataListCommandEventArgs e)  
  92.     {  
  93.         DataListEmp.EditItemIndex = -1;  
  94.         GetEmpDataList();  
  95.     }  
  96.     protected void DataListEmp_UpdateCommand(object source, DataListCommandEventArgs e)  
  97.     {  
  98.         int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
  99.   
  100.         TextBox txtName = (TextBox)e.Item.FindControl("txtName");  
  101.         TextBox txtContact = (TextBox)e.Item.FindControl("txtContact");  
  102.         TextBox txtEmail = (TextBox)e.Item.FindControl("txtEmail");  
  103.   
  104.         using (SqlConnection con = new SqlConnection(connection))  
  105.         {  
  106.             using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con))  
  107.             {  
  108.                 cmd.CommandType = CommandType.StoredProcedure;  
  109.   
  110.                 cmd.Parameters.AddWithValue("@EmpID", EmpID);  
  111.                 cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
  112.                 cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
  113.                 cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
  114.   
  115.                 con.Open();  
  116.                 cmd.ExecuteNonQuery();  
  117.                 con.Close();  
  118.   
  119.                 Clear();  
  120.                 Response.Write("<script type=\"text/javascript\">alert('Record Update Successfully');</script>");  
  121.                 DataListEmp.EditItemIndex = -1;  
  122.                 GetEmpDataList();  
  123.             }  
  124.         }  
  125.     }  
  126. }  
Step 5: Browser Side

Now run your new page in the browser.

Fill Record and save
Figure 4: Fill Record and save

Display Record
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.

Record Update and Delete
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:

contact Update and 1 record delete
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.


Similar Articles