How to Bind and Perform Update, Edit, Delete Operation on DataList Control in 3 Tier in ASP.Net

This article shows how to bind and perform edit, update and delete operations on a Datalist Control in 3 tiers in ASP.NET using C#.
 
Initial Chamber
 
Step 1: Open Visual Studio 2010 and create an Empty Website, give it a suitable name (DataList_demo).
 
Step 2: In Solution Explorer, you will get your empty website. Add a web form, SQL Database, and 3 class files as in the following.
 
For Web Form
 
DataList_demo (your empty website), right-click and Add New Item, Web Form. Name it datalist_demo.aspx.
 
For SQL Server Database
 
DataList_demo (your empty website), right-click and Add New Item, SQL Server Database (Add the database inside the App_Data_folder).
 
For 3 Class Files:
 
DataList_demo (your empty website), right-click and Add New Item, Class (add 3 class files, add your class file in the App_code folder), they provide the names as:
  1. Commonfunctions.cs
  2. BAL_user_operation.cs
  3. DAL_user_operation.cs
Database Chamber
 
Step 3: In Server Explorer, click on your database (Database.mdf), Tables, and Add New Table. Make the table as in the following:
  • Table, tbl_data (Don't Forget to make ID as IS Identity -- True).
     
    Table
     
    Add a Stored Procedure to edit, update, insert and delete data by going to the database (Database.mdf), Store Procedures and right-click, then add some new Stored Procedures as in the following:
     
    • sp_getdata()
       
      getdata
       
    • sp_insert()
       
      insert
       
    • sp_update()
       
      update
       
    • sp_delete()
       
      delete
     
    These are all the Stored Procedures that we will use for updating, deleting, and editing our data in the DataList.
Design Code
 
Step 4: It's time for the serious design in the DataList. Let's begin by opening your Datalist.aspx page and try the code as in the following:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title></title>  
  8.     <style type="text/css">  
  9.         .style1  
  10.         {  
  11.             width: 75px;  
  12.         }  
  13.         .style3  
  14.         {  
  15.             width: 82px;  
  16.         }  
  17.         .style4  
  18.         {  
  19.             width: 234px;  
  20.         }  
  21.         .style5  
  22.         {  
  23.             width: 19px;  
  24.         }  
  25.         .style6  
  26.         {  
  27.             text-decoration: underline;  
  28.             font-size: large;  
  29.             color: #3333CC;  
  30.         }  
  31.     </style>  
  32. </head>  
  33. <body>  
  34.     <form id="form1" runat="server">  
  35.     <div>  
  36.       
  37.     </div>  
  38.     <table style="width:100%;">  
  39.         <caption class="style6">  
  40.             <strong>DataList Control in Asp.Net</strong></caption>  
  41.         <tr>  
  42.             <td>  
  43.                  </td>  
  44.             <td>  
  45.                  </td>  
  46.             <td>  
  47.                  </td>  
  48.         </tr>  
  49.         <tr>  
  50.             <td>  
  51.                  </td>  
  52.             <td>  
  53.                  </td>  
  54.             <td>  
  55.                  </td>  
  56.         </tr>  
  57.         <tr>  
  58.             <td>  
  59.                  </td>  
  60.             <td>  
  61.     <asp:DataList ID="DataList1" runat="server" BackColor="#DEBA84"   
  62.         BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3"   
  63.         CellSpacing="2" DataKeyField="id" GridLines="Both"   
  64.         oncancelcommand="DataList1_CancelCommand"   
  65.         ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"   
  66.         onupdatecommand="DataList1_UpdateCommand" RepeatDirection="Horizontal">  
  67.         <EditItemTemplate>  
  68.             <table style="width:100%;">  
  69.                 <tr>  
  70.                     <td class="style1">  
  71.                          </td>  
  72.                     <td class="style3">  
  73.                          </td>  
  74.                     <td>  
  75.                          </td>  
  76.                 </tr>  
  77.                 <tr>  
  78.                     <td class="style1">  
  79.                          </td>  
  80.                     <td class="style3">  
  81.                          </td>  
  82.                     <td>  
  83.                          </td>  
  84.                 </tr>  
  85.                 <tr>  
  86.                     <td class="style1">  
  87.                         Name:</td>  
  88.                     <td class="style3">  
  89.                         <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>  
  90.                     </td>  
  91.                     <td>  
  92.                          </td>  
  93.                 </tr>  
  94.                 <tr>  
  95.                     <td class="style1">  
  96.                         Email:</td>  
  97.                     <td class="style3">  
  98.                         <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("email") %>'></asp:TextBox>  
  99.                     </td>  
  100.                     <td>  
  101.                          </td>  
  102.                 </tr>  
  103.                 <tr>  
  104.                     <td class="style1">  
  105.                         Designation:</td>  
  106.                     <td class="style3">  
  107.                         <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("designation") %>'></asp:TextBox>  
  108.                     </td>  
  109.                     <td>  
  110.                          </td>  
  111.                 </tr>  
  112.                 <tr>  
  113.                     <td class="style1">  
  114.                         City:</td>  
  115.                     <td class="style3">  
  116.                         <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>  
  117.                     </td>  
  118.                     <td>  
  119.                          </td>  
  120.                 </tr>  
  121.                 <tr>  
  122.                     <td class="style1">  
  123.                          </td>  
  124.                     <td class="style3">  
  125.                          </td>  
  126.                     <td>  
  127.                          </td>  
  128.                 </tr>  
  129.                 <tr>  
  130.                     <td class="style1">  
  131.                         <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Update</asp:LinkButton>  
  132.                     </td>  
  133.                     <td class="style3">  
  134.                         <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>  
  135.                     </td>  
  136.                     <td>  
  137.                          </td>  
  138.                 </tr>  
  139.             </table>  
  140.         </EditItemTemplate>  
  141.         <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />  
  142.         <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />  
  143.         <ItemStyle BackColor="#FFF7E7" ForeColor="#8C4510" />  
  144.         <ItemTemplate>  
  145.             <table style="width:100%;">  
  146.                 <tr>  
  147.                     <td class="style4">  
  148.                          </td>  
  149.                     <td class="style5">  
  150.                          </td>  
  151.                     <td>  
  152.                          </td>  
  153.                 </tr>  
  154.                 <tr>  
  155.                     <td class="style4">  
  156.                         <asp:Image ID="Image1" runat="server" BackColor="#FF99FF" BorderColor="#993399"   
  157.                             Height="191px" ImageAlign="AbsBottom" ImageUrl='<%# Bind("pic") %>'   
  158.                             Width="199px" />  
  159.                     </td>  
  160.                  
  161.                 </tr>  
  162.                 <tr>  
  163.                     <td class="style4">  
  164.                         Name:</td>  
  165.                     <td class="style5">  
  166.                         <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>  
  167.                     </td>  
  168.                     <td>  
  169.                          </td>  
  170.                 </tr>  
  171.                 <tr>  
  172.                     <td class="style4">  
  173.                         Email:</td>  
  174.                     <td class="style5">  
  175.                         <asp:Label ID="Label3" runat="server" Text='<%# Bind("email") %>'></asp:Label>  
  176.                     </td>  
  177.                     <td>  
  178.                          </td>  
  179.                 </tr>  
  180.                 <tr>  
  181.                     <td class="style4">  
  182.                         Designation</td>  
  183.                     <td class="style5">  
  184.                         <asp:Label ID="Label4" runat="server" Text='<%# Bind("designation") %>'></asp:Label>  
  185.                     </td>  
  186.                     <td>  
  187.                          </td>  
  188.                 </tr>  
  189.                 <tr>  
  190.                     <td class="style4">  
  191.                         City:</td>  
  192.                     <td class="style5">  
  193.                         <asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>  
  194.                     </td>  
  195.                     <td>  
  196.                          </td>  
  197.                 </tr>  
  198.                 <tr>  
  199.                     <td class="style4">  
  200.                          </td>  
  201.                     <td class="style5">  
  202.                          </td>  
  203.                     <td>  
  204.                          </td>  
  205.                 </tr>  
  206.                 <tr>  
  207.                     <td class="style4">  
  208.                         <asp:LinkButton ID="LinkButton3" CommandName="Edit" runat="server">Edit</asp:LinkButton>  
  209.                     </td>  
  210.                     <td class="style5">  
  211.                         <asp:LinkButton ID="LinkButton4"  CommandName="Delete" runat="server">Delete</asp:LinkButton>  
  212.                     </td>  
  213.                     <td>  
  214.                          </td>  
  215.                 </tr>  
  216.             </table>  
  217.         </ItemTemplate>  
  218.         <SelectedItemStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />  
  219.     </asp:DataList>  
  220.             </td>  
  221.            
  222.         </tr>  
  223.     </table>  
  224.     </form>  
  225. </body>  
  226. </html>  
Or you can do it manually like this:
  1. Drag your Datalist from the toolbox to the design page.
  2. Click on the arrow sign of the Datalist; you will enter into the Edit Template.
  3. Now in the DataList Task, there is a dropdown. From there, select Item Template.
  4. In the Item Template, drag an Html Table.
  5. Inside the table, drag an Image Control, 4 labels, and 2 Hyperlinks.
  6. Design like the following:
Design
 
Now you need to click the arrow sign of the Image Control and click on Edit DataBindings.
 
DataBindings
 
Here click on the ImageUrl and bind your Image URL by giving Bind(“pic”) in the code expression as in the preceding figure. In the same way, we can do it for all the labels.
  1. Label Name
     
     
    Name
     
  2. Label Email
     
     
    Email
     
  3. Label Designation
     
     
    Designation
     
  4. Label City
     
     
    City
Now return to the Edit template of the Datalist and select Edit Item Template and make the design like the following:
 
Edit template
 
For binding to the TextBox I will show you the binding for Textbox1 (TextBox for Name:).
 
bind
 
In a similar way do it for all the textboxes (email, designation, and city).
 
Code Chamber
  1. Open the Commonfunction.cs file and add the following code:
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Configuration;  
    6. /// <summary>  
    7. /// Summary description for Commonfunctions  
    8. /// </summary>  
    9. public class Commonfunctions  
    10. {  
    11.     public Commonfunctions()  
    12.     {  
    13.         //  
    14.         // TODO: Add constructor logic here  
    15.         //  
    16.     }  
    17.     public static string getconstring()  
    18.     {   
    19.         return ConfigurationManager.ConnectionStrings["dbcon"].ToString();  
    20.     }  
    21. }  
    The preceding code is written for a SQL Connection String that we need to call again and again by going to the database property. This is a lengthy process, that's why we had made this class and now we will just call its method getconstring() that makes our process shorter and easier.
     
  2. Open the DAL_user_operation.cs file and code it like the following:
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Data;  
    6. using System.Data.SqlClient;  
    7.   
    8. /// <summary>  
    9. /// Summary description for DAL_user_operation  
    10. /// </summary>  
    11. public class DAL_user_operation  
    12. {  
    13.     public DAL_user_operation()  
    14.     {  
    15.         //  
    16.         // TODO: Add constructor logic here  
    17.         //  
    18.     }  
    19.     // Inserting Code  
    20.     public bool user_insert(string name, string email,string designation,string city, string pic)  
    21.     {  
    22.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    23.         SqlCommand cmd = new SqlCommand("sp_insert", con);  
    24.         cmd.CommandType = CommandType.StoredProcedure;  
    25.         cmd.Parameters.AddWithValue("name", name);  
    26.         cmd.Parameters.AddWithValue("email", email);  
    27.         cmd.Parameters.AddWithValue("designation", designation);  
    28.         cmd.Parameters.AddWithValue("city", city);  
    29.         cmd.Parameters.AddWithValue("pic", pic);  
    30.         con.Open();  
    31.         int i = cmd.ExecuteNonQuery();  
    32.         con.Close();  
    33.   
    34.         if (i != 0)  
    35.         {  
    36.             return true;  
    37.         }  
    38.         else  
    39.         {  
    40.             return false;  
    41.         }  
    42.     }  
    43.   
    44.     // Delete Code  
    45.     public void user_delete(int id)  
    46.     {  
    47.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    48.         SqlCommand cmd = new SqlCommand("sp_delete", con);  
    49.         cmd.CommandType = CommandType.StoredProcedure;  
    50.         cmd.Parameters.AddWithValue("id", id);  
    51.         con.Open();  
    52.         int i = cmd.ExecuteNonQuery();  
    53.         con.Close();  
    54.     }  
    55.   
    56.   
    57.     // Update Code  
    58.     public void user_update(string name, string email, string designation, string city, int id)  
    59.     {  
    60.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    61.         SqlCommand cmd = new SqlCommand("sp_update", con);  
    62.         cmd.CommandType = CommandType.StoredProcedure;  
    63.         cmd.Parameters.AddWithValue("name", name);  
    64.         cmd.Parameters.AddWithValue("email", email);  
    65.         cmd.Parameters.AddWithValue("designation", designation);  
    66.         cmd.Parameters.AddWithValue("city", city);  
    67.          
    68.         cmd.Parameters.AddWithValue("id", id);  
    69.         con.Open();  
    70.         int i = cmd.ExecuteNonQuery();  
    71.         con.Close();   
    72.     }  
    73.   
    74.     public DataTable getdata()  
    75.     {  
    76.         SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");  
    77.         SqlCommand cmd = new SqlCommand("sp_getdata", con);  
    78.         cmd.CommandType = CommandType.StoredProcedure;  
    79.         SqlDataAdapter sda = new SqlDataAdapter(cmd);  
    80.         DataTable dt = new DataTable();  
    81.         sda.Fill(dt);  
    82.         return dt;  
    83.       
    84.     }  
    85.       
    86. }  
  3. Open your BAL_user_operation.cs file and code it like the following:
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Data;  
    6. using System.Data.SqlClient;  
    7.   
    8. /// <summary>  
    9. /// Summary description for BAL_user_operation  
    10. /// </summary>  
    11. public class BAL_user_operation  
    12. {  
    13.     DAL_user_operation du = new DAL_user_operation();  
    14.     public BAL_user_operation()  
    15.     {  
    16.         //  
    17.         // TODO: Add constructor logic here  
    18.         //  
    19.     }  
    20.     public bool user_insert(string name, string email, string designation, string city, string pic)  
    21.     {  
    22.        return du.user_insert(name, email,designation, city, pic);     
    23.     }  
    24.     public void user_delete(int id)  
    25.     {  
    26.         du.user_delete(id);  
    27.     }  
    28.     public void user_update(string name, string email, string designation,  string city, int id)  
    29.     {  
    30.         du.user_update(name, email, designation, city, id);   
    31.     }  
    32.     public DataTable getdata()  
    33.     {  
    34.         return du.getdata();  
    35.     }  
    36. }  
  4. Finally open the DataList.aspx.cs file and code it like the following:
    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.   
    8. public partial class _Default : System.Web.UI.Page  
    9. {  
    10.     BAL_user_operation bu = new BAL_user_operation();  
    11.     protected void Page_Load(object sender, EventArgs e)  
    12.     {  
    13.         if (!Page.IsPostBack)  
    14.         {  
    15.             refreshdata();  
    16.         }  
    17.   
    18.     }  
    19.     public void refreshdata()  
    20.     {  
    21.         DataList1.DataSource = bu.getdata();  
    22.         DataList1.DataBind();  
    23.     }  
    24.     protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)  
    25.     {  
    26.         DataList1.EditItemIndex = -1;  
    27.         refreshdata();  
    28.     }  
    29.     protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)  
    30.     {  
    31.         int id = Convert.ToInt16(DataList1.DataKeys[e.Item.ItemIndex].ToString());  
    32.         bu.user_delete(id);  
    33.         refreshdata();  
    34.   
    35.     }  
    36.     protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)  
    37.     {  
    38.         DataList1.EditItemIndex = e.Item.ItemIndex;  
    39.         refreshdata();  
    40.   
    41.     }  
    42.     protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)  
    43.     {  
    44.   
    45.         TextBox txtname = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox1"as TextBox;  
    46.         TextBox txtemail = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox3"as TextBox;  
    47.         TextBox txtdesignation = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox4"as TextBox;  
    48.         TextBox txtcity = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox2"as TextBox;  
    49.         int id = int.Parse(DataList1.DataKeys[e.Item.ItemIndex].ToString());  
    50.         bu.user_update(txtname.Text, txtemail.Text, txtdesignation.Text, txtcity.Text, id);  
    51.         DataList1.EditItemIndex = -1;  
    52.         refreshdata();  
    53.     }  
    54.   
    55. }  
    This is your web.config file code:
    1. <configuration>  
    2.   
    3.     <system.web>  
    4.         <compilation debug="true" targetFramework="4.0" />  
    5.     </system.web>  
    6.   <connectionStrings>  
    7.   
    8.     <add name="dbcon" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"/>  
    9.      
    10.   </connectionStrings>  
    11.   
    12. </configuration>  
Output Chamber
 
Output Chamber
 
data list control
 
I hope you will like this. Thank you for reading.
 
Have a nice day.


Recommended Ebook

Diving Into Microsoft .NET Entity Framework

Download Now!
Similar Articles