How to integrate .NET Aspire with Microsoft SQL Server (MSSQL)

In this article, we will explore how to integrate Microsoft SQL Server (MSSQL) with .NET Aspire, a powerful framework designed for building modern, cloud-native applications.

As you may already know, .NET Aspire simplifies the development of distributed applications by providing a set of tools and libraries that streamline the process of creating, deploying, and managing cloud-native solutions. By integrating MSSQL with .NET Aspire, you can leverage the robust capabilities of SQL Server to manage your application’s data layer efficiently, ensuring scalability, reliability, and performance.

In this article, we cover:

Create a .Net Aspire Starter App
Solution Explorer
Adding MSSQL to App Host Project
View after Adding Aspire.Hosting.SqlServer NuGet packages to the project.
Adding WithDataVolume
Adding WithDataBindMount
Adding With WithLifetime
Client Integration
Installing Packages in API Service Project.
Adding Swashbuckle.AspNetCore.SwaggerGen NuGet package.
Adding Swashbuckle.AspNetCore.SwaggerUI NuGet package.
After adding the All NuGet package to the Project.
Integrating the model into the API service project.
Introducing the Product model
Introducing the ProductRequest model to handle input requests.
Creating Database Context Class
Code snippet After Registering the MSSQL database context
Adding API Controller
Added Empty API Controller
Code Snippet of ProductController
Code Snippet of AspireApp7.ApiService Program.cs
Dashboard
Docker
Connecting and Creating Database Using Microsoft SQL Server Management Studio
Connecting Microsoft SQL Server Management Studio
Creating Database
Creating a table using SQL Script.
Call API Service for Inserting data
Inserting data by calling API
Call API Service to Insert data
View Inserted Data into SQL Server

What is MSSQL?

Microsoft SQL Server (MSSQL) is a relational database management system (RDBMS) developed by Microsoft. It is designed to store and retrieve data as requested by other software applications, whether those applications run on the same computer or another computer across a network. MSSQL uses a structured query language (SQL) for database access and manipulation. It provides various features such as data warehousing, data mining, and analytics, making it suitable for various data-driven applications.

Create a .Net Aspire Starter App

After selecting the project, click the “Next” button to proceed.

A new dialogue will appear, prompting you to configure your new project. Within this dialogue, you will be asked to provide a Solution Name and specify the location where the project should be saved.

After entering details, click the “Next” button to proceed.

A new dialogue will appear, prompting you to configure additional information related to the project, such as the framework, the .NET Aspire version, and the option to create a test project. For this demonstration, I will select .NET Aspire 9.0.

Additional information while creating project.

Solution Explorer

We have created a solution, so let’s begin by adding Microsoft SQL Server (MSSQL)to the project.

In this demo, we are going to configure the Microsoft SQL Server (MSSQL) database.

Adding MSSQL to App Host Project

In the App Host Project, we are going to Install the package Aspire.Hosting.SqlServer.

Install the package Aspire.Hosting.SqlServer. NuGet package

If you want to add from .NET CLI.

dotnet add package Aspire.Hosting.SqlServer

View after Adding Aspire.Hosting.SqlServer NuGet packages to the project.

After installation, we need to add resources to the Program.cs class of AppHost project.

Configuring Container Resource.

Adding WithDataVolume

This method is used to specify the lifetime of the container resource. The lifetime determines how long the container should exist and when it should be started or stopped.

The data volume stores SQL Server data separately from the container, so the data remains even if the container is removed. It is mounted at /var/opt/mssql in the SQL Server container. If no name is provided, a random name is generated. For more on data volumes and why they are preferred over bind mounts, see the Docker documentation.

var builder = DistributedApplication.CreateBuilder(args);

var password = builder.AddParameter("password", secret: true);

var sql = builder.AddSqlServer("mssql", password)
    .WithDataVolume();

var db = sql.AddDatabase("aspiredatabase");

var cache = builder.AddRedis("cache");

var apiService = builder.AddProject<Projects.AspireApp7_ApiService>("apiservice")
    .WithReference(db)
    .WaitFor(db);

