📢 Notifications in .NET Core with Web API, Azure Service Bus, and SQL Server

 🧠 What Are Notifications?

Notifications are messages or alerts sent to users or systems to inform them about an event or change in data.
They can be real-time (instant) or scheduled (delayed).

Example scenarios:

• A user receives an email when a new order is confirmed.

• A manager gets a push notification when a report is ready.

• A background service logs a message when inventory is updated.

Notifications enhance user experience, system monitoring, and automation.

---

⚙️ How Notifications Work in a .NET Core + Web API + Azure Service Bus + SQL Server Architecture

Let’s understand how notifications flow in this architecture:

🏗️ 1. SQL Server (Data Source)

• Acts as the primary data store (e.g., Orders, Users, Payments).

• When data changes (insert/update/delete), it triggers an event (through an API or message broker).

🌐 2. .NET Core Web API

• Acts as the middle layer that detects or triggers notifications.

• Example: When an order is created via POST /api/orders, the Web API:

  1. Stores order details in SQL Server.

  2. Publishes a notification message to Azure Service Bus.

☁️ 3. Azure Service Bus (Message Broker)

• Acts as a reliable message queue between services.

• Ensures asynchronous communication and message durability.

• Web API sends the notification event → another service (like Notification Worker) subscribes and processes it.

Example Message:

{
  "EventType": "OrderCreated",
  "UserId": 101,
  "Message": "Your order #1234 has been placed successfully.",
  "Channel": "Email"
}

⚙️ 4. Notification Worker Service (in .NET Core)

• A background worker or Azure Function subscribes to the Service Bus.

• It processes messages and sends notifications via Email, SMS, or Push.

• Uses libraries like:

  • System.Net.Mail or SendGrid for Emails.

  • Twilio or Azure Communication Services for SMS.

  • Firebase Cloud Messaging (FCM) or SignalR for Push Notifications.

---

🔁 End-to-End Notification Flow

[Client] → [Web API] → [SQL Server]
              ↓
        [Azure Service Bus Queue]
              ↓
     [Notification Worker Service]
              ↓
   [Email / SMS / Push Notification Sent]

---

💬 Example Implementation

✅ Step 1: Send Message to Azure Service Bus

using Azure.Messaging.ServiceBus;

public class NotificationService
{
    private readonly string _connectionString = "<Your Service Bus Connection>";
    private readonly string _queueName = "notifications";

    public async Task SendNotificationAsync(string message)
    {
        await using var client = new ServiceBusClient(_connectionString);
        ServiceBusSender sender = client.CreateSender(_queueName);

        await sender.SendMessageAsync(new ServiceBusMessage(message));
        Console.WriteLine("Notification message sent to Azure Service Bus!");
    }
}

---

✅ Step 2: Receive and Process Message (Background Worker)

using Azure.Messaging.ServiceBus;

public class NotificationProcessor
{
    private readonly string _connectionString = "<Your Service Bus Connection>";
    private readonly string _queueName = "notifications";

    public async Task StartProcessingAsync()
    {
        var client = new ServiceBusClient(_connectionString);
        var processor = client.CreateProcessor(_queueName);

        processor.ProcessMessageAsync += async args =>
        {
            string body = args.Message.Body.ToString();
            Console.WriteLine($"Received notification: {body}");

            // Here send Email or SMS
            await SendEmailAsync(body);

            await args.CompleteMessageAsync(args.Message);
        };

        processor.ProcessErrorAsync += args =>
        {
            Console.WriteLine(args.Exception.ToString());
            return Task.CompletedTask;
        };

        await processor.StartProcessingAsync();
    }

    private async Task SendEmailAsync(string message)
    {
        Console.WriteLine($"Sending Email: {message}");
        await Task.Delay(500); // simulate send delay
    }
}

---

🧩 Different Types of Notifications in .NET / .NET Core

Type	Description	Example
Email Notification	Sent through SMTP or APIs like SendGrid.	"Welcome to our platform!"
SMS Notification	Sent via Twilio, Azure Communication Services.	"Your OTP is 456123."
Push Notification	Sent to devices or browsers (via Firebase or SignalR).	"You have a new message."
In-App Notification	Shown inside the web or mobile app (via SignalR/WebSocket).	“Task completed successfully.”
System Notification	Logged for audit or monitoring.	Error logs, job success logs, etc.
Webhook Notification	Notifies external systems (via HTTP POST).	GitHub → Jenkins build trigger

---

🔒 Advantages of Using Azure Service Bus for Notifications

✅ Reliable message delivery
✅ Handles large volumes easily
✅ Asynchronous — doesn’t block main thread
✅ Retry and dead-letter queue support
✅ Scalable and secure

---

💡 Best Practices

• Use Queue per notification type (e.g., EmailQueue, SMSQueue).

• Implement DLQ (Dead Letter Queue) for failed messages.

• Add logging and monitoring via Application Insights.

• Use Azure Managed Identity for secure connection.

• Keep notification templates configurable in SQL Server or Blob Storage.

---

🏁 Conclusion

Notifications in .NET Core + Web API + Azure Service Bus + SQL Server architecture provide a robust, scalable way to inform users and systems of real-time events.
Using Service Bus as a message broker ensures reliability and performance, while background workers handle the actual sending of emails, SMS, and push messages asynchronously.

