How to Insert, Update, Delete and Read in ASP.NET using SQL Server 2022 & Bootstrap – CRUD App in ASP.NET [With Source Code]
designing code:
<title>CRUD APP</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">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
<style>
.bgColor{
background:#99b4d1
}
</style>
</head>
<body>
<div class="container text-center">
<div class="row">
<div class="col">
</div>
<div class="col-lg-5 bgColor rounded">
<div class="bg-primary p-4 mb-3 mt-3">
<h1 class="text-light text-center">CRUD Application</h1>
</div>
<form runat="server" method="post">
<asp:GridView ID="GridView1" CssClass="table table-dark" runat="server"></asp:GridView>
<div class="mb-3">
<asp:TextBox ID="txtID" CssClass="form-control" placeholder="ID" runat="server"></asp:TextBox>
</div>
<div class="mb-3">
<asp:TextBox ID="txtName" CssClass="form-control" placeholder="Full Name" runat="server"></asp:TextBox>
</div>
<div class="mb-3">
<asp:TextBox ID="txtEmail" TextMode="Email" CssClass="form-control" placeholder="email@email.com" runat="server"></asp:TextBox>
</div>
<div class="mb-3">
<asp:TextBox ID="txtUser" CssClass="form-control" placeholder="Username" runat="server"></asp:TextBox>
</div>
<div class="mb-3">
<asp:TextBox ID="txtPass" CssClass="form-control" TextMode="Password" placeholder="password" runat="server"></asp:TextBox>
</div>
<div class="d-grid gap-2 d-md-flex mb-3">
<asp:HyperLink ID="HyperLink1" CssClass="btn btn-secondary" href="crud.aspx" runat="server">Home</asp:HyperLink>
<asp:Button ID="Button4" CssClass="btn btn-primary" runat="server" Text="Create" OnClick="Button4_Click" />
<asp:Button ID="Button1" CssClass="btn btn-success" runat="server" Text="Read" OnClick="Button1_Click" />
<asp:Button ID="Button2" CssClass="btn btn-warning" runat="server" Text="Update" OnClick="Button2_Click" />
<asp:Button ID="Button3" CssClass="btn btn-danger" runat="server" Text="Delete" OnClick="Button3_Click" />
</div>
</form>
</div>
<div class="col">
</div>
</div>
</div>
</body>
backend code:
1. insert (create) code: [inside the clicking event of create button]
SqlConnection con = new SqlConnection("Data Source=RAN\\SQLEXPRESS;Initial Catalog=aspcrud;Integrated Security=True;TrustServerCertificate=True");
con.Open();
string createQuery = "INSERT INTO aspcrud (name, email, username, password) VALUES (@name, @email, @user, @pass)";
SqlCommand cmd = new SqlCommand(createQuery, con);
cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@user", txtUser.Text);
cmd.Parameters.AddWithValue("@pass", txtPass.Text);
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
Response.Write("<script type='text/javascript'>alert('Created successfully')</script>");
}
2. read code:
a. hiding textbox code:
private void HideControls(Control control)
{
foreach(Control ctrl in control.Controls)
{
if(ctrl is TextBox)
{
ctrl.Visible = false;
}
else if(ctrl is GridView)
{
ctrl.Visible = true;
}
else if (ctrl.HasControls())
{
HideControls(ctrl);
}
else
{
ctrl.Visible = true;
}
}
}
b. read (Select) code: [inside the clicking event of read button]
HideControls(this);
SqlConnection con = new SqlConnection("Data Source=RAN\\SQLEXPRESS;Initial Catalog=aspcrud;Integrated Security=True;TrustServerCertificate=True");
con.Open();
string readQuery = "SELECT * FROM aspcrud";
SqlCommand cmd = new SqlCommand(readQuery, con);
SqlDataReader reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
3. update code: [inside the clicking event of update button]
SqlConnection con = new SqlConnection("Data Source=RAN\\SQLEXPRESS;Initial Catalog=aspcrud;Integrated Security=True;TrustServerCertificate=True");
con.Open();
string updateQuery = "UPDATE aspcrud SET name=@name, email=@email, username=@user, password=@pass WHERE id=@id";
SqlCommand cmd = new SqlCommand(updateQuery, con);
cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@user", txtUser.Text);
cmd.Parameters.AddWithValue("@pass", txtPass.Text);
cmd.Parameters.AddWithValue("@id", txtID.Text);
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
Response.Write("<script type='text/javascript'>alert('updated successfully')</script>");
}
4. delete code: [inside the clicking event of delete button]
SqlConnection con = new SqlConnection("Data Source=RAN\\SQLEXPRESS;Initial Catalog=aspcrud;Integrated Security=True;TrustServerCertificate=True");
con.Open();
string deleteQuery = "DELETE FROM aspcrud WHERE id=@id";
SqlCommand cmd = new SqlCommand(deleteQuery, con);
cmd.Parameters.AddWithValue("@id",txtID.Text);
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
Response.Write("<script type='text/javascript'>alert('deleted successfully')</script>");
}
MORE: