Introduction

In this post, we will see how to configure the Azure SQL database using vs 2019. This post is a continuation of how to provision a relational database.

To read more about how to provision the relational database in the Azure portal, take a look at my post provision and configure relational databases azure portal

Demo Application Overview

In this demo, we will see how to create a small shopping API application by designing its database schema with minimum tables required for demo.

Once we design our database, we will see how to migrate these changes to the Azure SQL database using DOTNET CLI.

This demo application is created using Visual Studio 2019 and in DOTNET CORE 2.2

In my previous post, I have explained how to provision relational databases in the Azure portal. You may read the post to set up the new SQL database in the Azure portal.

Application Architecture

I have created this application with standard architecture addressing the separation of concerns. We have the following layers in our application.

• API (Main project)
• CORE
• CrossCutting
• Service
• Data

The full demo application is available for free and you may download from my GitHub repository.

Database schema to configure Azure SQL database

We have three tables namely Category, Product and ProductImages. Each product will be under a Category and each product might have more than one image to show.

Based on this we will start our relationship in our model. All the entities will go to the Core project.

using System.ComponentModel.DataAnnotations;

namespace KarthikTechBlog.Shopping.Core
{
    public class Category
    {
        public int Id { get; set; }
        [Required]
        [MinLength(2), MaxLength(200)]
        public string Name { get; set; }
        public Status Status { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace KarthikTechBlog.Shopping.Core
{
	public enum Status
    {
        InActive = 0,
        Active = 1,
        Suspended = 2
    }
	
    public class Product
    {
        public int Id { get; set; }
        [Required]
        [MinLength(5), MaxLength(200)]
        public string Name { get; set; }
        public decimal Price { get; set; }
        public DateTime? AvailableSince { get; set; }
        public int StockCount { get; set; }
        public int CategoryId { get; set; }
        public Category Category { get; set; }
        public List ProductImages { get; set; }
        public Status Status { get; set; }

    }
}

using System.ComponentModel.DataAnnotations;

namespace KarthikTechBlog.Shopping.Core
{
    public class ProductImages
    {
        public int Id { get; set; }
        [Required]
        [MinLength(128), MaxLength(128)]
        public string ImageId { get; set; }
        public int ProductId { get; set; }
        public Product Product { get; set; }
        public Status Status { get; set; }
    }
}

To follow along with me, download or clone the demo application from the GitHub URL .

Database Migrations

Open the project folder "KarthikTechBlog.Shopping.Data" using the command line.

I'm going to show how to generate a migration script and update the database using DOTNET CLI.

dotnet ef migrations add initalcreate -s ../KarthikTechBlog.Shopping.API/KarthikTechBlog.Shopping.API.csproj

In this, we are creating a migration script named "initialcreate" and specifying our startup project by specifying "-s project-path". Once you create migration using the above command, you can see the migration folder added to your project.

dotnet ef database update -s ../KarthikTechBlog.Shopping.API/KarthikTechBlog.Shopping.API.csproj

To push the created migration script to our database, run the above command.

Conclusion

In this post, you learned how to configure relational databases with azure SQL database using Visual Studio 2019 and DOTNET CLI.

That’s all from this post. If you have any questions or just want to chat with me, feel free to leave a comment below. If you want to get a continuous update about my blog, make sure to follow me on Facebook and Twitter.