Build REST CRUD APIs using Azure SQL Database and ASP.NET

To build REST CRUD APIs using Azure SQL Database and ASP.NET, you can follow these steps:

Step 1: Create an Azure SQL Database

1. Sign In to the Azure Portal

• Open Azure Portal.
• Sign in with your Azure credentials. If you don’t have an account, create one.

2. Navigate to the "Create a Resource" Section

• In the Azure Portal, click on "Create a resource".
• In the Search box, type "SQL Database" and select it from the search results.

3. Configure the SQL Database

This step involves configuring the necessary settings for your database.

Database Settings:

• Subscription: Select your Azure subscription.
• Resource Group: You can either create a new resource group or select an existing one. Resource groups help organize and manage related resources.
• Database Name: Choose a name for your database (e.g., MySqlDb).
• Server: You need to create a new SQL Server or select an existing one.
  • If creating a new server, click on "Create new", and provide:
    • Server Name: A unique name for your server (e.g., my-sql-server).
    • Admin Username: Choose an admin username for managing the database.
    • Password: Set a strong password for the SQL server.
    • Location: Choose a location near your application or user base for better performance (e.g., East US).
• Pricing Tier: Choose the performance tier. Azure offers several options:
  • Basic: For smaller workloads.
  • Standard: For medium-sized workloads.
  • Premium: For larger workloads and high availability.
• Collation: Leave this as default unless you need a specific collation for your database.

Additional Settings (Optional but Recommended):

• Backup and Retention: Azure handles automatic backups by default.
• Security & Networking: Configure firewall rules to allow your application or local machine to access the database. By default, Azure restricts access for security reasons.
• Data Encryption: Transparent Data Encryption (TDE) is enabled by default.

4. Review and Create the Database

• After configuring all the settings, review your selections and click Create.
• Azure will begin provisioning the SQL Database, which may take a few minutes.
• Once created, you can access the database from the SQL Databases section in the Azure portal.

---

Step 2: Configure Connection in Your ASP.NET Application

After your Azure SQL Database is created, the next step is to configure your ASP.NET application to connect to the database.

1. Obtain the Connection String

• Once your SQL Database is provisioned, go to the Overview section of your database in the Azure portal.
• Click on "Connection strings" to view the connection string for your database.
  • The connection string will look something like this:

    Server=tcp:my-sql-server.database.windows.net,1433;Initial Catalog=MySqlDb;Persist Security Info=False;User ID=adminuser;Password=yourpassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
    

2. Add Connection String to appsettings.json

In your ASP.NET application, you will need to store the connection string in the appsettings.json file for easy access. Add the following to your appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:my-sql-server.database.windows.net,1433;Initial Catalog=MySqlDb;Persist Security Info=False;User ID=adminuser;Password=yourpassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

3. Set Up Entity Framework Core in ASP.NET

If you want to use Entity Framework Core to interact with your Azure SQL Database, follow these steps:

Install Required NuGet Packages:

• Open the NuGet Package Manager or use the following command to install the necessary packages:

  dotnet add package Microsoft.EntityFrameworkCore.SqlServer
  dotnet add package Microsoft.EntityFrameworkCore.Tools

Create a Model Class:

• In your ASP.NET project, create a class that represents the data structure you want to store in the database. For example:

  public class Product
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public decimal Price { get; set; }
  }

Create a DbContext Class:

• Create a class that inherits from DbContext, which will be responsible for interacting with the database.

  public class ApplicationDbContext : DbContext
  {
      public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
          : base(options)
      { }
  
      public DbSet<Product> Products { get; set; }
  }

Configure DbContext in Startup Class:

• In Startup.cs (or Program.cs if using .NET 6+), configure the DbContext to use the SQL Server provider and the connection string from appsettings.json.

  public void ConfigureServices(IServiceCollection services)
  {
      services.AddDbContext<ApplicationDbContext>(options =>
          options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
      services.AddControllers();
  }

4. Create Migrations (Using Entity Framework Core)

To create the database schema based on your model, use the EF Core Migrations tool:

• Run the following command in your terminal or Package Manager Console:

  dotnet ef migrations add InitialCreate
  dotnet ef database update

This will generate the database schema for your Product model in the Azure SQL Database.

---

Step 3: Build REST CRUD APIs in ASP.NET

Now, that the database is set up, you can start building the RESTful CRUD API.

1. Create a Controller for CRUD Operations

Create an API controller to handle CRUD operations for your Product model.

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public ProductsController(ApplicationDbContext context)
    {
        _context = context;
    }

    // GET: api/products
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
    {
        return await _context.Products.ToListAsync();
    }

    // GET: api/products/5
    [HttpGet("{id}")]
    public async Task<ActionResult<Product>> GetProduct(int id)
    {
        var product = await _context.Products.FindAsync(id);

        if (product == null)
        {
            return NotFound();
        }

        return product;
    }

    // POST: api/products
    [HttpPost]
    public async Task<ActionResult<Product>> PostProduct(Product product)
    {
        _context.Products.Add(product);
        await _context.SaveChangesAsync();

        return CreatedAtAction("GetProduct", new { id = product.Id }, product);
    }

    // PUT: api/products/5
    [HttpPut("{id}")]
    public async Task<IActionResult> PutProduct(int id, Product product)
    {
        if (id != product.Id)
        {
            return BadRequest();
        }

        _context.Entry(product).State = EntityState.Modified;
        await _context.SaveChangesAsync();

        return NoContent();
    }

    // DELETE: api/products/5
    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteProduct(int id)
    {
        var product = await _context.Products.FindAsync(id);
        if (product == null)
        {
            return NotFound();
        }

        _context.Products.Remove(product);
        await _context.SaveChangesAsync();

        return NoContent();
    }
}

Step 4: Test the API

You can now run your ASP.NET application and use tools like Postman or cURL to test your CRUD APIs:

• GET /api/products: Retrieve all products.
• GET /api/products/{id}: Retrieve a product by its ID.
• POST /api/products: Create a new product.
• PUT /api/products/{id}: Update an existing product.
• DELETE /api/products/{id}: Delete a product.

---

Summary

1. Create an Azure SQL Database: Use the Azure portal to set up your SQL Database.
2. Configure Your ASP.NET Application: Add the connection string and configure Entity Framework.
3. Build REST CRUD APIs: Use ASP.NET to create an API controller that performs CRUD operations.
4. Test the API: Use tools like Postman to ensure everything is working.