Here we will learn crud operation in GridView using stored procedure in ASP.NET.

Before implementing this application first we will design the database table name is 'CollageInfo' and then stored Procedure name is CurdOperationInGridView.

Create Database Table and Stored procedure like following if you need,
  1. CREATE TABLE CollageInfo
  3. (
  5. Collageid int identity PRIMARY KEY ,
  7. Collagename nvarchar(50),
  9. CollageRank double
  11. )
  13. GO
  15. CREATE PROCEDURE CrudOperationsInGridView
  17. @Collageid int = 0,
  19. @Collagename varchar(50)=null,
  21. @CollageRank int=0,
  23. @status varchar(50)
  25. AS
  27. BEGIN
  29. SET NOCOUNT ON;
  31. --- Insert New Records For GridView
  33. IF @status='INSERT'
  35. BEGIN
  37. INSERT INTO CollageInfo(Collagename,CollageRank) VALUES(@Collagename,@CollageRank)
  39. END
  41. --- Select Records in Table For GridView
  43. IF @status='SELECT'
  45. BEGIN
  47. SELECT Collageid,Collagename,CollageRank FROM CollageInfo
  49. END
  51. --- Update Records in Table For GridView
  53. IF @status='UPDATE'
  55. BEGIN
  57. UPDATE CollageInfo SET @Collagename=@Collagename,CollageRank=@CollageRank WHERE Collageid=@Collageid
  59. END
  61. --- Delete Records from Table For GridView
  63. IF @status='DELETE'
  65. BEGIN
  67. DELETE FROM CollageInfo where Collageid=@Collageid
  69. END
  71. SET NOCOUNT OFF
  73. END
Once we Create stored procedure in database then add an aspx page and write the following code on .aspx Page.
  1. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head id="Head1" runat="server">
  5. <title>GridView Crud Operations using Stored Procedure in ASP.Net</title>
  7. <style type="text/css">
  8. .GridviewDesign {
  9. font-size: 100%;
  10. font-family: 'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif;
  11. color: #303933;
  12. }
  14. .headerstyleForGrid {
  15. color: #FFFFFF;
  16. border-right-color: #abb079;
  17. border-bottom-color: #abb079;
  18. background-color: #df5015;
  19. padding: 0.5em 0.5em 0.5em 0.5em;
  20. text-align: center;
  21. }
  22. </style>
  24. </head>
  26. <body>
  28. <form id="form1" runat="server">
  30. <div class="GridviewDesign">
  32. <asp:GridView runat="server" ID="gvDetailsForCurdOperation" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Collageid,Collagename" OnPageIndexChanging="gvDetailsForCurdOperation_PageIndexChanging" OnRowCancelingEdit="gvDetailsForCurdOperation_RowCancelingEdit" OnRowEditing="gvDetailsForCurdOperation_RowEditing" OnRowUpdating="gvDetailsForCurdOperation_RowUpdating" OnRowDeleting="gvDetailsForCurdOperation_RowDeleting" OnRowCommand="gvDetailsForCurdOperation_RowCommand">
  34. <HeaderStyle CssClass="headerstyleForGrid" />
  36. <Columns>
  38. <asp:BoundField DataField="Collageid" HeaderText="Collage Id" ReadOnly="true" />
  40. <asp:TemplateField HeaderText="Collage Name">
  42. <ItemTemplate>
  44. <asp:Label ID="lblCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
  46. </ItemTemplate>
  48. <EditItemTemplate>
  50. <asp:TextBox ID="txtCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
  52. </EditItemTemplate>
  54. <FooterTemplate>
  56. <asp:TextBox ID="txtpname" runat="server" />
  58. </FooterTemplate>
  60. </asp:TemplateField>
  62. <asp:TemplateField HeaderText="Collage Rank">
  64. <ItemTemplate>
  66. <asp:Label ID="lblCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'></asp:Label>
  68. </ItemTemplate>
  70. <EditItemTemplate>
  72. <asp:TextBox ID="txtCollageRank" runat="server" Text='<%# Eval("CollageRank")%>' />
  74. </EditItemTemplate>
  76. <FooterTemplate>
  78. <asp:TextBox ID="txtCollageRank" runat="server" />
  80. <asp:Button ID="btnAddNewItem" CommandName="AddNew" runat="server" Text="Add" />
  82. </FooterTemplate>
  84. </asp:TemplateField>
  86. <asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
  88. </Columns>
  90. </asp:GridView>
  92. <asp:Label ID="lblresult" runat="server"></asp:Label>
  94. </div>
  96. </form>
  98. </body>
  100. </html>
