Building a Task Management System in ASP.NET MVC: Creating the Database and Tables

In this mini-project tutorial, I develop the Task Management System Project database and tables and also show you how to establish a connection between the SQL Server and the ASP.NET MVC application.

First, we explore more about databases and tables in the form of SQL server scripts.

Create Database

 Create Database TMSDB 

The above single line of code will create a database in SQL Server.

Create Tables

We create a Users table and a Task table as per our requirements. Because the Users table is used to store user information and the Task table is used to store task information.

Users Table:

CREATE TABLE Users (
    UserId INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL,
    Password NVARCHAR(255) NOT NULL,
    Role NVARCHAR(50) NOT NULL
);

In this table, UserId is the primary key, and we're using an auto-incrementing identity column. Username and Password will store user credentials, and Role will define whether a user is a regular user or an administrator.

Tasks Table:

CREATE TABLE Tasks (
    TaskId INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(100) NOT NULL,
    Description NVARCHAR(MAX),
    DueDate DATETIME,
    Status NVARCHAR(50),
    UserId INT FOREIGN KEY REFERENCES Users(UserId)
);

The Tasks table has a foreign key relationship with the Users table, linking each task to a specific user. TaskId is the primary key, and we're storing information such as Title, Description, DueDate, and Status for each task.

Establishing a Database Connection with ASP.NET MVC

After creating Asp.net MVC Task Management System application and Open Package Manager Console.

Let me show you how to open the package manager console.

1. In Visual Studio, click on Tools Menu
2. Click on Nuget Package Manager
3. Click on Package Manager Console

In Visual Studio bottom Package Manager Console Window opened and run the following commands:

Add-Migration InitialCreate
Update-Database

These commands will create the initial migration and apply it to the database.

Connecting ASP.NET MVC and Database

In your Startup.cs file, add the following code in the ConfigureServices method to set up the database connection:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Ensure you have a connection string named "DefaultConnection" in your appsettings.json file.