builder.AddProject<Projects.AspireApp7_Web>("webfrontend")
    .WithExternalHttpEndpoints()
    .WithReference(cache)
    .WaitFor(cache)
    .WithReference(apiService)
    .WaitFor(apiService);

builder.Build().Run();

Adding WithDataBindMount

Adds a bind mount for the data folder to an MSSQL container resource and enables MSSQL persistence.

Data bind mounts rely on the host machine’s filesystem to persist the MSSQL server data across container restarts. The data-bind mount is mounted at the C:\SqlServer \Data on Windows (or /SqlServer/Data on Unix) path on the host machine in the MSSQL server container.

var builder = DistributedApplication.CreateBuilder(args);

var password = builder.AddParameter("password", secret: true);

var sql = builder.AddSqlServer("mssql", password)
    .WithDataBindMount(source: @"C:\SqlServer\Data");

var db = sql.AddDatabase("aspiredatabase");

var cache = builder.AddRedis("cache");

var apiService = builder.AddProject<Projects.AspireApp7_ApiService>("apiservice")
    .WithReference(db)
    .WaitFor(db);

builder.AddProject<Projects.AspireApp7_Web>("webfrontend")
    .WithExternalHttpEndpoints()
    .WithReference(cache)
    .WaitFor(cache)
    .WithReference(apiService)
    .WaitFor(apiService);

builder.Build().Run();

Adding With WithLifetime

By default, container resources exist only while the app is running. Each time the app starts, a new container is created, and when the app stops, the container is removed. To prevent unnecessary restarts and keep the container state, resources can be set to persist.

var builder = DistributedApplication.CreateBuilder(args);

var password = builder.AddParameter("password", secret: true);

var sql = builder.AddSqlServer("mssql", password)
    .WithLifetime(ContainerLifetime.Persistent);

var db = sql.AddDatabase("aspiredatabase");

var cache = builder.AddRedis("cache");

var apiService = builder.AddProject<Projects.AspireApp7_ApiService>("apiservice")
    .WithReference(db)
    .WaitFor(db);

builder.AddProject<Projects.AspireApp7_Web>("webfrontend")
    .WithExternalHttpEndpoints()
    .WithReference(cache)
    .WaitFor(cache)
    .WithReference(apiService)
    .WaitFor(apiService);

builder.Build().Run();

After Completing with App Host project Configuration let’s move to API Service Project.

Client Integration

Client integration means the project which is going to consume an SQL server using Entity Framework Core.

In this Demo, API Services will be the client because we are going to consume an SQL server using Entity Framework Core and perform operations in it.

Installing Packages in API Service Project.

Adding Aspire.Microsoft.EntityFrameworkCore.SqlServer NuGet package in API Service Project. We are going to use this package for performing crud operations using the entity framework core connecting the MSSQL database.

installing Aspire.Microsoft.EntityFrameworkCore.SqlServer NuGet package

Next, we will integrate Swagger into this project to enhance our API documentation.

Adding Swashbuckle.AspNetCore.SwaggerGen NuGet package.

Adding Swashbuckle.AspNetCore.SwaggerUI NuGet package.

After adding the All NuGet package to the Project.

Integrating the model into the API service project.

Introducing the Product model

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace AspireApp7.ApiService.Models;

[Table("Product")]
public class Product
{
    // Add properties here
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
}

Introducing the ProductRequest model to handle input requests.

namespace AspireApp7.ApiService.Models;

public class ProductRequest
{
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
}

Creating Database Context Class

We are going to use Entity Framework core for inserting data into MSSQL Database. For using entity framework core, we need to create a class which should inherit the Database context class.

Adding Class with name DataDbContext which is inheriting from DbContext class.

using AspireApp7.ApiService.Models;
using Microsoft.EntityFrameworkCore;
namespace AspireApp7.ApiService.Data;

public class DataDbContext : DbContext
{
    // Define your constructor here
    /// <summary>
    /// 
    /// </summary>
    /// <param name="options"></param>
    public DataDbContext(DbContextOptions<DataDbContext> options) : base(options) { }

    // Define your DbSets (tables) here
    public DbSet<Product> Products { get; set; }
}

After adding the DataDbContext class next we are going to Register it in Program.cs class as shown below. Where UserDatabase is a connection

