Thursday, 11 August 2016

CRUD operation on Datalist Control in 3 Tier


In this article I’ll show you how to bind and how to perform Edit, Update, Delete Operation on Datalist Control in 3 tier in asp.net using c#.



INITIAL CHAMBER:

Step1) Open Your Visual Studio 2010 and Create an Empty Website, Give a suitable name [DataList_demo].

Step2) In Solution Explorer you get your empty website, Add a web form, SQL Database and 3 Class file. By going like this –

For Web Form:
DataList_demo (Your Empty Website) -> Right Click -> Add New Item -> Web Form. Name it as -> datalist_demo.aspx.

For SQL Server Database:
DataList_demo (Your Empty Website) -> Right Click -> Add New Item -> SQL Server Database. [Add Database inside the App_Data_folder].

For 3 Class Files:

DataList_demo (Your Empty Website) -> Right Click -> Add New Item -> Class [Add 3 Class files - -> Add your class file in App_code Folder] - -> Give name as:- 

1) Commonfunctions.cs
2) BAL_user_operation.cs
3) DAL_user_operation.cs


DATABASE CHAMBER:


Step3) In Server Explorer, Click on your Database [Database.mdf] - -> Tables - -> Add New Table -:- Make table like this:
   
  • Table - -> tbl_data [Don’t Forget to make ID as IS Identity -- True]


C:\Users\Nilesh\Desktop\tbl_data_sql_table.jpg

Add some Store procedure for Edit,Update, Insert and Delete Data by going to Database [Database.mdf] - -> Store Procedures - -> Right Click - -> Add New store Procedures.
  1. sp_getdata()
C:\Users\Nilesh\Desktop\sp_getdata.jpg
  1. sp_insert()
C:\Users\Nilesh\Desktop\sp_insert.jpg

  1. sp_update()


C:\Users\Nilesh\Desktop\sp_update.jpg

  1. sp_delete()
C:\Users\Nilesh\Desktop\sp_delete.jpg

These all are the Store Procedures that we will use for updating deleting and editing our data in DataList.


DESIGN CODE:

Step5) Now it’s time for serious design in DataList. Let’s begin by opening your Datalist.aspx page and fry the code like this:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>
   <style type="text/css">
       .style1
       {
           width: 75px;
       }
       .style3
       {
           width: 82px;
       }
       .style4
       {
           width: 234px;
       }
       .style5
       {
           width: 19px;
       }
       .style6
       {
           text-decoration: underline;
           font-size: large;
           color: #3333CC;
       }
   </style>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   
   </div>
   <table style="width:100%;">
       <caption class="style6">
           <strong>DataList Control in Asp.Net</strong></caption>
       <tr>
           <td>
               &nbsp;</td>
           <td>
               &nbsp;</td>
           <td>
               &nbsp;</td>
       </tr>
       <tr>
           <td>
               &nbsp;</td>
           <td>
               &nbsp;</td>
           <td>
               &nbsp;</td>
       </tr>
       <tr>
           <td>
               &nbsp;</td>
           <td>
   <asp:DataList ID="DataList1" runat="server" BackColor="#DEBA84"
       BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3"
       CellSpacing="2" DataKeyField="id" GridLines="Both"
       oncancelcommand="DataList1_CancelCommand"
       ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"
       onupdatecommand="DataList1_UpdateCommand" RepeatDirection="Horizontal">
       <EditItemTemplate>
           <table style="width:100%;">
               <tr>
                   <td class="style1">
                       &nbsp;</td>
                   <td class="style3">
                       &nbsp;</td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       &nbsp;</td>
                   <td class="style3">
                       &nbsp;</td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       Name:</td>
                   <td class="style3">
                       <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       Email:</td>
                   <td class="style3">
                       <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("email") %>'></asp:TextBox>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       Designation:</td>
                   <td class="style3">
                       <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("designation") %>'></asp:TextBox>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       City:</td>
                   <td class="style3">
                       <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       &nbsp;</td>
                   <td class="style3">
                       &nbsp;</td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style1">
                       <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Update</asp:LinkButton>
                   </td>
                   <td class="style3">
                       <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
           </table>
       </EditItemTemplate>
       <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
       <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
       <ItemStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
       <ItemTemplate>
           <table style="width:100%;">
               <tr>
                   <td class="style4">
                       &nbsp;</td>
                   <td class="style5">
                       &nbsp;</td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       <asp:Image ID="Image1" runat="server" BackColor="#FF99FF" BorderColor="#993399"
                           Height="191px" ImageAlign="AbsBottom" ImageUrl='<%# Bind("pic") %>'
                           Width="199px" />
                   </td>
              
               </tr>
               <tr>
                   <td class="style4">
                       Name:</td>
                   <td class="style5">
                       <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       Email:</td>
                   <td class="style5">
                       <asp:Label ID="Label3" runat="server" Text='<%# Bind("email") %>'></asp:Label>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       Designation</td>
                   <td class="style5">
                       <asp:Label ID="Label4" runat="server" Text='<%# Bind("designation") %>'></asp:Label>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       City:</td>
                   <td class="style5">
                       <asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       &nbsp;</td>
                   <td class="style5">
                       &nbsp;</td>
                   <td>
                       &nbsp;</td>
               </tr>
               <tr>
                   <td class="style4">
                       <asp:LinkButton ID="LinkButton3" CommandName="Edit" runat="server">Edit</asp:LinkButton>
                   </td>
                   <td class="style5">
                       <asp:LinkButton ID="LinkButton4"  CommandName="Delete" runat="server">Delete</asp:LinkButton>
                   </td>
                   <td>
                       &nbsp;</td>
               </tr>
           </table>
       </ItemTemplate>
       <SelectedItemStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
   </asp:DataList>
           </td>
        
       </tr>
   </table>
   </form>
