CRUD Operation In ASP.NET GridView Using Single Stored Procedure

Mukesh Kumar
9y
11.9k
0
2

Article

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.

CREATE PROC [dbo].[usp_GridViewExample]
(
@EmpId int=0,@FirstName varchar(50)=Null,@LastName varchar(50)=Null,@PhoneNumber nvarchar(15)=Null,
@EmailAddress nvarchar(50)=Null,@Salary decimal=Null,@Event varchar(10)
)
AS
BEGIN
IF(@Event='Select')
BEGIN
SELECT * FROM Employee ORDER BY FirstName ASC;
END
ELSE IF(@Event='Add')
BEGIN
INSERT INTO Employee (FirstName,LastName,PhoneNumber,EmailAddress,Salary,CreatedDate) VALUES(@FirstName,@LastName,@PhoneNumber,@EmailAddress,@Salary,GETDATE());
END
ELSE IF(@Event='Update')
BEGIN
UPDATE Employee SET FirstName=@FirstName,LastName=@LastName,PhoneNumber=@PhoneNumber,EmailAddress=@EmailAddress,Salary=@Salary where Id=@EmpId;
END
ELSE
BEGIN
DELETE FROM Employee WHERE Id=@EmpId;
END
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,

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewDemo.aspx.cs" Inherits="GridViewDemo.GridViewDemo" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
td {
align=center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<div style="border: 1px Solid #0094ff; width: 70%;">
<table>
<tr>
<td colspan="3" align="center">
<h2>Employee Management System</h2> </td>
</tr>
<tr>
<td>First Name </td>
<td>
<asp:TextBox runat="server" ID="txtFirstName" ValidationGroup="add"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator runat="server" ID="reqFirstName" ControlToValidate="txtFirstName" ForeColor="Red" ErrorMessage="First Name Requierd" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Last Name </td>
<td>
<asp:TextBox runat="server" ID="txtLastName" ValidationGroup="add"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator runat="server" ID="reqLastName" ControlToValidate="txtLastName" ForeColor="Red" ErrorMessage="Last Name Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Phone Number </td>
<td>
<asp:TextBox runat="server" ID="txtPhoneNumber"></asp:TextBox>
</td>
<td></td>
</tr>
<tr>
<td>Emial Address </td>
<td>
<asp:TextBox runat="server" ID="txtEmailAddress" ValidationGroup="add"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator runat="server" ID="reqEmailAddress" ControlToValidate="txtEmailAddress" ForeColor="Red" ErrorMessage="Email Address Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>Salary </td>
<td>
<asp:TextBox runat="server" ID="txtSalary" ValidationGroup="add"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator runat="server" ID="reqSalary" ControlToValidate="txtSalary" ForeColor="Red" ErrorMessage="Salary Required" EnableClientScript="true" ValidationGroup="add"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td colspan="3" align="center">
<asp:Button runat="server" ID="btnAddEmployee" Text="Add" OnClick="btnAddEmployee_Click" Style="height: 26px" Width="37px" ValidationGroup="add" /> </td>
</tr>
<tr>
<td colspan="3" align="center">
<br />
<asp:Label runat="server" ID="lblMessage"></asp:Label>
<br />
<br /> </td>
</tr>
<tr>
<td colspan="3">
<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">
<Columns>
<asp:TemplateField HeaderText="EmpId">
<ItemTemplate>
<asp:Label runat="server" ID="lblEmpId" Text='<%#Eval("id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FirstName">
<ItemTemplate>
<asp:Label runat="server" ID="lblFirstName" Text='<%#Eval("FirstName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="txtFirstName" Text='<%#Eval("FirstName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LastName">
<ItemTemplate>
<asp:Label runat="server" ID="lblLastName" Text='<%#Eval("LastName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="txtLastName" Text='<%#Eval("LastName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Phone No.">
<ItemTemplate>
<asp:Label runat="server" ID="lblPhoneNumber" Text='<%#Eval("PhoneNumber") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="txtPhoneNumber" Text='<%#Eval("PhoneNumber") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email">
<ItemTemplate>
<asp:Label runat="server" ID="lblEmailAddress" Text='<%#Eval("EmailAddress") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="txtEmailAddress" Text='<%#Eval("EmailAddress") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label runat="server" ID="lblSalary" Text='<%#Eval("Salary") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox runat="server" ID="txtSalary" Text='<%#Eval("Salary") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Manage">
<ItemTemplate>
<asp:LinkButton runat="server" ID="btnEdit" Text="Edit" CommandName="Edit" />
<br /> <span onclick="return confirm('Are you sure you want to delete this record?')">
<asp:LinkButton runat="server" ID="btnDelete" Text="Delete" CommandName="Delete" />
</span> </ItemTemplate>
<EditItemTemplate>
<asp:LinkButton runat="server" ID="btnUpdate" Text="Update" CommandName="Update" />
<br />
<asp:LinkButton runat="server" ID="btnCancel" Text="Cancel" CommandName="Cancel" /> </EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</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>
</body>
</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.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace GridViewDemo
{
public partial class GridViewDemo : System.Web.UI.Page
{
private string strConnectionString = ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString;
private SqlCommand _sqlCommand;
private SqlDataAdapter _sqlDataAdapter;
DataSet _dtSet;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeeData();
}
}
public void CreateConnection()
{
SqlConnection _sqlConnection = new SqlConnection(strConnectionString);
_sqlCommand = new SqlCommand();
_sqlCommand.Connection = _sqlConnection;
}
public void OpenConnection()
{
_sqlCommand.Connection.Open();
}
public void CloseConnection()
{
_sqlCommand.Connection.Close();
}
public void DisposeConnection()
{
_sqlCommand.Connection.Dispose();
}
public void BindEmployeeData()
{
try
{
CreateConnection();
OpenConnection();
_sqlCommand.CommandText = "usp_GridViewExample";
_sqlCommand.CommandType = CommandType.StoredProcedure;
_sqlCommand.Parameters.AddWithValue("@Event", "Select");
_sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
_dtSet = new DataSet();
_sqlDataAdapter.Fill(_dtSet);
grvEmployee.DataSource = _dtSet;
grvEmployee.DataBind();
}
catch (Exception ex)
{
Response.Redirect("The Error is " + ex);
}
finally
{
CloseConnection();
DisposeConnection();
}
}
protected void btnAddEmployee_Click(object sender, EventArgs e)
{
try
{
CreateConnection();
OpenConnection();
_sqlCommand.CommandText = "usp_GridViewExample";
_sqlCommand.CommandType = CommandType.StoredProcedure;
_sqlCommand.Parameters.AddWithValue("@Event", "Add");
_sqlCommand.Parameters.AddWithValue("@FirstName", Convert.ToString(txtFirstName.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@LastName", Convert.ToString(txtLastName.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@PhoneNumber", Convert.ToString(txtPhoneNumber.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@EmailAddress", Convert.ToString(txtEmailAddress.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text));
int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
if (result > 0)
{
lblMessage.Text = "Record Added Successfully";
lblMessage.ForeColor = System.Drawing.Color.Green;
BindEmployeeData();
}
else
{
lblMessage.Text = "Failed";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
lblMessage.Text = "Check your input data";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
finally
{
CloseConnection();
DisposeConnection();
}
}
protected void grvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
grvEmployee.EditIndex = e.NewEditIndex;
BindEmployeeData();
}
protected void grvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
CreateConnection();
OpenConnection();
Label id = (Label)grvEmployee.Rows[e.RowIndex].FindControl("lblEmpId");
_sqlCommand.CommandText = "usp_GridViewExample";
_sqlCommand.Parameters.AddWithValue("@Event", "Delete");
_sqlCommand.Parameters.AddWithValue("@EmpId", Convert.ToInt32(id.Text));
_sqlCommand.CommandType = CommandType.StoredProcedure;
int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
if (result > 0)
{
lblMessage.Text = "Record Deleted Successfully";
lblMessage.ForeColor = System.Drawing.Color.Green;
grvEmployee.EditIndex = -1;
BindEmployeeData();
}
else
{
lblMessage.Text = "Failed";
lblMessage.ForeColor = System.Drawing.Color.Red;
BindEmployeeData();
}
}
catch (Exception ex)
{
lblMessage.Text = "Check your input data";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
finally
{
CloseConnection();
DisposeConnection();
}
}
protected void grvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{
CreateConnection();
OpenConnection();
Label Empid = (Label)grvEmployee.Rows[e.RowIndex].FindControl("lblEmpId");
TextBox txtFirstName = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtFirstName");
TextBox txtLastName = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtLastName");
TextBox txtPhoneNumber = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtPhoneNumber");
TextBox txtEmailAddress = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtEmailAddress");
TextBox txtSalary = (TextBox)grvEmployee.Rows[e.RowIndex].FindControl("txtSalary");
_sqlCommand.CommandText = "usp_GridViewExample";
_sqlCommand.CommandType = CommandType.StoredProcedure;
_sqlCommand.Parameters.AddWithValue("@Event", "Update");
_sqlCommand.Parameters.AddWithValue("@FirstName", Convert.ToString(txtFirstName.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@LastName", Convert.ToString(txtLastName.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@PhoneNumber", Convert.ToString(txtPhoneNumber.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@EmailAddress", Convert.ToString(txtEmailAddress.Text.Trim()));
_sqlCommand.Parameters.AddWithValue("@Salary", Convert.ToDecimal(txtSalary.Text));
_sqlCommand.Parameters.AddWithValue("@EmpId", Convert.ToDecimal(Empid.Text));
int result = Convert.ToInt32(_sqlCommand.ExecuteNonQuery());
if (result > 0)
{
lblMessage.Text = "Record Updated Successfully";
lblMessage.ForeColor = System.Drawing.Color.Green;
grvEmployee.EditIndex = -1;
BindEmployeeData();
}
else
{
lblMessage.Text = "Failed";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
catch (Exception ex)
{
lblMessage.Text = "Check your input data";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
finally
{
CloseConnection();
DisposeConnection();
}
}
protected void grvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grvEmployee.EditIndex = -1;
BindEmployeeData();
}
protected void grvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grvEmployee.PageIndex = e.NewPageIndex;
BindEmployeeData();
}
}
}