DEV Community: Abdullah Al Mamun Akand

SQL Server Triggers : Made Fun to Learn!

Abdullah Al Mamun Akand — Sun, 10 Aug 2025 17:05:39 +0000

If you want SQL Server to do something automatically whenever data changes, you need Triggers.

Think of a trigger like a security guard for your database:

It’s attached to a table.
Watches for INSERT, UPDATE, DELETE.
When something happens, it runs your code instantly.

What is a Trigger?

A trigger is SQL code that runs by itself when data changes in a table.

Remember:

You don’t run it manually.
It must be linked to a single table.

Types of Triggers

Type	When it Runs	Example Use
AFTER Trigger	After the action finishes	Audit logs, checks
INSTEAD OF Trigger	Replaces the action	For views or blocking
DDL Trigger	When schema changes	Stop unwanted changes
Logon Trigger	On user login	Security checks

Basic Syntax Structure for trigger:

CREATE TRIGGER trigger_name
ON table_name
{AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- Your SQL statements here
END;

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- Your SQL statements here
END;

trigger_name: Name you give to the trigger
table_name: Table the trigger is attached to
AFTER runs after the event
INSTEAD OF runs instead of the event
FOR runs after the event (same as AFTER)
You can specify one or more events: INSERT, UPDATE, DELETE

Magic/Virtual Tables – `INSERTED` & `DELETED`

When a trigger runs, SQL Server gives two temporary tables:

Table	What it has
INSERTED	The new rows added or updated
DELETED	The old rows deleted or updated

How they work:

When INSERT happens → INSERTED has that inserted data, DELETED is then empty.
When DELETE happens → DELETED has that deleted data, INSERTED is then empty.
When UPDATE happens → Both have data. [Update = First Delete + Then Insert]

Example for UPDATE:

Before: Price = 99.99 → After:  Price = 109.99
DELETED.Price = 99.99 → INSERTED.Price = 109.99

Step 1 – Create Products Table

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(200) NOT NULL,
    BrandName VARCHAR(200) NOT NULL,
    ReceiveDate DATE NULL,
    AvailableStock FLOAT NOT NULL,
    Price MONEY NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifyDate DATETIME NOT NULL DEFAULT GETDATE()
);

INSERT INTO Products (ProductName, BrandName, ReceiveDate, AvailableStock, Price)
VALUES 
('Logitech MX Master 3', 'Logitech', '2022-07-18', 100, 99.99),
('Apple Magic Keyboard', 'Apple', '2022-03-28', 200, 199.99);

Step 2 – Create Audit Table

CREATE TABLE AuditRecord
(
    RecordId INT PRIMARY KEY IDENTITY(1,1),
    ActionName VARCHAR(50) NOT NULL,
    TableName VARCHAR(50) NOT NULL,
    ColumnName VARCHAR(50) NULL,
    PreviousValue VARCHAR(500) NULL,
    ModifiedValue VARCHAR(500) NULL,
    PK INT NULL,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE()
);

AFTER INSERT Trigger – Log New Products

CREATE TRIGGER TR_Products_Insert
ON Products
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName)
    VALUES ('Insert', 'Products');
    PRINT 'Insert Trigger Fired!';
END;

Meaning:
If you run:

INSERT INTO Products (ProductName, BrandName, ReceiveDate, AvailableStock, Price)
VALUES ('Dell Wireless Mouse', 'Dell', '2023-06-15', 50, 29.99);

The trigger will add this to AuditRecord:

ActionName	TableName
Insert	Products

Show What’s in `INSERTED` & `DELETED`

ALTER TRIGGER TR_Products_Insert
ON Products
FOR INSERT
AS
BEGIN
    SELECT * FROM INSERTED; -- New data
    SELECT * FROM DELETED; -- Empty for INSERT
END;

Example after insert:
INSERTED → new product row
DELETED → empty

ProductId	ProductName	BrandName	ReceiveDate	AvailableStock	Price	CreateDate	ModifyDate
3	Dell Wireless Mouse	Dell	2023-06-15	50	29.99	2025-08-10 14:32:00	2025-08-10 14:32:00

ProductId	ProductName	BrandName	ReceiveDate	AvailableStock	Price	CreateDate	ModifyDate
(empty)	(empty)	(empty)	(empty)	(empty)	(empty)	(empty)	(empty)

