.NET 7.0 + Dapper - Connect to MS SQL Server Database in ASP.NET Core
Tutorial built with .NET 7.0, Dapper 2.0 and MS SQL Server
This is a quick post to show how to connect a .NET 7 API to a SQL Server database using Dapper and ADO.NET.
The example code is from of a .NET 7 CRUD API tutorial I posted recently, the full project and documentation is available at .NET 7.0 + Dapper + MS SQL Server - CRUD API Tutorial in ASP.NET Core.
Dapper ORM (Object Relational Mapper)
Dapper is a micro ORM that supports executing raw SQL queries and mapping results to C# objects and collections, it's implemented as a set of extension methods on top of the ADO.NET IDbConnection interface, so ADO.NET provides the connection to the database when using Dapper.
MS SQL Server Database
SQL Server is a full featured relational database built by Microsoft, it's a good option for any size application in production or development. The ADO.NET provider for SQL Server (Microsoft.Data.SqlClient) is used to connect to the database with Dapper.
Connect to SQL Server with Dapper and ADO.NET
Below is the data context class from the example .NET 7 CRUD API, the CreateConnection() method connects to the SQL Server database by creating a new instance of the ADO.NET SqlConnection() class with the specified connection string.
The Init() method creates the SQL Server database and tables if they don't exist, it is executed once on API startup. The Dapper extension method ExecuteAsync() is used to execute SQL queries.
namespace WebApi.Helpers;
using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
public class DataContext
{
private DbSettings _dbSettings;
public DataContext(IOptions<DbSettings> dbSettings)
{
_dbSettings = dbSettings.Value;
}
public IDbConnection CreateConnection()
{
var connectionString = $"Server={_dbSettings.Server}; Database={_dbSettings.Database}; User Id={_dbSettings.UserId}; Password={_dbSettings.Password};";
return new SqlConnection(connectionString);
}
public async Task Init()
{
await _initDatabase();
await _initTables();
}
private async Task _initDatabase()
{
// create database if it doesn't exist
var connectionString = $"Server={_dbSettings.Server}; Database=master; User Id={_dbSettings.UserId}; Password={_dbSettings.Password};";
using var connection = new SqlConnection(connectionString);
var sql = $"IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{_dbSettings.Database}') CREATE DATABASE [{_dbSettings.Database}];";
await connection.ExecuteAsync(sql);
}
private async Task _initTables()
{
// create tables if they don't exist
using var connection = CreateConnection();
await _initUsers();
async Task _initUsers()
{
var sql = """
IF OBJECT_ID('Users', 'U') IS NULL
CREATE TABLE Users (
Id INT NOT NULL PRIMARY KEY IDENTITY,
Title NVARCHAR(MAX),
FirstName NVARCHAR(MAX),
LastName NVARCHAR(MAX),
Email NVARCHAR(MAX),
Role INT,
PasswordHash NVARCHAR(MAX)
);
""";
await connection.ExecuteAsync(sql);
}
}
}
SQL SSL connection error
If you get the following SSL error on startup it's because SQL Server is using a self-signed SSL certificate to encrypt the connection to the database. To fix the error configure ASP.NET Core to trust the certificate by appending the database connection string with TrustServerCertificate=true. For more info see SqlClient.SqlException - The certificate chain was issued by an authority that is not trusted.
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904):
A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
Need Some .NET Help?
Search fiverr for freelance .NET developers.
Follow me for updates
When I'm not coding...
Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!