</body>
</html>



Or you can do it manually like this:

  1. Drag you Datalist from toolbox to the design page.
  2. Click on the arrow sign of Datalist you will enter into the - -> Edit Template.
  3. Now in DataList Task, there is drop down  - -> select Item Template .
  4. In the Item Template - -> Drag Html Table.
  5. Inside Table  - -> Drag Image Control, 4 Label and 2 HyperLinks.
  6. Design like this :


C:\Users\Nilesh\Desktop\Datalist tasks.jpg



Now you have to click arrow sign of Image Control - -> Click on Edit DataBindings. 


C:\Users\Nilesh\Desktop\image_databinding.jpg


Here Click on Image Url  - - > and bind your Image url by giving - - > Bind(“pic”) in code Expression. Like in above figure. In the similar way do it for all labels.

  1. Label - -> Name

C:\Users\Nilesh\Desktop\bind_name.jpg


  1. Label - -> Email

C:\Users\Nilesh\Desktop\bind_email.jpg



  1. Label - -> Designation

C:\Users\Nilesh\Desktop\bind_designation.jpg

  1. Label - - > City
C:\Users\Nilesh\Desktop\bind_city.jpg


Now get back to Edit template of Datalist and select - -> Edit Item Template and Make design like this:


C:\Users\Nilesh\Desktop\edititem template.jpg 
For Binding textbox: -  I am showing you binding for Textbox1 [Textbox for Name: ]

C:\Users\Nilesh\Desktop\txtname_bind.jpg



In the similar way do it for all the textbox [email, designation, city].


CODE CHAMBER:


  1. Open Commonfunction.cs file and add this code :- ->

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
/// <summary>
/// Summary description for Commonfunctions
/// </summary>
public class Commonfunctions
{
    public Commonfunctions()
    {
        //
        // TODO: Add constructor logic here
        //
    }
   public static string getconstring()
   {
       return ConfigurationManager.ConnectionStrings["dbcon"].ToString();
   }
}

This code is written for SQL Connection String that we have to call again and again by going to database property that must be the lengthy process, that’s why we had made this class and now we just call its method - ->getconstring() that make our process shorter and comfortable.

  1. Open DAL_user_operation.cs file and code it like this:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_user_operation
