📋 Assignment: Building a .
NET Console Application with Dapper
for Employee Management
🎯 Objective:
Develop a .NET Console Application that connects to a SQL Server
database using Dapper to perform CRUD (Create, Read, Update,
Delete) operations on an Employee table. The application should be
menu-driven, allowing users to interact with the database easily.
❓ Assignment Requirements & Questions:
🚀 Part 1: Project Setup
1. .NET Console Application:
o How do you create a .NET Console Application in VS Code?
o Provide the command to scaffold the project structure.
2. Package Installation:
o How do you install the following NuGet packages?
Dapper
System.Data.SqlClient
o What are the roles of these packages in the application?
3. SQL Server Connection:
o How do you establish a connection between the .NET console
app and SQL Server Management Studio (SSMS)?
o Write the structure of a connection string required for SQL
Server.
📊 Part 2: Database Setup (SQL Server)
4. Database and Table Creation:
o How do you create a sample database named EmployeeDB in
SQL Server?
o Write the SQL script to create an Employee table with the
following columns:
Id (Primary Key, INT, Identity)
FirstName (NVARCHAR(50))
LastName (NVARCHAR(50))
Email (NVARCHAR(100))
Salary (DECIMAL(10,2))
5. Stored Procedures:
o Create stored procedures for the following operations:
InsertEmployee
GetAllEmployees
GetEmployeeById
UpdateEmployee
DeleteEmployee
o How do stored procedures improve performance and security
in SQL Server?
⚙️Part 3: Backend Development (C# with Dapper)
6. Entity Class:
o Create an Employee.cs class that maps to the Employee table
in SQL Server.
o Define properties that match the table structure.
7. Database Operations (CRUD):
o Implement the following functions in C# using Dapper:
InsertEmployee() – Adds a new employee to the
database
GetAllEmployees() – Retrieves all employees
GetEmployeeById(int id) – Retrieves an employee by
their ID
UpdateEmployee(int id) – Updates an employee’s details
DeleteEmployee(int id) – Deletes an employee based on
ID
8. Using Dapper:
o How do you execute a stored procedure using Dapper’s Query
and Execute methods?
o Provide code snippets to demonstrate data retrieval and
insertion.
Part 4: User Interaction (Menu-Driven Console App)
9. Console Menu:
o Design a menu-driven interface that offers the following
options:
1️⃣ Insert a New Employee
2️⃣ Fetch All Employees
3️⃣ Fetch Employee by ID
4️⃣ Update Employee Details
5️⃣ Delete an Employee
6️⃣ Exit
10. Input Handling:
o How do you validate user inputs (e.g., checking for empty
fields or invalid IDs)?
o Implement error handling for database connection issues or
failed operations.
🎯 Bonus Tasks (Optional):
Implement search functionality to find employees by their first or
last name.
Add logging to track database operations and errors.
Apply transaction management for critical operations like
updates and deletes.
📊 Deliverables:
1. Complete source code for the .NET Console Application.
2. SQL scripts for:
o Database and Employee table creation
o Stored procedures
3. Screenshots of:
o Console output for each CRUD operation
o SQL Server showing data changes after operations
4. Documentation (PDF/Word) covering:
o Application architecture
o Setup instructions for SQL Server and the console app
o Sample input/output examples
✅ Evaluation Criteria:
Correct implementation of CRUD operations with Dapper (30%)
Proper integration with SQL Server using stored procedures (20%)
Code quality and error handling (15%)
User-friendly console interface (15%)
Documentation and clarity (10%)
Bonus features (10%)
