Dapper — Essentials

What is Dapper?

• Micro-ORM for .NET: thin abstraction over ADO.NET.
• Fast and lightweight: focuses on mapping rows to objects.
• You write SQL: Dapper executes and maps results to your types.

Setup

1. Install packages:

   dotnet add package Dapper
   dotnet add package Microsoft.Data.SqlClient

2. Connection string:

   Data Source=ELIZA\SQLEXPRESS; Initial Catalog=HealthcareSystem; Integrated Security=True; TrustServerCertificate=True

Entity model: Doctor

namespace HandsOnDapper
{
    public class Doctor
    {
        public int DoctorId { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Specialization { get; set; } = string.Empty;
        public string? Qualification { get; set; }
        public string? LicenseNumber { get; set; }
        public int ExperienceYears { get; set; }
        public string? PhoneNumber { get; set; }
        public TimeSpan AvailabilityStart { get; set; }
        public TimeSpan AvailabilityEnd { get; set; }
    }
}

Repository: DoctorRepository

using Dapper;
using System;
using System.Collections.Generic;
using Microsoft.Data.SqlClient; // ✅ Use this instead

namespace HandsOnDapper
{
    public class DoctorRepository
    {
        private string connectionString = "Data Source=ELIZA\\SQLEXPRESS; Initial Catalog=HealthcareSystem; Integrated Security=True; TrustServerCertificate=True";

        // READ
        public IEnumerable<Doctor> GetAllDoctors()
        {
            using var conn = new SqlConnection(connectionString);
            string query = "SELECT * FROM Doctors";
            return conn.Query<Doctor>(query);
        }

        // INSERT
        public void AddDoctor(Doctor doctor)
        {
            using var conn = new SqlConnection(connectionString);
            string query = @"INSERT INTO Doctors (Name, Specialization, Qualification, LicenseNumber, ExperienceYears, PhoneNumber, AvailabilityStart, AvailabilityEnd)
                             VALUES (@Name, @Specialization, @Qualification, @LicenseNumber, @ExperienceYears, @PhoneNumber, @AvailabilityStart, @AvailabilityEnd)";
            conn.Execute(query, doctor);
        }

        // UPDATE
        public void UpdateDoctor(Doctor doctor)
        {
            using var conn = new SqlConnection(connectionString);
            string query = @"UPDATE Doctors 
                             SET Name = @Name, Specialization = @Specialization 
                             WHERE DoctorId = @DoctorId";
            conn.Execute(query, doctor);
        }

        // DELETE
        public void DeleteDoctor(int doctorId)
        {
            using var conn = new SqlConnection(connectionString);
            string query = "DELETE FROM Doctors WHERE DoctorId = @DoctorId";
            conn.Execute(query, new { DoctorId = doctorId });
        }
    }
}

Program.cs

using System;
namespace HandsOnDapper
{
   internal class Program
    {
        static void Main(string[] args)
        {
            var repo = new DoctorRepository();

            // Insert
            repo.AddDoctor(new Doctor
            {
                Name = "Dr. Kavita Sharma",
                Specialization = "Dermatologist",
                Qualification = "MBBS, DDVL",
                LicenseNumber = "LIC10293",
                ExperienceYears = 14,
                PhoneNumber = "9876543210",
                AvailabilityStart = new TimeSpan(10, 0, 0),
                AvailabilityEnd = new TimeSpan(13, 0, 0)
            });

            // Read
            var doctors = repo.GetAllDoctors();
            foreach (var doc in doctors)
            {
                Console.WriteLine($"{doc.DoctorId}: {doc.Name}, {doc.Specialization}");
            }

            // Update
            repo.UpdateDoctor(new Doctor { DoctorId = 10, Name = "Dr. Harish Iyer", Specialization = "Updated Specialist" });

            // Delete
            repo.DeleteDoctor(9);
        }
    }
}

Notes

• Dapper maps columns by name to properties (case-insensitive).
• Use Query/QueryFirstOrDefault for reads and Execute for non-queries.
• Prefer Microsoft.Data.SqlClient provider for SQL Server.