Merging CSHTML Email Templates with Data from Stored Procedures in .NET 8 Minimal API
Introduction
Creating dynamic email content often involves merging HTML templates with data from a database. In this blog post, we’ll explore how to accomplish this using a CSHTML email template, data fetched via stored procedures, and the .NET 8 Minimal API. We’ll provide step-by-step instructions and code snippets to demonstrate the process.
Prerequisites
Before we start, make sure you have:
- Visual Studio or any C# IDE
- Basic understanding of ASP.NET Core Razor views
- SQL Server with a sample database and stored procedure
Step-by-Step Guide
1. Setting Up the Razor Template
First, create a Razor template for your email. This template will be a .cshtml file.
<!-- EmailTemplate.cshtml -->
@model dynamic
<!DOCTYPE html>
<html>
<head>
<title>Email</title>
</head>
<body>
<h1>Hello @Model.Name,</h1>
<p>Here is your detailed data from our database:</p>
<table border="1">
<thead>
<tr>
<th>Title</th>
<th>Description</th>
<th>Date</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.Details)
{
<tr>
<td>@item.Title</td>
<td>@item.Description</td>
<td>@item.Date</td>
</tr>
}
</tbody>
</table>
</body>
</html>
2. Creating the Stored Procedure
Assume you have a SQL Server database. Create a stored procedure to fetch the data you need.
CREATE PROCEDURE GetEmailDetails
AS
BEGIN
SELECT 'John Doe' AS Name, 'Report 1' AS Title, 'Detail description 1' AS Description, '2023-06-15' AS Date
UNION
SELECT 'John Doe' AS Name, 'Report 2' AS Title, 'Detail description 2' AS Description, '2023-06-16' AS Date
UNION
SELECT 'John Doe' AS Name, 'Report 3' AS Title, 'Detail description 3' AS Description, '2023-06-17' AS Date
END
3. Creating the DTO and Service
Move the data fetching logic and the DTO into separate files.
public class EmailDetail
{
public string Title { get; set; }
public string Description { get; set; }
public string Date { get; set; }
}
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
public class DataService
{
private readonly string _connectionString;
public DataService(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public List<EmailDetail> GetEmailDetails(out string name)
{
var data = new List<EmailDetail>();
name = string.Empty;
using (var connection = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand("GetEmailDetails", connection))
{
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
name = reader["Name"].ToString();
data.Add(new EmailDetail
{
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
Date = reader["Date"].ToString()
});
}
}
}
}
return data;
}
}
4. Creating the Email Template Service
Create a service to render the Razor template.
using Microsoft.AspNetCore.Mvc.Razor;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.IO;
using System.Threading.Tasks;
public class EmailTemplateService
{
private readonly IRazorViewEngine _razorViewEngine;
private readonly IServiceProvider _serviceProvider;
public EmailTemplateService(IRazorViewEngine razorViewEngine, IServiceProvider serviceProvider)
{
_razorViewEngine = razorViewEngine;
_serviceProvider = serviceProvider;
}
public async Task<string> RenderTemplateAsync(string templateName, object model)
{
var viewEngineResult = _razorViewEngine.GetView(null, templateName, false);
if (!viewEngineResult.Success)
{
throw new InvalidOperationException($"Unable to find view '{templateName}'");
}
var view = viewEngineResult.View;
using (var sw = new StringWriter())
{
var viewContext = new ViewContext()
{
ViewData = new Microsoft.AspNetCore.Mvc.ViewFeatures.ViewDataDictionary<object>(new Microsoft.AspNetCore.Mvc.ModelBinding.EmptyModelMetadataProvider(), new Microsoft.AspNetCore.Mvc.ModelBinding.ModelStateDictionary()),
Writer = sw
};
var viewData = viewEngineResult.ViewData;
viewData.Model = model;
await view.RenderAsync(viewContext);
return sw.ToString();
}
}
}
5. Setting Up the Minimal API
Finally, set up the .NET 8 Minimal API in Program.cs.
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
var builder = WebApplication.CreateBuilder(args);
// Add services
builder.Services.AddSingleton<IConfiguration>(builder.Configuration);
builder.Services.AddRazorPages();
builder.Services.AddTransient<DataService>();
builder.Services.AddTransient<EmailTemplateService>();
var app = builder.Build();
// Configure endpoints
app.MapGet("/", async (DataService dataService, EmailTemplateService emailTemplateService) =>
{
var emailData = dataService.GetEmailDetails(out var name);
var model = new { Name = name, Details = emailData };
var emailContent = await emailTemplateService.RenderTemplateAsync("EmailTemplate.cshtml", model);
return Results.Text(emailContent, "text/html");
});
// Run the app
app.Run();
Explanation
- Razor Template (
EmailTemplate.cshtml): A Razor template that displays the user’s name and a table of details fetched from the database. - Stored Procedure: The
GetEmailDetailsstored procedure returns a complex dataset with multiple fields: Name, Title, Description, and Date. - DTO (
EmailDetail.cs): A simple class representing the structure of the email details. - Data Service (
DataService.cs): Contains the logic to fetch data from the stored procedure and map it to a list ofEmailDetailobjects. - Email Template Service (
EmailTemplateService.cs): Uses the built-in Razor engine to compile and render the Razor template with the provided model. - Minimal API Setup (
Program.cs):- Configuration: Reads the connection string from
appsettings.json. - Dependency Injection: Registers
DataServiceandEmailTemplateServicewith the dependency injection container. - Endpoint: The root endpoint (
"/") fetches data via theDataServiceand renders the Razor template using theEmailTemplateService.
- Configuration: Reads the connection string from
Conclusion
In this blog post, we demonstrated how to merge a complex CSHTML email template with data returned by stored procedures in a .NET 8 Minimal API. By organizing the logic into services and using dependency injection, we created a clean and maintainable solution for generating dynamic email content. Happy coding!