How to connect the model to the local Microsoft database? (C# Razor MVC structure - MVS editor)

Question

(self learning here) I'm trying to make a simple website where users can request to change their passwords. There is a page for users' input. I want their input to be stored into the database. I made a database with a table titled dbo.PasswordHint.

The controller is:

using Microsoft.AspNetCore.Mvc;

namespace albarhelp.Controllers
{
    public class PasswordController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }
    }
}

The model is:

namespace albarhelp.Models
{
    public class PasswordModel
    {
        public int StaffNo { get; set; }

        public int CivilID { get; set; }

        public string FullName { get; set; }

        public string SuggestPassword { get; set; }

        public string ContactTel { get; set; }
    }
}

Please help me.

What I understand is that I have to make a connection string in the appsettings.json but then add a code to the model. But I'm not sure what to write.

Answer 1

To connect your C# Razor MVC application to a local Microsoft SQL Server database, you'll need to follow a few steps. Here's a general guide to help you set up the connection:

Step 1: Add Connection String to appsettings.json Open your appsettings.json file and add a connection string under the "ConnectionStrings" section. Replace the placeholders with your actual database details:

> {   "ConnectionStrings": {
>     "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=YourDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"
> },

Step 2: Install Entity Framework Core Ensure you have Entity Framework Core installed. If not, you can install it using the following NuGet Package Manager Console command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Step 3: Create a Database Context Create a new class for your database context. This class will inherit from DbContext and will represent your database.

using Microsoft.EntityFrameworkCore;

namespace albarhelp.Models
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<PasswordModel> PasswordHints { get; set; }
    }
}

Step 4: Update Startup.cs In the ConfigureServices method of your Startup.cs file, add the following code to configure your database context:

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

Step 5: Update Controller Update your PasswordController to use the database context:

using albarhelp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace albarhelp.Controllers
{
    public class PasswordController : Controller
    {
        private readonly ApplicationDbContext _context;

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

        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Index(PasswordModel passwordModel)
        {
            if (ModelState.IsValid)
            {
                _context.PasswordHints.Add(passwordModel);
                _context.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(passwordModel);
        }
    }
}

Step 6: Run Migrations In the Package Manager Console, run the following commands to create and apply migrations:

**

Add-Migration InitialCreate
Update-Database

** These commands will create the necessary database tables based on your models.

Step 7: Use the Database Now, you can use the _context instance in your controller to interact with the database. The code above shows how to add a new PasswordModel to the database when the form is submitted.