ASP.NET

How to Bind GridView and perform edit update and delete operation on gridview in 3 Tiers in ASP.Net Using C#

In this article I will show you how to Bind GridView in 3 tier in ASP.Net using C# with Stored Procedure. We will also edit, delete and update the data in GridView.

Initial Chamber

Step 1: Open Visual Studio 2010 and create an Empty Website. Give it a suitable name [GridView_demo].

Step 2: In Solution Explorer you will get your empty website. Add a web form, SQL database and 3 class files. By going like this:

For Web Form

GridView_demo (Your Empty Website) -> Right Click -> Add New Item -> Web Form. Name it as -> gridview_demo.aspx.

For SQL Server Database

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

For 3 Class Files

GridView_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 the following:

  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 - -> Add New Table -:- Make table like the following:

Table - -> tbl_data [Don’t Forget to make ID as IS Identity -- True]

Add some Stored procedure for Update, Insert and Delete Data by going to Database [Database.mdf] - -> Stored Procedures - -> Right Click - -> Add New Stored Procedures.

  1. sp_getdata()



  2. sp_insert()



  3. sp_update()



  4. sp_delete()

These all are the Stored Procedures that we will use for updating, deleting and editing data in GridView.

Design Code

Step 5: Now it’s time for serious design in GridView. Let’s begin by opening gridview_demo.aspx page and try the following code:

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.       
  4.     <table style="width:100%;">  
  5.         <caption class="style3">  
  6.             <strong>Bind Grid View Using 3Tier</strong></caption>  
  7.         <tr>  
  8.               
  9.             <td  align="center">  
  10.       
  11.     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"   
  12.         BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"   
  13.         CellPadding="2" DataKeyNames="id" ForeColor="Black" GridLines="None"   
  14.                     AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"   
  15.                     onrowcancelingedit="GridView1_RowCancelingEdit"   
  16.                     onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"   
  17.                     onrowupdating="GridView1_RowUpdating">  
  18.         <AlternatingRowStyle BackColor="PaleGoldenrod" />  
  19.         <Columns>  
  20.             <asp:TemplateField HeaderText="Name" SortExpression="name">  
  21.                 <EditItemTemplate>  
  22.                     <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>  
  23.                 </EditItemTemplate>  
  24.                 <ItemTemplate>  
  25.                     <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>  
  26.                 </ItemTemplate>  
  27.             </asp:TemplateField>  
  28.             <asp:TemplateField HeaderText="Email" SortExpression="email">  
  29.                 <EditItemTemplate>  
  30.                     <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("email") %>'></asp:TextBox>  
  31.                 </EditItemTemplate>  
  32.                 <ItemTemplate>  
  33.                     <asp:Label ID="Label3" runat="server" Text='<%# Bind("email") %>'></asp:Label>  
  34.                 </ItemTemplate>  
  35.             </asp:TemplateField>  
  36.             <asp:TemplateField HeaderText="City" SortExpression="city">  
  37.                 <EditItemTemplate>  
  38.                     <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>  
  39.                 </EditItemTemplate>  
  40.                 <ItemTemplate>  
  41.                     <asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>  
  42.                 </ItemTemplate>  
  43.             </asp:TemplateField>  
  44.         </Columns>  
  45.         <FooterStyle BackColor="Tan" />  
  46.         <HeaderStyle BackColor="Tan" Font-Bold="True" />  
  47.         <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"   
  48.             HorizontalAlign="Center" />  
  49.         <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />  
  50.         <SortedAscendingCellStyle BackColor="#FAFAE7" />  
  51.         <SortedAscendingHeaderStyle BackColor="#DAC09E" />  
  52.         <SortedDescendingCellStyle BackColor="#E1DB9C" />  
  53.         <SortedDescendingHeaderStyle BackColor="#C2A47B" />  
  54.     </asp:GridView>  
  55.             </td>  
  56.               
  57.         </tr>  
  58.     </table>  
  59.     </form>  
  60. </body>  

You can also manually create this design by dragging the GridView into .aspx page. Then click the arrow sign on GridView - -> GridView tasks will open - -> Edit Columns - -> A “Field” window will open (following image). Here you have to add three “BoundField” Button from - -> Available Fields. - -> Change the header text - -> Name, Email, City.

Unclick the Auto Generated Button at the bottom [Note: I forgot to unclick it.].

Get into Bound Field Properties - -> Data Fields - -> Change every Bound field’s Data field to - ->

Go to GridView [In design mode] - -> press F4 to open Property window of GridView and find - -> Data Keys Name - -> and write – id.

In Property Window find - -> Auto Generate Edit and Auto Generate Delete Button and make it - -> True.

This is your actual Design.