/// </summary>
public class DAL_user_operation
{
    public DAL_user_operation()
    {
        //
        // TODO: Add constructor logic here
        //
    }
   // Inserting Code
   public bool user_insert(string name, string email,string designation,string city, string pic)
   {
       SqlConnection con = new SqlConnection(Commonfunctions.getconstring());
       SqlCommand cmd = new SqlCommand("sp_insert", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("name", name);
       cmd.Parameters.AddWithValue("email", email);
       cmd.Parameters.AddWithValue("designation", designation);
       cmd.Parameters.AddWithValue("city", city);
       cmd.Parameters.AddWithValue("pic", pic);
       con.Open();
       int i = cmd.ExecuteNonQuery();
       con.Close();

       if (i != 0)
       {
           return true;
       }
       else
       {
           return false;
       }
   }

   // Delete Code
   public void user_delete(int id)
   {
       SqlConnection con = new SqlConnection(Commonfunctions.getconstring());
       SqlCommand cmd = new SqlCommand("sp_delete", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("id", id);
       con.Open();
       int i = cmd.ExecuteNonQuery();
       con.Close();
   }


   // Update Code
   public void user_update(string name, string email, string designation, string city, int id)
   {
       SqlConnection con = new SqlConnection(Commonfunctions.getconstring());
       SqlCommand cmd = new SqlCommand("sp_update", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("name", name);
       cmd.Parameters.AddWithValue("email", email);
       cmd.Parameters.AddWithValue("designation", designation);
       cmd.Parameters.AddWithValue("city", city);
      
       cmd.Parameters.AddWithValue("id", id);
       con.Open();
       int i = cmd.ExecuteNonQuery();
       con.Close();
   }

   public DataTable getdata()
   {
       SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
       SqlCommand cmd = new SqlCommand("sp_getdata", con);
       cmd.CommandType = CommandType.StoredProcedure;
       SqlDataAdapter sda = new SqlDataAdapter(cmd);
       DataTable dt = new DataTable();
       sda.Fill(dt);
       return dt;
   
   }
   
}



  1. Open your BAL_user_operation.cs File and code it like this :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for BAL_user_operation
/// </summary>
public class BAL_user_operation
{
   DAL_user_operation du = new DAL_user_operation();
    public BAL_user_operation()
    {
        //
        // TODO: Add constructor logic here
        //
    }
   public bool user_insert(string name, string email, string designation, string city, string pic)
   {
      return du.user_insert(name, email,designation, city, pic);   
   }
   public void user_delete(int id)
   {
       du.user_delete(id);
   }
   public void user_update(string name, string email, string designation,  string city, int id)
   {
       du.user_update(name, email, designation, city, id);
   }
   public DataTable getdata()
   {
       return du.getdata();
   }
}



  1. At last Open Your DataList.aspx.cs file and code it like this :



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   BAL_user_operation bu = new BAL_user_operation();
   protected void Page_Load(object sender, EventArgs e)
   {
       if (!Page.IsPostBack)
       {
           refreshdata();
       }

   }
   public void refreshdata()
   {
       DataList1.DataSource = bu.getdata();
       DataList1.DataBind();
   }
   protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
   {
       DataList1.EditItemIndex = -1;
       refreshdata();
   }
   protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
   {
       int id = Convert.ToInt16(DataList1.DataKeys[e.Item.ItemIndex].ToString());
       bu.user_delete(id);
       refreshdata();

   }
   protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
   {
       DataList1.EditItemIndex = e.Item.ItemIndex;
       refreshdata();

   }
   protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
   {

       TextBox txtname = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox1") as TextBox;
       TextBox txtemail = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox3") as TextBox;
       TextBox txtdesignation = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox4") as TextBox;
       TextBox txtcity = DataList1.Items[e.Item.ItemIndex].FindControl("TextBox2") as TextBox;
       int id = int.Parse(DataList1.DataKeys[e.Item.ItemIndex].ToString());
       bu.user_update(txtname.Text, txtemail.Text, txtdesignation.Text, txtcity.Text, id);
       DataList1.EditItemIndex = -1;
       refreshdata();
   }

}


This is your web.config file code:

<configuration>

   <system.web>
       <compilation debug="true" targetFramework="4.0" />
   </system.web>
 <connectionStrings>

   <add name="dbcon" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"/>
  
 </connectionStrings>

</configuration>



OUTPUT CHAMBER:


C:\Users\Nilesh\Desktop\output_datalist.jpg



C:\Users\Nilesh\Desktop\update_data.jpg

Hope you like it. Thank you for Reading Have a nice Day.