Using SQL Server Stored Procedures and ASP.NET Core 6 MVC

Using ASP.NET Core 6 MVC and SQL Server stored procedures seems like such a simple thing now. However, it took me a while to find the answer when I first tried to figure out how to do this. 

For over a decade, I have used web forms, C# in code behind, and SqlDataSource with stored procedures. It was second nature. In transitioning from ASP.NET Web Forms to ASP.NET Core MVC,  I learned that ASP.NET Core and MVC provide a much easier way to use store procedures.

Here is one straightforward way to retrieve data from a Microsoft SQL Server Database stored procedure from a controller. This tutorial assumes you understand how to use Entity Framework to establish a database connection.

Create a Model for the Data Fields in the Stored Procedure

   public partial class ArticleSelect
    {
        [Key]
        public int Id { get; set; }
        public string ContributorId { get; set; } = null!;
        public string FirstName { get; set; } = null!;
        public string LastName { get; set; } = null!;
        public string Title { get; set; } = null!;
        public string MetaTitle { get; set; } = null!;
        
        [DataType(DataType.MultilineText)]
        public string MetaDescription { get; set; } = null!;

        [DataType(DataType.MultilineText)]
        public string Content { get; set; } = null!;
        public string? Image { get; set; }
        public string? CategoryId { get; set; }
        public string? CategoryName { get; set; }
        public string? InformationTypeId { get; set; }
        public string? TypeName { get; set; }
        public int? AspnetVersionId { get; set; }
        public string? VersionName { get; set; }
        public string? Keywords { get; set; }
        public DateTime DatePosted { get; set; }
        public DateTime DateUpdated { get; set; }
        public string? Approved { get; set; }
    }

Access the Stored Procedure in the Controller's Action Method

Create a LINQ Query based on an interpolated string representing the stored procedure query.

If your stored procedure takes a parameter, you can pass the parameter to the stored procedure as shown below with @title.

var results = await _context.ArticleSelect.FromSqlInterpolated($"exec dbo.ArticleSelectTitle @title={id}").ToListAsync()

var results = await _context.ArticleSelect.FromSqlInterpolated($"exec dbo.ArticleSelect").ToListAsync();

        // GET: Get Article by title
        public async Task<IActionResult> Article(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                var results = await _context.ArticleSelect.FromSqlInterpolated($"exec dbo.ArticleSelectTitle @title={id}").ToListAsync();

                if (results == null)
                {
                    return NotFound();
                }
                else
                {
                    TempData["whichresults"] = "article";
                    return View(results);
                }
            }
            else
            {
                TempData["whichresults"] = "noarticle";
                var results = await _context.ArticleSelect.FromSqlInterpolated($"exec dbo.ArticleSelect").ToListAsync();
                return View(results);
            }
        }

You can also use raw SQL queries this way. 

---