Code snippet After Registering the MSSQL database context

using AspireApp7.ApiService.Data;

var builder = WebApplication.CreateBuilder(args);

// Add service defaults & Aspire client integrations.
builder.AddServiceDefaults();

// Add services to the container.
builder.Services.AddProblemDetails();

// Microsoft SQL Server Connection
builder.AddSqlServerDbContext<DataDbContext>("aspiredatabase");

var app = builder.Build();

// Configure the HTTP request pipeline.
app.UseExceptionHandler();

app.MapDefaultEndpoints();

app.Run();

After Registering AddSqlServerDbContext next we are going to Add API Controller.

Adding API Controller

Added Empty API Controller

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace AspireApp7.ApiService
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
    }
}

After adding the API controller, the next step is to implement methods for inserting data into the MSSQL Database and retrieving values from it.

To achieve this, we will use constructor injection to obtain an instance of the DataDbContext Class.

Code Snippet of ProductController

using AspireApp7.ApiService.Data;
using AspireApp7.ApiService.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace AspireApp7.ApiService
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        private readonly DataDbContext _context;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public ProductController(DataDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<IActionResult> GetProducts()
        {
            var listodEmployees = await _context.Products.ToListAsync();
            return Ok(listodEmployees);
        }

        [HttpPost]
        public async Task<bool> AddProduct([FromBody] ProductRequest productrequest)
        {
            try
            {
                var products = new Product
                {
                    Name = productrequest.Name,
                    Price = productrequest.Price,
                    Description = productrequest.Description
                };

                await _context.Products.AddAsync(products);
                await _context.SaveChangesAsync();
            }
            catch (Exception e)
            {
                throw;
            }
            return true;
        }
    }
}

After Adding the Controller we are going to register the Service in the Program.cs class.

Code Snippet of AspireApp7.ApiService Program.cs

using AspireApp7.ApiService.Data;

var builder = WebApplication.CreateBuilder(args);

// Add service defaults & Aspire client integrations.
builder.AddServiceDefaults();

// Add API Documentation
builder.Services.AddSwaggerGen();

// Add controllers services
builder.Services.AddControllers();

// Add services to the container.
builder.Services.AddProblemDetails();

// Microsoft SQL Server Connection
builder.AddSqlServerDbContext<DataDbContext>("aspiredatabase");

var app = builder.Build();

// MapControllers is used to map the controllers to the application.
app.MapControllers();

// Register the Swagger generator and the Swagger UI middlewares
app.UseSwagger();

// Enable middleware to serve generated Swagger as a JSON endpoint.
app.UseSwaggerUI();

// Configure the HTTP request pipeline.
app.UseExceptionHandler();

app.MapDefaultEndpoints();

app.Run();

After completing the integration with the API Service, we can now run the application for the first time.

This is the Dashboard for Aspire project where you will see all applications which are running.

Dashboard

Docker

The application runs in the docker container.

Connecting and Creating Database Using Microsoft SQL Server Management Studio

First, to connect to Management Studio we need Login and Password. We can get that from the Dashboard.

We need to click on Apiservice Resource from Dashboard and then go to the Environment variables tab inside it check the ConnectionStrings__aspiredatabase variable and in value, you will find the connection string.

There are 2 ways you can Create a Database

Manually
Using migration which will be called on application startup

Connecting Microsoft SQL Server Management Studio

Creating Database

Creating a table using SQL Script.

Call API Service for Inserting data

As we have configured swagger in Api service, we can see the generated document from UI calling Apiservice localhost URL and then appending it with the swagger keyword will land you to the swagger page.

The URL to access is your localhost application. With an appending swagger keyword at the end https://localhost:7360/swagger

Inserting data by calling API

Call API Service to Insert data

View Inserted Data into SQL Server

Conclusion

I hope you have liked the article. In this article, you have learned how to create an Aspire project and configure the Microsoft SQL Server database for Inserting and reading data from it.

Referenced From: Microsoft Learn – ASP.NET Core Inspire Series

GitHub Link :- https://github.com/saineshwar/Aspire_MSSQL

Aspire with MongoDB :- https://tutexchange.com/how-to-integrate-net-aspire-with-mongodb/

BySaineshwar