⚙️ SQL vs T-SQL vs PostgreSQL — Practical Code Comparison & EF Core Integration

 

🧩 1. Side-by-Side Query Comparison

Operation	SQL (Standard)	T-SQL (SQL Server)	PostgreSQL
Select Data	sql SELECT * FROM Employees;	sql SELECT * FROM Employees;	sql SELECT * FROM Employees;
Insert Record	sql INSERT INTO Employees (Name, Dept) VALUES ('Ravi', 'IT');	sql INSERT INTO Employees (Name, Dept) VALUES ('Ravi', 'IT');	sql INSERT INTO Employees (Name, Dept) VALUES ('Ravi', 'IT');
Declare Variable	❌ Not supported	sql DECLARE @Dept NVARCHAR(20) = 'HR'; SELECT * FROM Employees WHERE Dept = @Dept;	sql DO $$ DECLARE dept TEXT := 'HR'; BEGIN SELECT * FROM Employees WHERE Dept = dept; END $$;
String Concatenation	```sql SELECT FirstName		' '
Current Date	sql SELECT CURRENT_DATE;	sql SELECT GETDATE();	sql SELECT NOW();
Top / Limit	sql SELECT * FROM Employees FETCH FIRST 5 ROWS ONLY;	sql SELECT TOP 5 * FROM Employees;	sql SELECT * FROM Employees LIMIT 5;
Stored Procedure	Limited / Varies by DB	sql CREATE PROCEDURE GetEmployees AS BEGIN SELECT * FROM Employees; END;	sql CREATE FUNCTION GetEmployees() RETURNS TABLE(...) AS $$ SELECT * FROM Employees; $$ LANGUAGE sql;

✅ Takeaway:

• T-SQL adds variables, flow control, and procedural logic.

• PostgreSQL has its own procedural language PL/pgSQL with advanced functions and JSON operations.

• Standard SQL works across all, but lacks procedural capabilities.

---

🛠 2. Entity Framework Core Setup in .NET Core Web API

Now let’s see how to connect .NET Core Web API with each database using Entity Framework Core (EF Core).

---

🔹 A. SQL Server (T-SQL)

🧩 Install NuGet Package

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

⚙️ appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=EmployeeDB;User Id=sa;Password=Your@123;"
  }
}

⚙️ Program.cs

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

🧱 Example Entity

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
}

✅ SQL Server uses T-SQL syntax internally for all queries EF Core generates.

---

🔹 B. PostgreSQL

🧩 Install NuGet Package

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

⚙️ appsettings.json

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Database=EmployeeDB;Username=postgres;Password=admin123"
  }
}

⚙️ Program.cs

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("PostgresConnection")));

✅ PostgreSQL queries generated by EF Core use PostgreSQL syntax, including features like LIMIT, RETURNING, and ILIKE.

---

🔹 C. Generic SQL (Database Independent Example)

If you’re writing SQL logic that can work on both SQL Server and PostgreSQL, use standard SQL commands in EF Core’s LINQ or raw SQL:

var employees = await _context.Employees
    .Where(e => e.Department == "Finance")
    .OrderByDescending(e => e.Id)
    .Take(5)
    .ToListAsync();

✅ EF Core translates this query automatically to T-SQL (if SQL Server) or PostgreSQL syntax — making your code database-independent.

---

⚡ 3. Real-Time Example: Hybrid Database Microservice

In real-world microservices:

• OrderService (SQL Server / T-SQL) → Handles transactional operations

• AnalyticsService (PostgreSQL) → Stores analytical data, JSON reports, or logs

Example Architecture:

Angular UI
   ↓
API Gateway (Ocelot)
   ↓
OrderService (.NET Core → EF Core → SQL Server)
   ↓
AnalyticsService (.NET Core → EF Core → PostgreSQL)

This architecture ensures high scalability and polyglot persistence, allowing each microservice to choose the most efficient database engine.

---

🧠 Interview Q&A (EF + DB Context)

Q1. How does EF Core handle different databases?
👉 EF Core uses different providers (SqlServer, Npgsql, MySql, etc.) that translate LINQ queries into DB-specific SQL.

Q2. Can I switch from SQL Server to PostgreSQL easily?
👉 Yes, if you write queries using LINQ and avoid DB-specific T-SQL code.

Q3. What’s the main difference between EF Core SQL Server and EF Core PostgreSQL providers?
👉 SQL Server uses TOP, GETDATE(), IDENTITY; PostgreSQL uses LIMIT, NOW(), and SERIAL.

Q4. Is PostgreSQL good for microservices?
👉 Absolutely. It supports JSON, schema evolution, and strong concurrency — ideal for microservices and cloud-native apps.

---

🏁 Conclusion

Use Case	Database	Query Type	EF Core Provider
Enterprise apps on Azure	SQL Server	T-SQL	Microsoft.EntityFrameworkCore.SqlServer
Open-source, cloud-based systems	PostgreSQL	SQL / PLpgSQL	Npgsql.EntityFrameworkCore.PostgreSQL
Database-agnostic logic	Any RDBMS	Standard SQL	Generic EF Core LINQ

SQL = The language
T-SQL = Microsoft’s dialect
PostgreSQL = The database engine

Mastering all three gives you the flexibility to design robust, cloud-ready microservices that run anywhere — from Azure to AWS to on-premises.