CRUD Pages

I had some trouble following the CRUD Page notes so I decided to basically rewrite them here after finishing that stuff.

Since this page is public, I won't be putting the actual IP address for the database, but it will be the same for everyone. It is in the original CRUD notes & you should have it in mysql anyway. I'll use 11.11.11.111 as a placeholder IP address.

1. Create Project

First, you want to just create the application using the VS template.

Open VS and click "Create a new project"

The template we want is "ASP.NET Core Web App (Razor Pages).
If you don't see this template, you may have to go back and install some extra things. It doesn't matter what you name/ where you safe the project. Just make sure it's something you'll understand & remember.

Now, we want to install the correct packages. On the toolbar, click "Tools > NuGet Package Manager > Manage NuGet Packages for Solution...". This should open a sidebar on the left.

In the sidebar, click "Browse" and search for "entity framework core". Of the following packages, select them, click the box next to your project name, and hit "install".

  1. Microsoft.EntityFrameworkCore
  2. Microsoft.EntityFrameworkCore.Design
  3. Microsoft.EntityFrameworkCore.Tools
  4. Microsoft.EntityFrameworkCore.SqlServer
  5. Microsoft.VisualStudio.Web.CodeGeneration.EntityFrameworkCore

2. Connect Database

With the project set up, we can connect your database.

On the toolbar, click "View > Server Explorer" This will add a sidebar to the left.

You should see something labeled "Data Connections" Right click it and hit "Add Connection"

This will bring up a popup. Fill it out as follows:

  1. Data Source: Microsoft SQL Server (SqlClient)
  2. Authentication: Sql Server Authentication
  3. Server name: [Server IP address]
  4. User name: SqlServer username (school email prefix)
  5. Password: SqlServer password (student id)
  6. Trust Server Certificate: [Check]
  7. Save my password: [Check]
  8. Select or enter a database name: name of your database in SqlServer

Click "Test Connection" to make sure everything's working, then click "Advanced" to proceed.

Make sure that "TrustServerCertificate" = True, then click "OK"

3. Create Context Files

Click "Tools > NuGet Package Manager > Package Manager Console"

This will open up a terminal at the bottom of your screen. Copy and paste the following into the terminal, replacing the highlighted portions with your information.

Package Manager Console

Scaffold-DbContext 'Data Source=serverIP; Initial Catalog= yourDataBase; User Id = yourUserId; password= yourPwd; TrustServerCertificate=True' Microsoft.EntityFrameworkCore.SqlServer

You should now see C# (.cs) files in the Solution Explorer for each of your tables, and one for the database.

4. Set Application Settings

The rest of this process involves editing files. I will include excerpts of the files to give you context for where to make edits. Existing code will be light brown, and values you need to change to your information will be pink again.

First, we want to edit the settings in "appsettings.Development.json" and "appsettings.json"

Note: You can choose the connection name here.

appsettings.Development.json

{
"DetailedErrors": true,
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},

"ConnectionStrings": {
"ConnectionName": "Data Source = serverIP; Initial Catalog= yourDataBase; User ID = yourUserID; Password = yourPwd; TrustServerCertificate = True;"
}
 }

appsettings.json

{
"DetailedErrors": true,
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",

"ConnectionStrings": {
"ConnectionName": "Data Source = serverIP; Initial Catalog= yourDataBase; User ID = yourUserID; Password = yourPwd; TrustServerCertificate = True;"
}
 }

As a reminder, you are using your SqlServer login credentials.

5. Modify Context File

Now, we want to edit thethe database context file. We are going to use the connection string to avoid exposing the user ID and password.

You should see a file like "yourDataBase.cs" (with the specific name of your data base, of course.)

We want to make four changes...

  1. Add "using" satements
  2. Remove empty public contextname constructor
  3. Add public IConfiguration declaration
  4. Modify OnConfiguring function

yourDataBaseContext.cs

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
 using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Configuration;

namespace ParkingLotWebApp;

public partial class FLast1Context : DbContext
{

public yourDataBaseContext()
{
}

public FLast1Context(DbContextOptions<yourDataBase> options)
: base(options)
{
}

public virtual DbSet TblCars { get; set; }
public virtual DbSet TblFaculties { get; set; }
public virtual DbSet TblStudents { get; set; }

public IConfiguration myconfig { get;}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
if(!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(myconfig.GetConnectionString("ConnectionName"));
}
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
...
...
...

6. Modify Program.cs

For this sep you will need the name of the context.cs page we just modified in the previous step, and the name of your connection string created in part 4.

Open "Program.cs"

Program.cs

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddRazorPages();
builder.Services.AddDbContext(x
=> x.UseSqlServer(builder.Configuration.GetConnectionString("ConnectionName")));

var app = builder.Build();
...
...
...

7. Make CRUD Pages

To make the CRUD pages, first make a folder for each table by right clicking "Pages" and clicking "Add > New Folder"

Drag each of the table cs files into their respective folders. Then, right click that folder and hit "Add > New Scaffolded Item".

A popup will open. Select "Razor Pages > Razor Pages using Entity Framework (CRUD)", then hit "Add".

Fill out the next page with the following information:

  1. Model class: The current table
  2. DbContext class: yourDataBaseContext
  3. Use Layout Page: [Check] ~/Pages/Shared/_Layout.cshtml

It will take a few moments to process, then it should look something like below once you've done this for all tables.

Remember to save all by clicking on the save all (double floppy disk) button.

Finally, test the CRUD pages by right clicking on "index.cshtml" and selecting "View in Browser"

If that worked, you should be all done!