Post
Ask Question

CRUD Operation In ASP.NET GridView Using Single Stored Procedure

Stored Procedure

Firstly, create a Stored Procedure for performing CRUD operation on database from ASP.NET page. In the following Stored procedure I have mentioned the entire query in one stored procedure like Select, Add, Update and Delete. All the operation is based on Event or status which will send from ASP.NET page.

Stored Procedure is based on Table "Employee".

Column Name DataType
Id int
FirstName varchar(50)
LastName varchar(50)
PhoneNumber nvarchar(15)
EmailAddress nvarchar(50)
Salary decimal(18, 2)

Below find the stored procedure for above table. I have used if-else condition to perform all operations in single stored procedure. Based on Event which is sent by ASP.NET page the query will be executed.

  1. CREATE PROC [dbo].[usp_GridViewExample]  
  2. (  
  3. @EmpId int=0,@FirstName varchar(50)=Null,@LastName varchar(50)=Null,@PhoneNumber nvarchar(15)=Null,  
  4. @EmailAddress nvarchar(50)=Null,@Salary decimal=Null,@Event varchar(10)  
  5. )  
  6. AS   
  7. BEGIN  
  8.     IF(@Event='Select')  
  9.     BEGIN  
  10.     SELECT * FROM Employee ORDER BY FirstName ASC;  
  11.     END  
  12.   
  13.     ELSE IF(@Event='Add')  
  14.     BEGIN  
  15.     INSERT INTO Employee (FirstName,LastName,PhoneNumber,EmailAddress,Salary,CreatedDate) VALUES(@FirstName,@LastName,@PhoneNumber,@EmailAddress,@Salary,GETDATE());  
  16.     END  
  17.   
  18.     ELSE IF(@Event='Update')  
  19.     BEGIN  
  20.     UPDATE Employee SET FirstName=@FirstName,LastName=@LastName,PhoneNumber=@PhoneNumber,EmailAddress=@EmailAddress,Salary=@Salary where Id=@EmpId;  
  21.     END  
  22.   
  23.     ELSE  
  24.     BEGIN  
  25.     DELETE FROM Employee WHERE Id=@EmpId;  
  26.     END  
  27. END  
GridViewDemo.aspx

