ASP.NET Core API connect to Azure SQL Database

SQL Database Integration

Entity Framework (EF) Package — Object Relational Mapper (ORM)

An object-relational mapper (O/RM) that enables .NET developers to persist objects to and from a data source — eliminates the need for most of the data access code developers would typically need to write.

1. Add EF package using CLI or NuGet Package Manager
2. Check the newly installed package under NuGet directory

Connection Strings — appsettings.json

Will be used ConfigureServices — GetConnectionString() method in Startup.cs

“ConnectionStrings”: {
“mydatabaseapp”: “Server=tcp:<databasehost>,1433;Initial Catalog=<databasename>;Persist Security Info=False;User ID=<username>;Password=<passowrd>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;”
}

DbContext — Hold properties representing collections for entities

Must have a constructor that accepts DbContextOptions and passes this argument to the base DbContext constructor.

Create a new subclass of DBContext

public class ClientDbContext : DbContext
{
   public ClientDbContext(DbContextOptions<ClientDbContext> options): base(options)
{
}
   public DbSet<Client> Clients { get; set; }
}

ConfigureServices

Startup.cs — Configure the app’s services

To configure DbContext subclass to use a SQL Server database with a connection string defined in Configuration — use .AddDbContext

public void ConfigureServices(IServiceCollection services)
{
   services.AddControllers();
   services.AddDbContext<ClientDbContext>(opts => opts.UseSqlServer(Configuration.GetConnectionString(“mydatabaseapp”)));
}

Controller

Responding to request

[Route(“api”)]
[ApiController]
public class ClientController : ControllerBase
{
   private readonly ILogger<ClientController> _logger;
   private readonly ClientDbContext _context;
   public ClientController(ILogger<ClientController> logger, ClientDbContext context)
{
   _logger = logger;
   _context = context;
}
[HttpGet]
[Route(“clients”)]
public IActionResult GetAll()
{
   _logger.LogInformation(“Get all clients”);
   var data = _context.Clients.AsQueryable();
   return Ok(data);
}
}

Entity / Model

Entity properties to map table columns

[Table(“Client”)]
public class Client
{
[Column(“client_id”)]
public int ClientId { get; set; }

[Column(“client_name”)]
public string ClientName { get; set; }

[Column(“client_address”)]
public string ClientAddress { get; set; }
}