After completion code for .aspx page write the following code on .aspx.cs page,
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. if (!IsPostBack) {
  4. BindGridview();
  5. }
  6. }
  7. protected void BindGridview()
  8. {
  9. DataSet ds = new DataSet();
  10. using(SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation"))
  11. {
  12. con.Open();
  13. SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
  14. cmd.CommandType = CommandType.StoredProcedure;
  15. cmd.Parameters.AddWithValue("@status", "SELECT");
  16. SqlDataAdapter da = new SqlDataAdapter(cmd);
  17. da.Fill(ds);
  18. con.Close();
  19. if (ds.Tables[0].Rows.Count > 0) {
  20. gvDetailsForCurdOperation.DataSource = ds;
  21. gvDetailsForCurdOperation.DataBind();
  22. } else {
  23. ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
  24. gvDetailsForCurdOperation.DataSource = ds;
  25. gvDetailsForCurdOperation.DataBind();
  26. int columncount = gvDetailsForCurdOperation.Rows[0].Cells.Count;
  27. gvDetailsForCurdOperation.Rows[0].Cells.Clear();
  28. gvDetailsForCurdOperation.Rows[0].Cells.Add(new TableCell());
  29. gvDetailsForCurdOperation.Rows[0].Cells[0].ColumnSpan = columncount;
  30. gvDetailsForCurdOperation.Rows[0].Cells[0].Text = "No Records Found";
  31. }
  32. }
  33. }
  34. protected void gvDetailsForCurdOperation_RowCommand(object sender, GridViewCommandEventArgs e) {
  35. if (e.CommandName.Equals("AddNew")) {
  36. TextBox txtname = (TextBox) gvDetailsForCurdOperation.FooterRow.FindControl("txtpname");
  37. TextBox txtprice = (TextBox) gvDetailsForCurdOperation.FooterRow.FindControl("txtprice");
  38. crudoperationsForGrid("INSERT", txtname.Text, txtprice.Text, 0);
  39. }
  40. }
  41. protected void gvDetailsForCurdOperation_RowEditing(object sender, GridViewEditEventArgs e) {
  42. gvDetailsForCurdOperation.EditIndex = e.NewEditIndex;
  43. BindGridview();
  44. }
  45. protected void gvDetailsForCurdOperation_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
  46. gvDetailsForCurdOperation.EditIndex = -1;
  47. BindGridview();
  48. }
  49. protected void gvDetailsForCurdOperation_PageIndexChanging(object sender, GridViewPageEventArgs e) {
  50. gvDetailsForCurdOperation.PageIndex = e.NewPageIndex;
  51. BindGridview();
  52. }
  53. protected void gvDetailsForCurdOperation_RowUpdating(object sender, GridViewUpdateEventArgs e) {
  54. int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
  55. TextBox txtname = (TextBox) gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollagename");
  56. TextBox txtCollageRank = (TextBox) gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollageRank");
  57. crudoperationsForGrid("UPDATE", txtname.Text, txtCollageRank.Text, Collageid);
  58. }
  59. protected void gvDetailsForCurdOperation_RowDeleting(object sender, GridViewDeleteEventArgs e) {
  60. int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
  61. string Collagename = gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collagename"].ToString();
  62. crudoperationsForGrid("DELETE", Collagename, "", Collageid);
  63. }
  64. protected void crudoperationsForGrid(string status, string Collagename, string price, int Collageid) {
  65. using(SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation")) {
  66. con.Open();
  67. SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
  68. cmd.CommandType = CommandType.StoredProcedure;
  69. if (status == "INSERT") {
  70. cmd.Parameters.AddWithValue("@status", status);
  71. cmd.Parameters.AddWithValue("@Collagename", Collagename);
  72. cmd.Parameters.AddWithValue("@CollageRank", price);
  73. } else if (status == "UPDATE") {
  74. cmd.Parameters.AddWithValue("@status", status);
  75. cmd.Parameters.AddWithValue("@Collagename", Collagename);
  76. cmd.Parameters.AddWithValue("@CollageRank", price);
  77. cmd.Parameters.AddWithValue("@Collageid", Collageid);
  78. } else if (status == "DELETE") {
  79. cmd.Parameters.AddWithValue("@status", status);
  80. cmd.Parameters.AddWithValue("@Collageid", Collageid);
  81. }
  82. cmd.ExecuteNonQuery();
  83. lblresult.ForeColor = Color.Green;
  84. lblresult.Text = Collagename + " details " + status.ToLower() + "d successfully";
  85. gvDetailsForCurdOperation.EditIndex = -1;
  86. BindGridview();
  87. }
  88. }
Now run your application and see the output and perform CRUD operation for GridView.