This is a UI of application. In this I have created Add Employee Panel where we can add new employee and below this I have added a gridview where we can do Edit and Delete as well as you can see all the records,
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewDemo.aspx.cs" Inherits="GridViewDemo.GridViewDemo" %>  
  2.     <!DOCTYPE html>  
  3.     <html xmlns="http://www.w3.org/1999/xhtml">  
  4.   
  5.     <head runat="server">  
  6.         <title></title>  
  7.         <style>  
  8. td {  
  9.     align=center;  
  10. }  
  11.         </style>  
  12.     </head>  
  13.   
  14.     <body>  
  15.         <form id="form1" runat="server">  
  16.             <div align="center">  
  17.                 <div style="border: 1px Solid #0094ff; width: 70%;">  
  18.                     <table>  
  19.                         <tr>  
  20.                             <td colspan="3" align="center">  
  21.                                 <h2>Employee Management System</h2> </td>  
  22.                         </tr>  
  23.                         <tr>  
  24.                             <td>First Name </td>  
  25.                             <td>  
  26.                                 <asp:TextBox runat="server" ID="txtFirstName" ValidationGroup="add"></asp:TextBox>  
  27.                             </td>  
  28.                             <td>  
  29.                                 <asp:RequiredFieldValidator runat="server" ID="reqFirstName" ControlToValidate="txtFirstName" ForeColor="Red" ErrorMessage="First Name Requierd" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>  
  30.                             </td>  
  31.                         </tr>  
  32.                         <tr>  
  33.                             <td>Last Name </td>  
  34.                             <td>  
  35.                                 <asp:TextBox runat="server" ID="txtLastName" ValidationGroup="add"></asp:TextBox>  
  36.                             </td>  
  37.                             <td>  
  38.                                 <asp:RequiredFieldValidator runat="server" ID="reqLastName" ControlToValidate="txtLastName" ForeColor="Red" ErrorMessage="Last Name Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>  
  39.                             </td>  
  40.                         </tr>  
  41.                         <tr>  
  42.                             <td>Phone Number </td>  
  43.                             <td>  
  44.                                 <asp:TextBox runat="server" ID="txtPhoneNumber"></asp:TextBox>  
  45.                             </td>  
  46.                             <td></td>  
  47.                         </tr>  
  48.                         <tr>  
  49.                             <td>Emial Address </td>  
  50.                             <td>  
  51.                                 <asp:TextBox runat="server" ID="txtEmailAddress" ValidationGroup="add"></asp:TextBox>  
  52.                             </td>  
  53.                             <td>  
  54.                                 <asp:RequiredFieldValidator runat="server" ID="reqEmailAddress" ControlToValidate="txtEmailAddress" ForeColor="Red" ErrorMessage="Email Address Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>  
  55.                             </td>  
  56.                         </tr>  
  57.                         <tr>  
  58.                             <td>Salary </td>  
  59.                             <td>  
  60.                                 <asp:TextBox runat="server" ID="txtSalary" ValidationGroup="add"></asp:TextBox>  
  61.                             </td>  
  62.                             <td>  
  63.                                 <asp:RequiredFieldValidator runat="server" ID="reqSalary" ControlToValidate="txtSalary" ForeColor="Red" ErrorMessage="Salary Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>  
  64.                             </td>  
  65.                         </tr>  
  66.                         <tr>  
  67.                             <td colspan="3" align="center">  
  68.                                 <asp:Button runat="server" ID="btnAddEmployee" Text="Add" OnClick="btnAddEmployee_Click" Style="height: 26px" Width="37px" ValidationGroup="add" /> </td>  
  69.                         </tr>  
  70.                         <tr>  
  71.                             <td colspan="3" align="center">  
  72.                                 <br />  
  73.                                 <asp:Label runat="server" ID="lblMessage"></asp:Label>  
  74.                                 <br />  
  75.                                 <br /> </td>  
  76.                         </tr>  
  77.                         <tr>  
  78.                             <td colspan="3">  
  79.                                 <asp:GridView ID="grvEmployee" runat="server" AllowPaging="true" AutoGenerateColumns="false" Width="900px" HeaderStyle-ForeColor="blue" OnPageIndexChanging="grvEmployee_PageIndexChanging" OnRowCancelingEdit="grvEmployee_RowCancelingEdit" OnRowDeleting="grvEmployee_RowDeleting" OnRowEditing="grvEmployee_RowEditing" OnRowUpdating="grvEmployee_RowUpdating">  
  80.                                     <Columns>  
  81.                                         <asp:TemplateField HeaderText="EmpId">  
  82.                                             <ItemTemplate>  
  83.                                                 <asp:Label runat="server" ID="lblEmpId" Text='<%#Eval("id") %>'></asp:Label>  
  84.                                             </ItemTemplate>  
  85.                                         </asp:TemplateField>  
  86.                                         <asp:TemplateField HeaderText="FirstName">  
  87.                                             <ItemTemplate>  
  88.                                                 <asp:Label runat="server" ID="lblFirstName" Text='<%#Eval("FirstName") %>'></asp:Label>  
  89.                                             </ItemTemplate>  
  90.                                             <EditItemTemplate>  
  91.                                                 <asp:TextBox runat="server" ID="txtFirstName" Text='<%#Eval("FirstName") %>'></asp:TextBox>  
  92.                                             </EditItemTemplate>  
  93.                                         </asp:TemplateField>  
  94.                                         <asp:TemplateField HeaderText="LastName">  
  95.                                             <ItemTemplate>  
  96.                                                 <asp:Label runat="server" ID="lblLastName" Text='<%#Eval("LastName") %>'></asp:Label>  
  97.                                             </ItemTemplate>  
  98.                                             <EditItemTemplate>  
  99.                                                 <asp:TextBox runat="server" ID="txtLastName" Text='<%#Eval("LastName") %>'></asp:TextBox>  
  100.                                             </EditItemTemplate>  
  101.                                         </asp:TemplateField>  
  102.                                         <asp:TemplateField HeaderText="Phone No.">  
  103.                                             <ItemTemplate>  
  104.                                                 <asp:Label runat="server" ID="lblPhoneNumber" Text='<%#Eval("PhoneNumber") %>'></asp:Label>  
  105.                                             </ItemTemplate>  
  106.                                             <EditItemTemplate>  
  107.                                                 <asp:TextBox runat="server" ID="txtPhoneNumber" Text='<%#Eval("PhoneNumber") %>'></asp:TextBox>  
  108.                                             </EditItemTemplate>  
  109.                                         </asp:TemplateField>  
  110.                                         <asp:TemplateField HeaderText="Email">  
  111.                                             <ItemTemplate>  
  112.                                                 <asp:Label runat="server" ID="lblEmailAddress" Text='<%#Eval("EmailAddress") %>'></asp:Label>  
  113.                                             </ItemTemplate>  
  114.                                             <EditItemTemplate>  
  115.                                                 <asp:TextBox runat="server" ID="txtEmailAddress" Text='<%#Eval("EmailAddress") %>'></asp:TextBox>  
  116.                                             </EditItemTemplate>  
  117.                                         </asp:TemplateField>  
  118.                                         <asp:TemplateField HeaderText="Salary">  
  119.                                             <ItemTemplate>  
  120.                                                 <asp:Label runat="server" ID="lblSalary" Text='<%#Eval("Salary") %>'></asp:Label>  
  121.                                             </ItemTemplate>  
  122.                                             <EditItemTemplate>  
  123.                                                 <asp:TextBox runat="server" ID="txtSalary" Text='<%#Eval("Salary") %>'></asp:TextBox>  
  124.                                             </EditItemTemplate>  
  125.                                         </asp:TemplateField>  
  126.                                         <asp:TemplateField HeaderText="Manage">  
  127.                                             <ItemTemplate>  
  128.                                                 <asp:LinkButton runat="server" ID="btnEdit" Text="Edit" CommandName="Edit" />  
  129.                                                 <br /> <span onclick="return confirm('Are you sure you want to delete this record?')">  
  130.                                                 <asp:LinkButton runat="server" ID="btnDelete" Text="Delete" CommandName="Delete" />  
  131.                                             </span> </ItemTemplate>  
  132.                                             <EditItemTemplate>  
  133.                                                 <asp:LinkButton runat="server" ID="btnUpdate" Text="Update" CommandName="Update" />  
  134.                                                 <br />  
  135.                                                 <asp:LinkButton runat="server" ID="btnCancel" Text="Cancel" CommandName="Cancel" /> </EditItemTemplate>  
  136.                                         </asp:TemplateField>  
  137.                                     </Columns>  
  138.                                 </asp:GridView>  
  139.                             </td>  
  140.                         </tr>  
  141.                     </table>  
  142.                 </div>  
  143.             </div> <a href="http://www.nextprogramming.com/2014/09/stored-procedure-in-sql-server-advantages-of-stored-procedure-and-how-to-use-stored-procedure">Click Here</a> </form>  
  144.     </body>  
  145.   
  146. </html>  