AFTER UPDATE Trigger – Log Price Changes

*Let's keep track of value of a column when data updates. Keep both Previus + New *

ALTER TRIGGER TR_Products_Update
ON Products
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName, PK, ColumnName, PreviousValue, ModifiedValue)
    SELECT 'Update', 'Products', i.ProductId, 'Price', d.Price, i.Price
    FROM INSERTED i
    JOIN DELETED d ON i.ProductId = d.ProductId
    WHERE i.Price <> d.Price;
    PRINT 'Update Trigger Fired!';
END;

Meaning (with example):
If you run:

UPDATE Products
SET Price = 109.99
WHERE ProductId = 1;

Before: Price was 99.99 (in DELETED)
After: Price is 109.99 (in INSERTED)

Audit table will show:

ActionName	TableName	PK	ColumnName	PreviousValue	ModifiedValue
Update	Products	1	Price	99.99	109.99

AFTER DELETE Trigger – Log Deleted Products

CREATE TRIGGER TR_Products_Delete
ON Products
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName, PK)
    SELECT 'Delete', 'Products', d.ProductId
    FROM DELETED d;
    PRINT 'Delete Trigger Fired!';
END;

Meaning (with example):
If you run:

DELETE FROM Products
WHERE ProductId = 2;

Audit table will show:

ActionName	TableName	PK
Delete	Products	2

Drop a Trigger

DROP TRIGGER TR_Products_Insert;

N.B: for Triggers

Keep them short.
Avoid heavy work inside.

Happy coding!
Find me on LinkedIn
Inspired from AurpanDash Bhai.

