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:

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.

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;
   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.
   
   
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;
   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. }
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;
   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. }
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;
   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

Output Chamber

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