Try to search your question here, if you can't find : Ask Any Question Now ?

Link to a stored procedure result set from a .cshtml page in ASP.Net Razor Pages

HomeCategory: stackoverflowLink to a stored procedure result set from a .cshtml page in ASP.Net Razor Pages
john asked 1 week ago

I want to see movies of a certain media type (ie DVD, Bluray, VHS), so I have created stored procedure to do that. This stored procedure returns movies that have an ID associated with the media type. For instance, ID 1 returns DVDs. I have a page for my media type that lists the media (the razor pages automatically created the details page for me after scaffolding).

  1. I have the code for my stored procedure below:
using System;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Movies.Models
{
    public class MovieDataAccessLayer
    {
        string connectionString = "Server=ServerName;Database=DatabaseMovies;Trusted_Connection=True;MultipleActiveResultSets=true";

        public IEnumerable<Movies> MovieByMedia(byte? MediaID)
        {
            List<Movies> lstmovie = new List<Movies>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_MovieByMedia", con);
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Movies movies = new Movies();

                    movies.MovieTitle = rdr["MovieTitle"].ToString();
                    movies.MovieYear = Convert.ToInt32(rdr["MovieYear"]);

                    lstmovie.Add(movies);
                }

                con.Close();

            }
            return lstmovie;
        }

    }
}

  1. Here is my Media Details .cshtml.cs code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Movies.Models;

namespace Movies.Pages.MediaDetails
{
    public class DetailsModel : PageModel
    {
        private readonly Movies.Models.MoviesContext _context;

        public DetailsModel(Movies.Models.MoviesContext context)
        {
            _context = context;
        }

        public Media Media { get; set; }

        public async Task<IActionResult> OnGetAsync(byte? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            Media = await _context.Media.FirstOrDefaultAsync(m => m.MediaTypeID == id);


            if (Media == null)
            {
                return NotFound();
            }
            return Page();
        }
    }
}
  1. And here is my .cshtml page
@model Movies.Pages.MediaDetails.DetailsModel

@{
    ViewData["Title"] = "Details";
}

<h1>Details</h1>

<div>
    <h4>Media</h4>
    <hr />
    <dl class="row">
        <dt class="col-sm-2">
            @Html.DisplayNameFor(model => model.Media.MediaType)
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Media.MediaType)
        </dd>
    </dl>
</div>
<div>
    <a asp-page="./Edit" asp-route-id="@Model.Media.MediaTypeID">Edit</a> |
    <a asp-page="./Index">Back to List</a>
</div>
  1. Here is the code for my movie details .cshtml that I tried to connect to my Media .cshtml without success
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Movies.Models;

namespace Movies.Pages.MovieDetails
{
    public class DetailsModel : PageModel
    {
        private readonly Movies.Models.MoviesContext _context;

        public DetailsModel(Movies.Models.MoviesContext context)
        {
            _context = context;
        }

        MovieDataAccessLayer objmovie = new MovieDataAccessLayer();
        public Movies 
            Movies { get; set; }

        public async Task<IActionResult> OnGetAsync(byte id)
        {
            Movies = await _context.Movies.FirstOrDefaultAsync(m => m.MediaTypeID == id);

            if (Movies == null)
            {
                return NotFound();
            }
            return Page();
        }
    }
}

I would like to modify my details page that lists out my media types so that when I click “details” for the respective media type, I can generate the result of my stored procedure for the media type that I click on. For instance, when I click “DVD” my stored procedure is executed to show me only my DVDs. How do I do this? I know that I somehow have to reference to my movie data access layer to execute my SP. Any assistance will be greatly appreciated.

1 Answers
Best Answer
Jyoti answered 1 week ago
Your Answer

12 + 4 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com