GridViewDemo.aspx.cs

It’s a .cs file or called code-behind file where I have written the entire login for CRUD operation in GridView using stored procedure. I have created Connection for database connectivity, on Add button I have added a new employee in database. I have also used here validation using Required Field Validator. Edit and Delete operation have been done in GridView.

 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.UI;  
  9. using System.Web.UI.WebControls;  
  10.   
  11. namespace GridViewDemo  
  12. {  
  13.     public partial class GridViewDemo : System.Web.UI.Page  
  14.     {  
  15.         private string strConnectionString = ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString;  
  16.         private SqlCommand _sqlCommand;  
  17.         private SqlDataAdapter _sqlDataAdapter;  
  18.         DataSet _dtSet;  
  19.         protected void Page_Load(object sender, EventArgs e)  
  20.         {  
  21.             if (!IsPostBack)  
  22.             {  
  23.                 BindEmployeeData();  
  24.             }  
  25.   
  26.         }  
  27.         public void CreateConnection()  
  28.         {  
  29.             SqlConnection _sqlConnection = new SqlConnection(strConnectionString);  
  30.             _sqlCommand = new SqlCommand();  
  31.             _sqlCommand.Connection = _sqlConnection;  
  32.         }  
  33.         public void OpenConnection()  
  34.         {  
  35.             _sqlCommand.Connection.Open();  
  36.         }  
  37.         public void CloseConnection()  
  38.         {  
  39.             _sqlCommand.Connection.Close();  
  40.         }  
  41.         public void DisposeConnection()  
  42.         {  
  43.             _sqlCommand.Connection.Dispose();  
  44.         }  
  45.         public void BindEmployeeData()  
  46.         {  
  47.             try  
  48.             {  
  49.                 CreateConnection();  
  50.                 OpenConnection();  
  51.                 _sqlCommand.CommandText = "usp_GridViewExample";  
  52.                 _sqlCommand.CommandType = CommandType.StoredProcedure;  
  53.                 _sqlCommand.Parameters.AddWithValue("@Event", "Select");  
  54.                 _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);  
  55.                 _dtSet = new DataSet();  
  56.                 _sqlDataAdapter.Fill(_dtSet);  
  57.                 grvEmployee.DataSource = _dtSet;  
  58.                 grvEmployee.DataBind();  
  59.             }  
  60.             catch (Exception ex)  
  61.             {  
  62.                 Response.Redirect("The Error is " + ex);  
  63.             }  
  64.             finally  
  65.             {  
  66.                 CloseConnection();  
  67.                 DisposeConnection();  
  68.             }  
  69.         }  
  70.   
  71.         protected void btnAddEmployee_Click(object sender, EventArgs e)  
  72.         {  
  73.             try  
  74.             {  
  75.                 CreateConnection();  
  76.                 OpenConnection();  
  77.                 _sqlCommand.CommandText = "usp_GridViewExample";  
  78.                 _sqlCommand.CommandType = CommandType.StoredProcedure;  
  79.                 _sqlCommand.Parameters.AddWithValue("@Event", "Add");  
  80.                 _sqlCommand.Parameters.AddWithValue("@FirstName", Convert.ToString(txtFirstName.Text.Trim()));  
  81.                 _sqlCommand.Parameters.AddWithValue("@LastName", Convert.ToString(txtLastName.Text.Trim()));  
  82.                 _sqlCommand.Parameters.AddWithValue("@PhoneNumber", Convert.ToString(txtPhoneNumber.Text.Trim()));  
  83.                 _sqlCommand.Parameters.AddWithValue("@EmailAddress", Convert.ToString(txtEmailAddress.Text.Trim()));  
  84.                 _sqlCommand.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text));  
  85.                 int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());  
  86.                 if (result > 0)  
  87.                 {  
  88.                     lblMessage.Text = "Record Added Successfully";  
  89.                     lblMessage.ForeColor = System.Drawing.Color.Green;  
  90.                     BindEmployeeData();  
  91.                 }  
  92.                 else  
  93.                 {  
  94.                     lblMessage.Text = "Failed";  
  95.                     lblMessage.ForeColor = System.Drawing.Color.Red;                  
  96.                 }  
  97.             }  
  98.             catch (Exception ex)  
  99.             {  
  100.                 lblMessage.Text = "Check your input data";  
  101.                 lblMessage.ForeColor = System.Drawing.Color.Red;    
  102.             }  
  103.             finally  
  104.             {  
  105.                 CloseConnection();  
  106.                 DisposeConnection();  
  107.             }  
  108.         }  
  109.   
  110.         protected void grvEmployee_RowEditing(object sender, GridViewEditEventArgs e)  
  111.         {  
  112.             grvEmployee.EditIndex = e.NewEditIndex;  
  113.             BindEmployeeData();  
  114.         }  
  115.   
  116.         protected void grvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)  
  117.         {  
  118.             try  
  119.             {  
  120.                 CreateConnection();  
  121.                 OpenConnection();  
  122.               Label id = (Label)grvEmployee.Rows[e.RowIndex].FindControl("lblEmpId");  
  123.                 _sqlCommand.CommandText = "usp_GridViewExample";  
  124.                 _sqlCommand.Parameters.AddWithValue("@Event", "Delete");  
  125.                 _sqlCommand.Parameters.AddWithValue("@EmpId", Convert.ToInt32(id.Text));  
  126.                 _sqlCommand.CommandType = CommandType.StoredProcedure;                                 
  127.                 int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());  
  128.                 if (result > 0)  
  129.                 {  
  130.                     lblMessage.Text = "Record Deleted Successfully";  
  131.                     lblMessage.ForeColor = System.Drawing.Color.Green;  
  132.                     grvEmployee.EditIndex = -1;  
  133.                     BindEmployeeData();  
  134.                 }  
  135.                 else  
  136.                 {  
  137.                     lblMessage.Text = "Failed";  
  138.                     lblMessage.ForeColor = System.Drawing.Color.Red;  
  139.                     BindEmployeeData();  
  140.                 }  
  141.             }  
  142.             catch (Exception ex)  
  143.             {  
  144.                 lblMessage.Text = "Check your input data";  
  145.                 lblMessage.ForeColor = System.Drawing.Color.Red;  
  146.             }  
  147.             finally  
  148.             {  
  149.                 CloseConnection();  
  150.                 DisposeConnection();  
  151.             }  
  152.         }  
  153.   
  154.         protected void grvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)  
  155.         {  
  156.             try  
  157.             {  
  158.                 CreateConnection();  
  159.                 OpenConnection();  
  160.   
  161.                 Label Empid = (Label)grvEmployee.Rows[e.RowIndex].FindControl("lblEmpId");  
  162.                 TextBox txtFirstName = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtFirstName");  
  163.                 TextBox txtLastName = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtLastName");  
  164.                 TextBox txtPhoneNumber = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtPhoneNumber");  
  165.                 TextBox txtEmailAddress = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtEmailAddress");  
  166.                 TextBox txtSalary = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtSalary");  
  167.   
  168.   
  169.                 _sqlCommand.CommandText = "usp_GridViewExample";  
  170.                 _sqlCommand.CommandType = CommandType.StoredProcedure;  
  171.                 _sqlCommand.Parameters.AddWithValue("@Event", "Update");  
  172.                 _sqlCommand.Parameters.AddWithValue("@FirstName", Convert.ToString(txtFirstName.Text.Trim()));  
  173.                 _sqlCommand.Parameters.AddWithValue("@LastName", Convert.ToString(txtLastName.Text.Trim()));  
  174.                 _sqlCommand.Parameters.AddWithValue("@PhoneNumber", Convert.ToString(txtPhoneNumber.Text.Trim()));  
  175.                 _sqlCommand.Parameters.AddWithValue("@EmailAddress", Convert.ToString(txtEmailAddress.Text.Trim()));  
  176.                 _sqlCommand.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text));  
  177.                 _sqlCommand.Parameters.AddWithValue("@EmpId", Convert.ToDecimal(Empid.Text));  
  178.   
  179.                 int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());  
  180.                 if (result > 0)  
  181.                 {  
  182.                     lblMessage.Text = "Record Updated Successfully";  
  183.                     lblMessage.ForeColor = System.Drawing.Color.Green;  
  184.                     grvEmployee.EditIndex = -1;  
  185.                     BindEmployeeData();  
  186.                 }  
  187.                 else  
  188.                 {  
  189.                     lblMessage.Text = "Failed";  
  190.                     lblMessage.ForeColor = System.Drawing.Color.Red;  
  191.                 }  
  192.             }  
  193.             catch (Exception ex)  
  194.             {  
  195.                 lblMessage.Text = "Check your input data";  
  196.                 lblMessage.ForeColor = System.Drawing.Color.Red;  
  197.             }  
  198.             finally  
  199.             {  
  200.                 CloseConnection();  
  201.                 DisposeConnection();  
  202.             }  
  203.         }  
  204.   
  205.         protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)  
  206.         {  
  207.             grvEmployee.EditIndex = -1;  
  208.             BindEmployeeData();  
  209.         }  
  210.   
  211.         protected void grvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)  
  212.         {  
  213.             grvEmployee.PageIndex = e.NewPageIndex;  
  214.             BindEmployeeData();  
  215.         }  
  216.     }  
  217. }  
Conclusion

Today we learned how to perform CRUD operation in ASP.NET GridView using Stored Procedure with Validation. 


Similar Articles