(C# EF Core) SQL Server Database Connectivity in an ASP.NET Core Razor Page Project

Things you will Need

These must be available -

1. SQL Server must be running and accessible. You can verify this in Visual Studio 2019/2022 by the menu View > SQL Server Object Explorer. You should see one or two instances running. See the linked video also. If your instance is not visible, then you may have to contact your database administrator to obtain your connection string containing the user-id/password/ip etc.,
2. Right click the instance to get the connection string, or obtain it from your database administrator.
3. Verify that you can connect to the SQL server with your UserID and Password. You can verify it through the Visual Studio SQL Server Object Explorer, or through some third party SQL Server connection interface.

Video Explanation

Please watch the following youtube video:

Add Nuget Package

Run this on package manager console -

PM > Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 5.0.12

Configure your Startup.cs File

This is the bare minimum startup.cs file that will work for our case -

//

// namespaces 
using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.DependencyInjection;

using MyRazorApp.Pages.Models;

namespace MyRazorApp
{

  public class Startup
  {

    public void ConfigureServices(IServiceCollection services)
    {

      // this is connection string for SQL 
      // Initial Catalog is the name of database 
      // EF Core will create the database and its 
      // tables because we are using code-first approach 
      String strConnString = @"Initial Catalog=mydb;Data Source=(localdb)\MSSQLLocalDB...etc";

      services.AddDbContext<MyProjectContext>(
          opt => opt.UseSqlServer(strConnString)
      );

      services.AddRazorPages();

    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {

      // NOTE: only bare minimum pipeline shown 
      app.UseRouting();

      app.UseEndpoints(endpoints =>
      {

        endpoints.MapRazorPages();

      });

    }

  }

}

Following is the Program.cs file - the same as visual studio creates for you -

// Program.cs as created by visual studio 
using Microsoft.AspNetCore.Hosting;

using Microsoft.Extensions.Hosting;

public class Program
{

  public static void Main(string[] args)
  {

    CreateHostBuilder(args).Build().Run();

  }

  public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureWebHostDefaults(webBuilder =>
        {
          webBuilder.UseStartup<Startup>();

        });

}

Model and Context Classes

Use these simple model and context classes

// model class 
public class Item
{

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int ID { get; set; }

  // column for name property 
  public String Name { get; set; }
}


// dbcontext 
public class MyProjectContext : DbContext
{

  public MyProjectContext(DbContextOptions options) : base(options)
  {

    // IMPORTANT: you will get exception here 
    // if your SqlServer connection fails 
    Database.EnsureCreated();

  }


  // MUST be PLURAL 
  public DbSet<Item> Items { get; set; }
}

The Index Razor Page

Use this for the Index.cshtml file

@page

// your namespace - mine is MyRazorApp 
@model MyRazorApp.Pages.IndexModel

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<form method="post">

  <table>

    <tr>

      <td>
        <label asp-for="DataItem.Name"></label>
      </td>

      <td>
        <input asp-for="DataItem.Name" />
      </td>

    </tr>

    <tr>
      <td></td>

      <td>
        <input type="submit" />
      </td>

    </tr>

  </table>

</form>

<span>@TempData["Message"]</span>

Use this for the Index.cshtml.cs file

// use your namespace names - mine is MyRazorApp 
using Microsoft.AspNetCore.Mvc;

using Microsoft.AspNetCore.Mvc.RazorPages;

using MyRazorApp.Pages.Models;

using System;

using System.Threading.Tasks;

namespace MyRazorApp.Pages
{

  public class IndexModel : PageModel
  {

    readonly MyProjectContext _ctx;

    public IndexModel(MyProjectContext ctx)
    {

      _ctx = ctx;

    }

    [BindProperty]
    public Item DataItem { get; set; }

    // used for displaying message upon redirect 
    [TempData]
    public String Message { get; set; }

    // on post handler 
    public async Task<IActionResult> OnPostAsync()
    {

      _ctx.Items.Add(DataItem);

      try
      {

        await _ctx.SaveChangesAsync();

        Message = "Saved OK!";

      }

      catch(Exception ex)
      {

        Message = "failed: " + ex.InnerException.Message;

      }

      return RedirectToPage();

    }

  }

}

Run the Project

Run the project to see that you can add records!