Gridview insert update delete using stored procedures in Asp.Net | crud using stored procedure c#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace CRUDGridView_StoredProcedure
{
public partial class GridViewCRUD : System.Web.UI.Page
{
string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetData();
}
}
private void GetData()
{
try
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetData", con);
con.Open();
empgrid.DataSource = cmd.ExecuteReader();
empgrid.DataBind();
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
}
protected void btnsubmit_Click(object sender, EventArgs e)
{
try
{
using(SqlConnection con = new SqlConnection(cs))
{
string fname = txtfname.Text;
string lname = txtlname.Text;
int age = Convert.ToInt32(txtage.Text);
int sal = Convert.ToInt32(txtsalary.Text);
SqlCommand cmd = new SqlCommand("spInsert",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@First_Name",fname);
cmd.Parameters.AddWithValue("@Last_Name",lname);
cmd.Parameters.AddWithValue("@Age",age);
cmd.Parameters.AddWithValue("@Salary",sal);
con.Open();
cmd.ExecuteNonQuery();
GetData();
lblmsg.Text = "Record Inserted!..";
txtfname.Text = "";
txtlname.Text = "";
txtage.Text = "";
txtsalary.Text = "";
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
}
protected void empgrid_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = Convert.ToInt32(empgrid.DataKeys[e.RowIndex].Value);
try
{
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid",id);
con.Open();
cmd.ExecuteNonQuery();
GetData();
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
}
protected void empgrid_RowEditing(object sender, GridViewEditEventArgs e)
{
empgrid.EditIndex = e.NewEditIndex;
GetData();
}
protected void empgrid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
empgrid.EditIndex = -1;
GetData();
}
protected void empgrid_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
try
{
using (SqlConnection con = new SqlConnection(cs))
{
int id = Convert.ToInt32(empgrid.DataKeys[e.RowIndex].Value);
string fname = (empgrid.Rows[e.RowIndex].FindControl("txtfname") as TextBox).Text;
string lname = (empgrid.Rows[e.RowIndex].FindControl("txtlname") as TextBox).Text;
int age = Convert.ToInt32((empgrid.Rows[e.RowIndex].FindControl("txtage") as TextBox).Text);
int sal = Convert.ToInt32((empgrid.Rows[e.RowIndex].FindControl("txtsalary") as TextBox).Text);
SqlCommand cmd = new SqlCommand("spUpdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@First_Name", fname);
cmd.Parameters.AddWithValue("@Last_Name", lname);
cmd.Parameters.AddWithValue("@Age", age);
cmd.Parameters.AddWithValue("@Salary", sal);
cmd.Parameters.AddWithValue("@Empid",id);
con.Open();
cmd.ExecuteNonQuery();
empgrid.EditIndex = -1;
GetData();
lblmsg.Text = "Record Updated!..";
txtfname.Text = "";
txtlname.Text = "";
txtage.Text = "";
txtsalary.Text = "";
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
}
}
}
Above File is CRUDGridView_StoredProcedure\GridViewCRUD.aspx.cs
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewCRUD.aspx.cs" Inherits="CRUDGridView_StoredProcedure.GridViewCRUD" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous"/>
</head>
<body>
<form id="form1" runat="server">
<div class="col-md-8 col-md-offset-2">
<div class="form-group">
<div class="row">
<div class="col-md-2">
<label>First Name</label>
</div>
<div class="col-md-10">
<asp:TextBox ID="txtfname" CssClass="form-control" runat="server"></asp:TextBox>
</div>
</div>
</div>
<div class="form-gruop">
<div class="row">
<div class="col-md-2">
<label>Last Name</label>
</div>
<div class="col-md-10">
<asp:TextBox ID="txtlname" CssClass="form-control" runat="server"></asp:TextBox>
</div>
</div>
</div>
<div class="form-group">
<div class="row">
<div class="col-md-2">
<label>Age</label>
</div>
<div class="col-md-10">
<asp:TextBox ID="txtage" CssClass="form-control" runat="server"></asp:TextBox>
</div>
</div>
</div>
<div class="form-group">
<div class="row">
<div class="col-md-2">
<label>Salary</label>
</div>
<div class="col-md-10">
<asp:TextBox ID="txtsalary" CssClass="form-control" runat="server"></asp:TextBox>
</div>
</div>
</div>
<div class="form-group">
<div class="row">
<div class="col-md-2 col-md-offset-1">
<asp:Button runat="server" ID="btnsubmit" CssClass="btn btn-block btn-success" Text="Submit" OnClick="btnsubmit_Click"/>
</div>
<div class="col-md-2">
<asp:Button runat="server" ID="btnclear" CssClass="btn btn-block btn-danger" Text="Clear"/>
</div>
</div>
</div>
<br/>
<asp:Label runat="server" ID="lblmsg"></asp:Label>
</div>
<div class="col-md-8 col-md-offset-2">
<asp:GridView ID="empgrid" runat="server" CssClass="table" OnRowDeleting="empgrid_RowDeleting" OnRowEditing="empgrid_RowEditing" OnRowCancelingEdit="empgrid_RowCancelingEdit" OnRowUpdating="empgrid_RowUpdating" DataKeyNames="Empid" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="Employee ID" DataField="Empid" ReadOnly="true"/>
<asp:TemplateField HeaderText="First Name">
<ItemTemplate>
<asp:Label runat="server" Text='<%# Eval("First_Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtfname" runat="server" Text='<%# Bind("First_Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name">
<ItemTemplate>
<asp:Label runat="server" Text='<%# Eval("Last_Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtlname" runat="server" Text='<%# Bind("Last_Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Age">
<ItemTemplate>
<asp:Label runat="server" Text='<%# Eval("Age") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtage" runat="server" Text='<%# Bind("Age") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label runat="server" Text='<%# Eval("Salary") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtsalary" runat="server" Text='<%# Bind("Salary") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="true" ControlStyle-CssClass="btn btn-danger" HeaderText="Remove Record"/>
<asp:CommandField ShowEditButton="true" ControlStyle-CssClass="btn btn-primary" HeaderText="Edit Record"/>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
Comments
Post a Comment