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.

By Chaitrali / March 7, 2025

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.

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();
            }
        }
    }
}