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
  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
  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
  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">
  5. <head runat="server">
  6. <title></title>
  7. <style>
  8. td {
  9. align=center;
  10. }
  11. </style>
  12. </head>
  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>
  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;
  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. }
  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. }
  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. }
  110. protected void grvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
  111. {
  112. grvEmployee.EditIndex = e.NewEditIndex;
  113. BindEmployeeData();
  114. }
  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. }
  154. protected void grvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
  155. {
  156. try
  157. {
  158. CreateConnection();
  159. OpenConnection();
  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");
  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));
  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. }
  205. protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
  206. {
  207. grvEmployee.EditIndex = -1;
  208. BindEmployeeData();
  209. }
  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.