Interface Segregation Principle (ISP) in SOLID (Simple C# Examples)

Abdullah Al Mamun Akand — Mon, 10 Mar 2025 19:39:07 +0000

Today, let's talk about a simple way to make your C# code cleaner and easier to manage—the Interface Segregation Principle (ISP).

What is the Interface Segregation Principle?

The Interface Segregation Principle (ISP) means:

"A class should not be forced to implement methods it doesn’t need."

In simple words, don’t create big interfaces that make classes do things they don’t need to do. Instead, split them into smaller, more specific ones.

Why is ISP Important?

If we don’t follow ISP, our code becomes:

Hard to maintain – We add unnecessary methods to classes.
Less flexible – A simple change can break multiple classes.
More complicated – Too many unused methods make the code confusing.

Let’s see a real example!

Bad Example (Violating ISP)

Imagine we are designing a system for humans and robots in a workplace. We create a general interface for all workers:

public interface IWorker
{
    void Work();
    void Eat();
    void Sleep();
}

Now, let’s implement it:

public class HumanWorker : IWorker
{
    public void Work() { Console.WriteLine("Human is working."); }
    public void Eat() { Console.WriteLine("Human is eating."); }
    public void Sleep() { Console.WriteLine("Human is sleeping."); }
}

public class RobotWorker : IWorker
{
    public void Work() { Console.WriteLine("Robot is working."); }
    public void Eat() { throw new NotImplementedException(); } // Robots don’t eat
    public void Sleep() { throw new NotImplementedException(); } // Robots don’t sleep
}

What’s Wrong?

RobotWorker has to implement Eat() and Sleep(), even though robots don’t need them.
The interface mixes unrelated actions (working, eating, sleeping) into one.
If we add a new worker type, it might need to implement unnecessary methods too.

Good Example (Following ISP)

Let’s fix this by creating separate interfaces:

public interface IWorkable
{
    void Work();
}

public interface IHumanNeeds
{
    void Eat();
    void Sleep();
}

Now, let’s implement them properly:

public class HumanWorker : IWorkable, IHumanNeeds
{
    public void Work() { Console.WriteLine("Human is working."); }
    public void Eat() { Console.WriteLine("Human is eating."); }
    public void Sleep() { Console.WriteLine("Human is sleeping."); }
}

public class RobotWorker : IWorkable
{
    public void Work() { Console.WriteLine("Robot is working."); }
}

Why is this better?

✅ HumanWorker implements only what it needs, both Work() and human-specific actions like Eat() and Sleep().

✅ RobotWorker only implements Work(), no extra methods.

✅ We follow ISP properly by breaking the big interface into smaller, focused ones.

Final Thoughts

By following ISP, we get:

Cleaner code – No unnecessary methods.
Easier maintenance – Changes won’t affect unrelated classes.
Better flexibility – We can add new features without breaking the existing code.

Next time you design an interface, ask yourself: “Am I forcing a class to implement something it doesn’t need?” If yes, split the interface into smaller parts!

Happy coding!
Find me on LinkedIn

Sending Emails with Gmail Using MailKit in .NET Web API: The Complete Guide

Abdullah Al Mamun Akand — Tue, 18 Feb 2025 19:43:13 +0000

Introduction:

Sending emails programmatically is a common requirement for many web applications. In this blog post, we will walk you through how to send emails using Gmail’s SMTP server in a .NET Core Web API application using MailKit, a modern and lightweight email library for .NET.

By the end of this guide, you'll learn how to set up a Web API, integrate MailKit to send emails, and use configuration settings stored in appsettings.json.

Prerequisites:

Before you begin, ensure you have the following:

.NET 6 or higher installed.
A Gmail account with 2-Step Verification enabled and an App Password generated (since Gmail blocks less secure apps).
Basic knowledge of creating Web API projects in .NET.

Step 1: Set Up Your .NET Web API Project

Open Visual Studio (or your preferred IDE) and create a new Web API project.
Install MailKit via NuGet:

   dotnet add package MailKit

MailKit is a fully featured email client library that allows you to send and receive emails using SMTP, POP3, and IMAP.

Step 2: Add Email Configuration in `appsettings.json`

In your project, open the appsettings.json file and add the configuration for your email settings, like this:

{
  "EmailSettings": {
    "Username": "your-mail@gmail.com",
    "Password": "gmail-generated-app-password",
    "SmtpServer": "smtp.gmail.com",
    "Port": 587
  }
}

Username: Your Gmail email address.
Password: The App Password generated in your Gmail account. (you can create it from your Google Account under Security > App Passwords).
SmtpServer: The SMTP server for Gmail (smtp.gmail.com).
Port: Gmail's SMTP port, which is 587 for TLS encryption.

Step 3: Create the Email Service

We will create a service class called EmailService that will handle email sending.

1. Create an Interface for the service:

In the IRepository folder (or wherever you prefer), create a new file called IEmailService.cs:

   namespace EmailApp.IRepository
   {
       public interface IEmailService
       {
           Task<string> SendEmail(string toEmail, string subject, string body);
       }
   }

2. Create the Email Service class that implements the IEmailService interface.

In the Repository folder, create a new file called EmailService.cs:

   using EmailApp.IRepository;
   using MailKit.Net.Smtp;
   using MailKit.Security;
   using Microsoft.Extensions.Configuration;
   using MimeKit;

   namespace EmailApp.Repository
   {
       public class EmailService : IEmailService
       {
           private readonly IConfiguration _configuration;

           public EmailService(IConfiguration configuration)
           {
               _configuration = configuration;
           }

           public async Task<string> SendEmail(string toEmail, string mailSubject, string mailBody)
           {
               try
               {
                   var smtpServer = _configuration["EmailSettings:SmtpServer"];
                   var port = int.Parse(_configuration["EmailSettings:Port"]);
                   var fromMail = _configuration["EmailSettings:Username"];
                   var password = _configuration["EmailSettings:Password"];

                   var email = new MimeMessage();
                   email.From.Add(new MailboxAddress("Your Name", fromMail));
                   email.To.Add(new MailboxAddress("To Name", toEmail));
                   email.Subject = mailSubject;
                   email.Body = new TextPart("html") { Text = mailBody };

                   using var smtp = new SmtpClient();
                   await smtp.ConnectAsync(smtpServer, port, SecureSocketOptions.StartTls);
                   await smtp.AuthenticateAsync(fromMail, password);
                   await smtp.SendAsync(email);
                   await smtp.DisconnectAsync(true);

                   return "Email sent successfully!";
               }
               catch (Exception ex)
               {
                   // Log the exception (not done here for simplicity)
                   return $"Email sending failed. Error: {ex.Message}";
               }
           }
       }
   }

Brief Explanation of the Code:

1. Fetching Configuration Values

   var smtpServer = _configuration["EmailSettings:SmtpServer"];
   var port = int.Parse(_configuration["EmailSettings:Port"]);
   var fromMail = _configuration["EmailSettings:Username"];
   var password = _configuration["EmailSettings:Password"];

Retrieves SMTP server details (server address, port, username, and password) from appsettings.json.

2. Creating the Email Message

   var email = new MimeMessage();
   email.From.Add(new MailboxAddress("Your Name", fromMail));
   email.To.Add(new MailboxAddress("To Name", toEmail));
   email.Subject = mailSubject;
   email.Body = new TextPart("html") { Text = mailBody };

MimeMessage is used to create an email.
From & To Addresses: Sets the sender (fromMail) and recipient (toEmail).
Subject & Body: Sets the subject and body in HTML format.

3. Sending the Email

   using var smtp = new SmtpClient();
   await smtp.ConnectAsync(smtpServer, port, SecureSocketOptions.StartTls);
   await smtp.AuthenticateAsync(fromMail, password);
   await smtp.SendAsync(email);
   await smtp.DisconnectAsync(true);

Creates an SMTP client (SmtpClient).
Connects to SMTP server using the given server address and port.
Enables authentication with Gmail credentials.
Sends the email.
Disconnects cleanly after sending.

Step 4: Register the Service in `Program.cs`

Now, register the EmailService in the Program.cs file so that it can be injected into the controller.

In your Program.cs (or Startup.cs for older versions), add the following:

builder.Services.AddScoped<IEmailService, EmailService>();

This registers the EmailService to be available for dependency injection in your controllers.

Step 5: Create the Email Controller

Next, let's create an API controller that will handle HTTP requests for sending emails.

In the Controllers folder, create a new file called EmailController.cs:

using EmailApp.IRepository;
using Microsoft.AspNetCore.Mvc;

namespace EmailApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmailController : ControllerBase
    {
        private readonly IEmailService _emailService;

        public EmailController(IEmailService emailService)
        {
            _emailService = emailService;
        }

        [HttpPost]
        [Route("SendEmail")]
        public async Task<IActionResult> SendEmail(string toEmail, string mailSubject, string mailBody)
        {
            var result = await _emailService.SendEmail(toEmail, mailSubject, mailBody);
            return Ok(new { message = result });
        }
    }
}

