ASP.NET Core  

Integrating SQL Server with Angular + ASP.NET Core for Full-Stack Development

Introduction

In modern web development, building a full-stack application requires a frontend framework, a backend server, and a database. Angular, ASP.NET Core, and SQL Server form a powerful combination for full-stack development.

  • Angular provides a responsive, interactive frontend.

  • ASP.NET Core handles server-side logic, APIs, and security.

  • SQL Server stores and manages data efficiently.

Integrating these three technologies allows developers to create enterprise-grade web applications that are scalable, maintainable, and performant.

This article explains step-by-step how to integrate SQL Server with an Angular + ASP.NET Core project, covering database setup, backend API development, frontend communication, and best practices.

Step 1: Setting Up SQL Server

1. Install SQL Server

  1. Download SQL Server from https://www.microsoft.com/sql-server.

  2. Install SQL Server Developer or Express Edition.

  3. During installation, choose Mixed Mode Authentication (SQL Server Authentication + Windows Authentication).

  4. Create a SQL Server user with a strong password for your application.

2. Install SQL Server Management Studio (SSMS)

  • SSMS is used to manage databases, write queries, and monitor the server.

  • Download from https://aka.ms/ssmsfullsetup.

3. Create a Database

Open SSMS and create a new database:

CREATE DATABASE FullStackAppDb;

4. Create a Sample Table

USE FullStackAppDb;

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL,
    Email NVARCHAR(50) UNIQUE NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

  • Id is the primary key.

  • Email must be unique.

  • CreatedAt stores the creation timestamp.

Step 2: Setting Up ASP.NET Core Backend

ASP.NET Core acts as the bridge between SQL Server and Angular.

1. Create a Web API Project

Using the command line:

dotnet new webapi -n FullStackApi
cd FullStackApi
dotnet run

Or in Visual Studio:

  • Select ASP.NET Core Web API template.

  • Enable OpenAPI/Swagger for API testing.

2. Install Entity Framework Core

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

3. Configure DbContext

Create a Models folder and define the User model:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

Create AppDbContext.cs:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

    public DbSet<User> Users { get; set; }
}

Add DbContext in Program.cs:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();
builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAngular",
        builder => builder.WithOrigins("http://localhost:4200")
                          .AllowAnyMethod()
                          .AllowAnyHeader());
});

var app = builder.Build();
app.UseCors("AllowAngular");
app.UseAuthorization();
app.MapControllers();
app.Run();

Add connection string in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=FullStackAppDb;User Id=sa;Password=YourPassword;"
}

4. Create a Users Controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly AppDbContext _context;
    public UsersController(AppDbContext context) => _context = context;

    [HttpGet]
    public async Task<IEnumerable<User>> Get() => await _context.Users.ToListAsync();

    [HttpPost]
    public async Task<ActionResult<User>> Post(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return CreatedAtAction(nameof(Get), new { id = user.Id }, user);
    }
}

  • GET: Retrieves all users.

  • POST: Adds a new user.

Step 3: Setting Up Angular Frontend

1. Create an Angular Project

ng new fullstack-app
cd fullstack-app
ng serve --open

  • The app runs at http://localhost:4200.

2. Install HttpClientModule

In app.module.ts:

import { HttpClientModule } from '@angular/common/http';
import { FormsModule } from '@angular/forms';

@NgModule({
  imports: [
    BrowserModule,
    HttpClientModule,
    FormsModule
  ],
})
export class AppModule { }

3. Create a Service for API Calls

ng generate service services/user

user.service.ts:

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';

export interface User {
  id?: number;
  name: string;
  email: string;
  createdAt?: string;
}

@Injectable({ providedIn: 'root' })
export class UserService {
  private apiUrl = 'https://localhost:5001/api/users';

  constructor(private http: HttpClient) {}

  getUsers(): Observable<User[]> {
    return this.http.get<User[]>(this.apiUrl);
  }

  addUser(user: User): Observable<User> {
    return this.http.post<User>(this.apiUrl, user);
  }
}

4. Create a Component to Display Users

ng generate component user-list

user-list.component.ts:

import { Component, OnInit } from '@angular/core';
import { UserService, User } from '../services/user.service';

@Component({
  selector: 'app-user-list',
  templateUrl: './user-list.component.html',
})
export class UserListComponent implements OnInit {
  users: User[] = [];
  newUser: User = { name: '', email: '' };

  constructor(private userService: UserService) {}

  ngOnInit(): void {
    this.userService.getUsers().subscribe(data => this.users = data);
  }

  addUser(): void {
    this.userService.addUser(this.newUser).subscribe(user => {
      this.users.push(user);
      this.newUser = { name: '', email: '' };
    });
  }
}

user-list.component.html:

<h2>User List</h2>
<ul>
  <li *ngFor="let user of users">{{ user.name }} - {{ user.email }}</li>
</ul>

<h3>Add New User</h3>
<input [(ngModel)]="newUser.name" placeholder="Name">
<input [(ngModel)]="newUser.email" placeholder="Email">
<button (click)="addUser()">Add</button>

Step 4: Running and Testing the Full Stack Application

  1. Start SQL Server and ensure the database is accessible.

  2. Run ASP.NET Core API:

dotnet run

  1. Run Angular app:

ng serve --open

  1. Open http://localhost:4200 to view users and add new ones.

  • Angular calls ASP.NET Core API, which communicates with SQL Server.

  • Data is stored and retrieved in real-time.

Best Practices

  1. Environment Variables: Store connection strings securely.

  2. CORS Configuration: Always allow only trusted origins.

  3. Validation: Validate input on both frontend and backend.

  4. Error Handling: Handle API errors gracefully in Angular.

  5. Separation of Concerns: Keep frontend and backend modular.

  6. Use Entity Framework Migrations: To manage schema changes efficiently.

Conclusion

Integrating SQL Server with Angular + ASP.NET Core enables web developers to build robust full-stack applications. SQL Server manages data efficiently, ASP.NET Core provides a secure API layer, and Angular delivers a dynamic user interface.

By following this step-by-step approach, developers can create full-stack applications that are scalable, maintainable, and enterprise-ready. Mastering this integration opens the door to building real-world web applications, from simple dashboards to complex e-commerce platforms.

With this knowledge, you are now ready to expand your application with features like authentication, authorization, pagination, and advanced queries for a complete enterprise solution.

Membership not found