In this article I will explain with an example, how to use Stored Procedure in ASP.Net Core (.Net Core 8) Razor Pages.
Note: For beginners in ASP.Net Core (.Net Core 8) Razor Pages, please refer my article ASP.Net Core 8 Razor Pages: Hello World Tutorial with Sample Program example.
 
 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Download and install Northwind Database
 
 

Stored Procedure

The following Stored Procedures accepts @ContactName parameter which is used to perform a search on the records in Customers Table.
CREATE PROCEDURE Customers_SearchCustomers
       @ContactName NVARCHAR(30)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT TOP 10 CustomerId
            ,ContactName
            ,City
            ,Country
      FROM Customers
      WHERE ContactName LIKE '%' + @ContactName + '%'
END
 
 

Model

The Model class consists of the following properties.
public class Customer
{
    public string CustomerID { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}
 
 

Database Context

The very first step is to create Database Context class.
Note: For beginners in Entity Framework, please refer my article .Net Core 8: Entity Framework Tutorial in ASP.Net Core Razor Pages. It covers all the information needed for connecting Entity Framework.
 
Inside the class, first inherit the EntityFrameworkCore namespace and then inherit the DbContext class.
Then using Dependency Injection, a Constructor is created DbContextOptions are passed as parameter and also the Constructor of base class i.e. DbContext class is inherited.
A DbSet Collection property of Customer Entity is created, which will be later used for holding the Data fetched from SQL Server Database Table.
Finally, there is a method SearchCustomers which is used to call the Stored Procedure and return its result.
The Stored Procedure is called using the FromSqlRaw function. The FromSqlRaw function accepts the following two parameters:
1. SQL Query – SQL Query to execute the Stored Procedure.
2. Parameters – One or more objects of SqlParameter class.
Note: The FromSqlRaw function belongs to the Microsoft.EntityFrameworkCore namespace.
 
using Search_EF_MVC_Core_8.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
 
namespace Search_EF_MVC_Core_8
{
    public class DBCtx : DbContext
    {
        public DBCtx(DbContextOptions<DBCtx> options) : base(options)
        {
        }
 
        public DbSet<Customer> Customers { get; set; }
 
        public IQueryable<Customer>SearchCustomers(string contactName)
        {
            SqlParameter pContactName = new SqlParameter("@ContactName", contactName);
            return this.Customers.FromSqlRaw("EXECUTECustomers_SearchCustomers @ContactName", pContactName);
        }
    }
}
 
 

Razor PageModel (Code-Behind)

The PageModel consists of following Handler methods.

Handler method for handling GET operation

Inside this Handler method, the SearchCustomers method of the Database Context class is called and an empty string is passed as parameter.
Finally, the returned results are converted into Generic List collection and assigned to the public property Customers and returned to the Razor Page.
 

Handler method for handling POST operation

This Handler method is executed when the Search Button is clicked.
Inside this Handler method, the value of the Customer Name TextBox is submitted to this Handler method and the received value is passed as parameter to the SearchCustomers function.
Finally, the returned results are converted into Generic List collection and assigned to the public property Customers and returned to the Razor Page.
public class IndexModel : PageModel
{
    private DBCtx  Context { get; }
    public IndexModel(DBCtx _context)
    {
        this.Context = _context;
    }
 
    public List<Customer> Customers { get; set; }
 
    public void OnGet()
    {
        this.Customers = this.Context.SearchCustomers("").ToList();
    }
 
    public void OnPostSearch(string customerName)
    {
        this.Customers = this.Context.SearchCustomers(customerName).ToList();
    }
}
 
 

Razor Page (HTML)

HTML Markup

The HTML of Razor Page consists of an HTML Table for displaying following ASP.Net Tag Helpers attributes, a HTML TextBox and a Submit Button.
method – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
When the Search button is clicked, the Form is submitted.
Note: In the Razor PageModel, the Handler method name is OnPostSearch but here it will be specified as Search when calling from the Razor HTML Page.
 

Displaying the records

For displaying the records, an HTML Table is used. A loop will be executed over the Model property which will generate the HTML Table rows with the Customer records.
@page
@using Search_EF_Core_Razor_8.Models
@model Search_EF_Core_Razor_8.Pages.IndexModel
@addTagHelper*,Microsoft.AspNetCore.Mvc.TagHelpers
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post">
        <span>Customer Name:</span>
        <input type="text" name="CustomerName" />
        <input type="submit" value="Search" asp-page-handler="Search" />
        <br /><br />
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>Customer Id</th>
                <th>ContactName</th>
                <th>City</th>
                <th>Country</th>
            </tr>
            @foreach (Customer customer in Model.Customers)
            {
                <tr>
                    <td>@customer.CustomerID</td>
                    <td>@customer.ContactName</td>
                    <td>@customer.City</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    </form>
</body>
</html>
 
 

Screenshot

Using Stored Procedure in ASP.Net Core Razor Pages
 
 

Downloads

Download Code