MVC - Database First - Stored Procedure example - How to populate DropDownList

Watch this example on YouTube


 
1. SQL - Orders Table

USE [Company]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 2017-10-21 8:39:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] NULL,
    [Qty] [int] NULL,
    [ProductID] [int] NULL
) ON [PRIMARY]

GO

2. SQL - Product Table

USE [Company]
GO

/****** Object:  Table [dbo].[Product]    Script Date: 2017-10-21 8:40:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

3. Stored Procedures

CREATE PROCEDURE SelectProducts
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Product
END
GO

CREATE PROCEDURE SelectOrders
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Orders
END
GO

4. Model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MVCDropDownListDBFirst.Models;

namespace MVCDropDownListDBFirst.Models
{
    public class OrderClass
    {
        public SelectOrders_Result or { get; set; }
        public IEnumerable<SelectProducts_Result> pr { get; set; }
    }
}

5. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVCDropDownListDBFirst.Models;

namespace MVCDropDownListDBFirst.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();

        public ActionResult Test()
        {
            OrderClass model = new OrderClass();
            model.or = db.SelectOrders().FirstOrDefault();
            model.pr = db.SelectProducts();
            return View(model);
        }


6. View

@model MVCDropDownListDBFirst.Models.OrderClass
@{
    ViewBag.Title = "Test";
}

<h2>Test</h2>

@Html.DropDownListFor(x=>x.or.ProductID, new SelectList(Model.pr, "ProductID", "Name"))