Code Chamber

  1. Open Commonfunction.cs file and add this code:
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Configuration;  
    6.   
    7. /// <summary>  
    8. /// Summary description for Commonfunctions  
    9. /// </summary>  
    10. public class Commonfunctions  
    11. {  
    12.     public Commonfunctions()  
    13.     {  
    14.         //  
    15.         // TODO: Add constructor logic here  
    16.         //  
    17.     }  
    18.     public static string getconstring()  
    19.     {  
    20.   
    21.         return ConfigurationManager.ConnectionStrings["dbcon"].ToString();  
    22.        
    23.     }  
    24. }  
    The preceding code is written for SQL Connection String that we have to call again and again by going to the database property. This is a lengthy process, that’s why we made this class and now we will just call its method - ->getconstring() that will make our process shorter and comfortable.

  2. Open DAL_user_operation.cs file and code it as in the following code.
    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.     public bool user_insert(string name, string email,string city)  
    20.     {  
    21.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    22.         SqlCommand cmd = new SqlCommand("sp_insert", con);  
    23.         cmd.CommandType = CommandType.StoredProcedure;  
    24.         cmd.Parameters.AddWithValue("name", name);  
    25.         cmd.Parameters.AddWithValue("email",email);  
    26.         cmd.Parameters.AddWithValue("city", city);  
    27.           
    28.         con.Open();  
    29.         int i = cmd.ExecuteNonQuery();  
    30.        con.Close();  
    31.         if (i != 0)  
    32.         {  
    33.             return true;  
    34.         }  
    35.         else  
    36.         {  
    37.   
    38.             return false;  
    39.   
    40.         }  
    41.   
    42.     }  
    43.     public void  user_delete(int id)  
    44.     {  
    45.   
    46.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    47.         SqlCommand cmd = new SqlCommand("sp_delete", con);  
    48.         cmd.CommandType = CommandType.StoredProcedure;  
    49.         cmd.Parameters.AddWithValue("id", id);  
    50.         con.Open();  
    51.         int i = cmd.ExecuteNonQuery();  
    52.         con.Close();  
    53.   
    54.     }  
    55.   
    56.     public void user_update(string name, string email,string city, int id)  
    57.     {  
    58.   
    59.         SqlConnection con = new SqlConnection(Commonfunctions.getconstring());  
    60.         SqlCommand cmd = new SqlCommand("sp_update", con);  
    61.         cmd.CommandType = CommandType.StoredProcedure;  
    62.         cmd.Parameters.AddWithValue("name", name);  
    63.         cmd.Parameters.AddWithValue("email",email);  
    64.         cmd.Parameters.AddWithValue("city", city);  
    65.   
    66.         cmd.Parameters.AddWithValue("id", id);  
    67.         con.Open();  
    68.         int i = cmd.ExecuteNonQuery();  
    69.         con.Close();  
    70.       
    71.       
    72.     }  
    73.     public DataTable getdata()  
    74.     {  
    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.   
    83.         return dt;  
    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.   
    21.     public bool user_insert(string name, string email,string city)  
    22.     {  
    23.         return du.user_insert(name, email,city);  
    24.     }  
    25.   
    26.     public void  user_delete(int id)  
    27.     {  
    28.          du.user_delete(id);  
    29.       
    30.     }  
    31.   
    32.     public void user_update(string name, string email,string city, int id)   
    33.     {  
    34.   
    35.         du.user_update(name, email, city, id);  
    36.     }  
    37.   
    38.     public DataTable getdata()  
    39.    {  
    40.        return du.getdata();  
    41.     }  
    42. }  
  4. At last Open gridview_demo.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.   
    22.         GridView1.DataSource = bu.getdata();   
    23.         GridView1.DataBind();  
    24.       
    25.     }  
    26.     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)  
    27.     {  
    28.         int id = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());  
    29.         bu.user_delete(id);  
    30.         refreshdata();  
    31.   
    32.     }  
    33.     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)  
    34.     {  
    35.         GridView1.EditIndex = e.NewEditIndex;  
    36.         refreshdata();  
    37.   
    38.     }  
    39.     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
    40.     {  
    41.   
    42.         TextBox txtname = GridView1.Rows[e.RowIndex].FindControl("TextBox1") as TextBox;  
    43.         TextBox txtemail = GridView1.Rows[e.RowIndex].FindControl("TextBox3") as TextBox;  
    44.         TextBox txtcity = GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox;  
    45.       
    46.        int id= Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());  
    47.        bu.user_update(txtname.Text, txtemail.Text, txtcity.Text, id);  
    48.        GridView1.EditIndex = -1;  
    49.        refreshdata();  
    50.     }  
    51.     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
    52.     {  
    53.         GridView1.EditIndex = -1;  
    54.         refreshdata();  
    55.     }  
    56. }  

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

 

Hope you liked this!
Have a nice day and enjoy this tutorial.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs