Setting Up a SQL Server Database in Full Stack .NET Applications

June 21, 2025

🛠️ Setting Up a SQL Server Database in Full Stack .NET Applications

Whether you're building a full stack .NET application with ASP.NET Core (backend) and a SQL Server database, integrating the database is a critical step. Below is a complete, beginner-friendly overview to get you up and running.

📦 Tech Stack Overview

Frontend: HTML/CSS/JavaScript or React/Angular (optional for now)

Backend: ASP.NET Core Web API / MVC

Database: Microsoft SQL Server

ORM: Entity Framework Core (EF Core)

🧩 Step-by-Step Setup

1️⃣ Install Required Tools

Visual Studio (Community Edition is fine)

.NET SDK

SQL Server Express or use Azure SQL

SQL Server Management Studio (SSMS)

2️⃣ Create a New .NET Project

Open Visual Studio:

File > New > Project > ASP.NET Core Web Application

Choose API or MVC

Select .NET 6/7/8 depending on your environment.

3️⃣ Define the Model

Create a class in the Models folder:

csharp

Copy

Edit

public class Employee

{

public int Id { get; set; }

public string Name { get; set; }

public string Position { get; set; }

}

4️⃣ Set Up DbContext

Create AppDbContext.cs:

csharp

Copy

Edit

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext

{

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

public DbSet<Employee> Employees { get; set; }

}

5️⃣ Add Connection String in appsettings.json

json

Copy

Edit

"ConnectionStrings": {

"DefaultConnection": "Server=localhost;Database=MyCompanyDB;Trusted_Connection=True;"

}

Replace localhost with your SQL instance name if needed (e.g., localhost\\SQLEXPRESS)

6️⃣ Register DbContext in Program.cs or Startup.cs

For .NET 6 and newer:

csharp

Copy

Edit

builder.Services.AddDbContext<AppDbContext>(options =>

options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Add:

csharp

Copy

Edit

using Microsoft.EntityFrameworkCore;

7️⃣ Add EF Core Tools (if not already)

Install via NuGet or CLI:

bash

Copy

Edit

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add package Microsoft.EntityFrameworkCore.Tools

8️⃣ Run Migrations

bash

Copy

Edit

dotnet ef migrations add InitialCreate

dotnet ef database update

This creates your SQL Server database and tables.

Ensure you’ve enabled EF CLI tools:

dotnet tool install --global dotnet-ef

9️⃣ Create a Controller (for API)

csharp

Copy

Edit

[Route("api/[controller]")]

[ApiController]

public class EmployeesController : ControllerBase

{

private readonly AppDbContext _context;

public EmployeesController(AppDbContext context)

{

_context = context;

}

[HttpGet]

public async Task<ActionResult<IEnumerable<Employee>>> GetEmployees()

{

return await _context.Employees.ToListAsync();

}

[HttpPost]

public async Task<ActionResult<Employee>> AddEmployee(Employee emp)

{

_context.Employees.Add(emp);

await _context.SaveChangesAsync();

return CreatedAtAction(nameof(GetEmployees), new { id = emp.Id }, emp);

}

🔄 Test Your API

Use tools like:

Postman

Swagger (auto-generated in .NET Core projects)

📁 Folder Structure Example

pgsql

Copy

Edit

MyApp/

├── Controllers/

│ └── EmployeesController.cs

├── Models/

│ └── Employee.cs

├── Data/

│ └── AppDbContext.cs

├── appsettings.json

└── Program.cs

✅ Conclusion

You've now connected a full stack .NET application to a SQL Server database using Entity Framework Core. This setup forms the foundation for scalable web applications with clean separation between backend logic and data.

Learn Full Stack Dot NET Training in Hyderabad

Databases in Full Stack .NET

Understanding Asynchronous Programming in C# for Back-End Development

Best Practices for Building Secure APIs in .NET Core

Visit Our Quality Thought Training in Hyderabad

Get Directions