Post
Ask Question

CRUD Operation in Gridview using Stored Procedure

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  
  2.   
  3. (  
  4.   
  5. Collageid int identity PRIMARY KEY ,  
  6.   
  7. Collagename nvarchar(50),  
  8.   
  9. CollageRank double  
  10.   
  11. )  
  12.   
  13. GO  
  14.   
  15. CREATE PROCEDURE CrudOperationsInGridView  
  16.   
  17. @Collageid int = 0,  
  18.   
  19. @Collagename varchar(50)=null,  
  20.   
  21. @CollageRank int=0,  
  22.   
  23. @status varchar(50)  
  24.   
  25. AS  
  26.   
  27. BEGIN  
  28.   
  29. SET NOCOUNT ON;  
  30.   
  31. --- Insert New Records For GridView  
  32.   
  33. IF @status='INSERT'  
  34.   
  35. BEGIN  
  36.   
  37. INSERT INTO CollageInfo(Collagename,CollageRank) VALUES(@Collagename,@CollageRank)  
  38.   
  39. END  
  40.   
  41. --- Select Records in Table For GridView  
  42.   
  43. IF @status='SELECT'  
  44.   
  45. BEGIN  
  46.   
  47. SELECT Collageid,Collagename,CollageRank FROM CollageInfo  
  48.   
  49. END  
  50.   
  51. --- Update Records in Table For GridView  
  52.   
  53. IF @status='UPDATE'  
  54.   
  55. BEGIN  
  56.   
  57. UPDATE CollageInfo SET @Collagename=@Collagename,CollageRank=@CollageRank WHERE Collageid=@Collageid  
  58.   
  59. END  
  60.   
  61. --- Delete Records from Table For GridView  
  62.   
  63. IF @status='DELETE'  
  64.   
  65. BEGIN  
  66.   
  67. DELETE FROM CollageInfo where Collageid=@Collageid  
  68.   
  69. END  
  70.   
  71. SET NOCOUNT OFF  
  72.   
  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">  
  2.   
  3. <head id="Head1" runat="server">  
  4.   
  5.     <title>GridView Crud Operations using Stored Procedure in ASP.Net</title>  
  6.   
  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.         }  
  13.           
  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>  
  23.   
  24. </head>  
  25.   
  26. <body>  
  27.   
  28.     <form id="form1" runat="server">  
  29.   
  30.         <div class="GridviewDesign">  
  31.   
  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">  
  33.   
  34.                 <HeaderStyle CssClass="headerstyleForGrid" />  
  35.   
  36.                 <Columns>  
  37.   
  38.                     <asp:BoundField DataField="Collageid" HeaderText="Collage Id" ReadOnly="true" />  
  39.   
  40.                     <asp:TemplateField HeaderText="Collage Name">  
  41.   
  42.                         <ItemTemplate>  
  43.   
  44.                             <asp:Label ID="lblCollagename" runat="server" Text='<%# Eval("Collagename")%>' />  
  45.   
  46.                         </ItemTemplate>  
  47.   
  48.                         <EditItemTemplate>  
  49.   
  50.                             <asp:TextBox ID="txtCollagename" runat="server" Text='<%# Eval("Collagename")%>' />  
  51.   
  52.                         </EditItemTemplate>  
  53.   
  54.                         <FooterTemplate>  
  55.   
  56.                             <asp:TextBox ID="txtpname" runat="server" />  
  57.   
  58.                         </FooterTemplate>  
  59.   
  60.                     </asp:TemplateField>  
  61.   
  62.                     <asp:TemplateField HeaderText="Collage Rank">  
  63.   
  64.                         <ItemTemplate>  
  65.   
  66.                             <asp:Label ID="lblCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'></asp:Label>  
  67.   
  68.                         </ItemTemplate>  
  69.   
  70.                         <EditItemTemplate>  
  71.   
  72.                             <asp:TextBox ID="txtCollageRank" runat="server" Text='<%# Eval("CollageRank")%>' />  
  73.   
  74.                         </EditItemTemplate>  
  75.   
  76.                         <FooterTemplate>  
  77.   
  78.                             <asp:TextBox ID="txtCollageRank" runat="server" />  
  79.   
  80.                             <asp:Button ID="btnAddNewItem" CommandName="AddNew" runat="server" Text="Add" />  
  81.   
  82.                         </FooterTemplate>  
  83.   
  84.                     </asp:TemplateField>  
  85.   
  86.                     <asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />  
  87.   
  88.                 </Columns>  
  89.   
  90.             </asp:GridView>  
  91.   
  92.             <asp:Label ID="lblresult" runat="server"></asp:Label>  
  93.   
  94.         </div>  
  95.   
  96.     </form>  
  97.   
  98. </body>  
  99.   
  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.
Next Recommended Reading CRUD Operation Using 3-Tier Architecture In ASP.NET With jQuery Validation