This controller exposes a POST endpoint api/Email/SendEmail that will accept parameters like toEmail, mailSubject, and mailBody, and send the email accordingly using the EmailService.

Step 6: Test the API

You can now test the API by sending a POST request using Postman or Swagger (if configured).

Example Request:

Endpoint: POST api/Email/SendEmail
Body (JSON):

  {
    "toEmail": "recipient-email@gmail.com",
    "mailSubject": "Test Email",
    "mailBody": "This is a test email sent from the .NET Web API using MailKit."
  }

If everything is set up correctly, you should receive a response like:

{
  "message": "Email sent successfully!"
}

Conclusion:

In this blog, you learned how to send emails using Gmail's SMTP server in a .NET Web API application with the MailKit library. You also saw how to structure the solution with the Repository Pattern, and how to manage email configuration settings securely in appsettings.json.

By following these steps, you can easily integrate email functionality into your .NET Core applications and send emails via Gmail (or any other SMTP service) programmatically.

Next Steps:

Enhance the email service with additional functionality such as attachments or HTML content formatting.
Build a UI to trigger email sending from a front-end application.

Happy coding!
Find me on LinkedIn

Dependency Inversion Principle (DIP) & Dependency Injection (DI) in C# (Easy Examples)

Abdullah Al Mamun Akand — Fri, 14 Feb 2025 19:01:02 +0000

🔹Understanding Dependency Inversion Principle

What is DIP?

DIP is a principle in SOLID design that says:

High-level code (business logic) should not depend on low-level code (implementation details).
Both should depend on an abstraction (interface or abstract class).

Why is DIP Important?

✅ Makes the code flexible – you can swap implementations easily.

✅ Reduces dependencies – high-level modules don’t care about the details.

✅ Makes the code easier to test – we can use mock implementations.

🔹 Real-Life Example (Pizza Ordering System)

