This web application demonstrates basic CRUD operations (Insert, Update, Delete) using a SQL database, helping students understand how employee data is managed dynamically through a web interface.
Slip 4 Q.B) Problem Statement
Q. Create a web application to insert 3 records into a SQL database table with the following fields: (DeptId, DeptName, EmpName, Salary). Update the salary for any one employee, increasing it by 15% of the present salary. Perform a delete operation on one row of the database table. 25 Marks
Answer:
Steps:
1.Create a SQL Server Table: First, create a table in your SQL Server database:
CREATE TABLE DepartmentEmployees ( DeptId INT PRIMARY KEY, DeptName NVARCHAR(50), EmpName NVARCHAR(50), Salary DECIMAL(10, 2) );
2.Create an ASP.NET Web Application: Now, create a new ASP.NET Web Forms application. Follow these steps:
- Open Visual Studio and create a new ASP.NET Web Application.
- Choose Web Forms as the template.
Source Code
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Database Operations</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>Insert Records into Database</h2>
<button type="button" onclick="insertRecords()">Insert Records</button>
<br />
<h2>Update Salary by 15%</h2>
<button type="button" onclick="updateSalary()">Update Salary</button>
<br />
<h2>Delete a Record</h2>
<button type="button" onclick="deleteRecord()">Delete Record</button>
</div>
</form>
</body>
<script>
function insertRecords() {
window.location.href = "Default.aspx/InsertRecords";
}
function updateSalary() {
window.location.href = "Default.aspx/UpdateSalary";
}
function deleteRecord() {
window.location.href = "Default.aspx/DeleteRecord";
}
</script>
</html>Default.aspx.cs (Code-behind logic):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
namespace WebApplication1
{
public partial class Default : Page
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
// Check for any commands triggered via AJAX calls (like Insert, Update, Delete).
string command = Request.QueryString["command"];
if (command == "InsertRecords")
{
InsertRecords();
}
else if (command == "UpdateSalary")
{
UpdateSalary();
}
else if (command == "DeleteRecord")
{
DeleteRecord();
}
}
private void InsertRecords()
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string insertQuery = "INSERT INTO DepartmentEmployees (DeptId, DeptName, EmpName, Salary) VALUES (@DeptId, @DeptName, @EmpName, @Salary)";
SqlCommand cmd = new SqlCommand(insertQuery, con);
cmd.Parameters.AddWithValue("@DeptId", 1);
cmd.Parameters.AddWithValue("@DeptName", "HR");
cmd.Parameters.AddWithValue("@EmpName", "John Doe");
cmd.Parameters.AddWithValue("@Salary", 5000);
cmd.ExecuteNonQuery();
cmd.Parameters["@DeptId"].Value = 2;
cmd.Parameters["@DeptName"].Value = "IT";
cmd.Parameters["@EmpName"].Value = "Jane Smith";
cmd.Parameters["@Salary"].Value = 6000;
cmd.ExecuteNonQuery();
cmd.Parameters["@DeptId"].Value = 3;
cmd.Parameters["@DeptName"].Value = "Finance";
cmd.Parameters["@EmpName"].Value = "Emily Johnson";
cmd.Parameters["@Salary"].Value = 7000;
cmd.ExecuteNonQuery();
}
}
private void UpdateSalary()
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string updateQuery = "UPDATE DepartmentEmployees SET Salary = Salary * 1.15 WHERE EmpName = @EmpName";
SqlCommand cmd = new SqlCommand(updateQuery, con);
cmd.Parameters.AddWithValue("@EmpName", "John Doe");
cmd.ExecuteNonQuery();
}
}
private void DeleteRecord()
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string deleteQuery = "DELETE FROM DepartmentEmployees WHERE DeptId = @DeptId";
SqlCommand cmd = new SqlCommand(deleteQuery, con);
cmd.Parameters.AddWithValue("@DeptId", 1); // Delete DeptId 1 as an example
cmd.ExecuteNonQuery();
}
}
}
}FAQ Section
1. What is CRUD operation in a database?
CRUD stands for Create, Read, Update, and Delete. These are the four basic operations used to manage data in a database.
2. Why do we use SQL for database management?
SQL is used because it allows us to store, retrieve, update, and delete data easily and efficiently from a relational database.
3. What is the purpose of the UPDATE statement?
The UPDATE statement is used to modify existing data in a database table.
4. How is the 15% salary increment calculated?
The new salary is calculated by adding 15% of the current salary to the existing salary.
Formula:New Salary = Salary + (Salary × 15 / 100)
5. What happens if the WHERE clause is not used in an UPDATE query?
If the WHERE clause is not used, all records in the table will be updated, which may cause data loss or errors.