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

Nilesh Jadav
9y
162.3k
0
3

Article

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:

Commonfunctions.cs
BAL_user_operation.cs
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.

sp_getdata()
sp_insert()
sp_update()
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:

<body>
<form id="form1" runat="server">
<table style="width:100%;">
<caption class="style3">
<strong>Bind Grid View Using 3Tier</strong></caption>
<tr>
<td align="center">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
CellPadding="2" DataKeyNames="id" ForeColor="Black" GridLines="None"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating">
<AlternatingRowStyle BackColor="PaleGoldenrod" />
<Columns>
<asp:TemplateField HeaderText="Name" SortExpression="name">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" SortExpression="email">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("email") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("email") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City" SortExpression="city">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="Tan" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<SortedAscendingCellStyle BackColor="#FAFAE7" />
<SortedAscendingHeaderStyle BackColor="#DAC09E" />
<SortedDescendingCellStyle BackColor="#E1DB9C" />
<SortedDescendingHeaderStyle BackColor="#C2A47B" />
</asp:GridView>
</td>
</tr>
</table>
</form>
</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

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;
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. {
21. return ConfigurationManager.ConnectionStrings["dbcon"].ToString();
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.
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;
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);
28. con.Open();
29. int i = cmd.ExecuteNonQuery();
30. con.Close();
31. if (i != 0)
32. {
33. return true;
34. }
35. else
36. {
38. return false;
40. }
42. }
43. public void user_delete(int id)
44. {
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();
54. }
56. public void user_update(string name, string email,string city, int id)
57. {
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);
66. cmd.Parameters.AddWithValue("id", id);
67. con.Open();
68. int i = cmd.ExecuteNonQuery();
69. con.Close();
72. }
73. public DataTable getdata()
74. {
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);
83. return dt;
85. }
86. }
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;
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. }
21. public bool user_insert(string name, string email,string city)
22. {
23. return du.user_insert(name, email,city);
24. }
26. public void user_delete(int id)
27. {
28. du.user_delete(id);
30. }
32. public void user_update(string name, string email,string city, int id)
33. {
35. du.user_update(name, email, city, id);
36. }
38. public DataTable getdata()
39. {
40. return du.getdata();
41. }
42. }
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;
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. }
19. public void refreshdata()
20. {
22. GridView1.DataSource = bu.getdata();
23. GridView1.DataBind();
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();
32. }
33. protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
34. {
35. GridView1.EditIndex = e.NewEditIndex;
36. refreshdata();
38. }
39. protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
40. {
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;
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

<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

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

Up Next

Ebook Download

View all

Hands on ASP.NET GridView

Read by 12k people

Download Now!

Learn

View all