Imagine a restaurant with a Waiter and a Chef.

Bad Design (Without DIP):
- The Waiter (business logic) goes to a specific chef (implementation) to make a pizza.
- If the chef changes, the waiter also needs to change! ❌
Good Design (With DIP):
- The Waiter doesn’t care who the chef is.
- Instead, the Waiter follows an interface (IChef) to request the pizza.
- Now, any chef (ItalianChef, RobotChef) can cook without changing the waiter! ✅

🔹 Without DIP (Bad Design - Tightly Coupled Code)

// Low-Level Module (Concrete Implementation)
public class ItalianChef
{
    public void MakePizza()
    {
        Console.WriteLine("Italian Chef is making a pizza!");
    }
}

// High-Level Module (Directly Dependent on Low-Level)
public class Waiter
{
    private ItalianChef _chef; // Direct dependency ❌

    public Waiter()
    {
        _chef = new ItalianChef(); // Hardcoded dependency ❌
    }

    public void TakeOrder()
    {
        _chef.MakePizza(); // Tightly coupled ❌
    }
}

❌ Problems:

The Waiter is directly tied to ItalianChef.
If we want a RobotChef, we must change the Waiter class.
Hard to maintain, test, and extend.

🔹 With DIP (Good Design - Loosely Coupled Code)

✔ Step 1: Create an Interface (Abstraction)

public interface IChef
{
    void MakePizza();
}

✔ Step 2: Implement Different Chef Variants

public class ItalianChef : IChef
{
    public void MakePizza()
    {
        Console.WriteLine("Italian Chef is making a pizza!");
    }
}

public class RobotChef : IChef
{
    public void MakePizza()
    {
        Console.WriteLine("Robot Chef is making a pizza with automation!");
    }
}

✔ Step 3: Modify Waiter to Depend on an Interface

public class Waiter
{
    private IChef _chef; // Uses abstraction ✅

    public Waiter(IChef chef) // Inject dependency
    {
        _chef = chef;
    }

    public void TakeOrder()
    {
        _chef.MakePizza(); // Works with any chef ✅
    }
}

✔ Step 4: Use Dependency Injection

class Program
{
    static void Main()
    {
        IChef chef = new ItalianChef(); // Or new RobotChef();
        Waiter waiter = new Waiter(chef);
        waiter.TakeOrder(); // Output: Italian Chef is making a pizza!
    }
}

✅ Now, the Waiter can work with ANY Chef (ItalianChef, RobotChef, etc.) without changing the Waiter class!

🔹 Understanding Dependency Injection (DI)

What is DI?

Dependency Injection is a technique where dependencies (like IChef) are provided from the outside instead of being created inside the class.

DI Techniques in C#

1️⃣ Constructor Injection (Best Practice)

   public class Waiter
   {
       private IChef _chef;

       public Waiter(IChef chef) // Injected via constructor
       {
           _chef = chef;
       }
   }

2️⃣ Property Injection

   public class Waiter
   {
       public IChef Chef { get; set; } // Injected via property
   }

3️⃣ Method Injection

   public class Waiter
   {
       public void TakeOrder(IChef chef) // Injected via method
       {
           chef.MakePizza();
       }
   }

🔹 Combining DIP & DI for a Powerful System

1️⃣ DIP makes code loosely coupled ✅

2️⃣ DI injects dependencies dynamically ✅

3️⃣ Together, they make the code flexible & testable! ✅

Example with a DI Container (ASP.NET Core)

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddScoped<IChef, ItalianChef>(); // Inject ItalianChef
builder.Services.AddScoped<Waiter>();

var app = builder.Build();

Now, the framework automatically injects ItalianChef into Waiter!

Key Takeaways

Concept	Explanation
DIP (Dependency Inversion Principle)	High-level modules should not depend on low-level modules; both should depend on abstractions.
DI (Dependency Injection)	A technique to provide dependencies from the outside instead of creating them inside the class.
Interface Role	Interfaces act as the middle layer, allowing flexibility.
Best Practice	Use DIP + DI together for loosely coupled, maintainable, and testable code.

Outcome

"DIP & DI = More Flexibility + Less Maintenance!"

If you follow these principles, your code will be scalable, reusable, and easy to modify!