title: MVC - Creating View that lists products from database using stored procedure that passes search parameter from textbox
body: <p>I hope the wording on the Title isn't confusing..I tried to be as specific as possible...</p>
  
  <p><strong>Goal</strong></p>
  
  <p>Create a View that allows a customer to search products using a textbox from the Index View on my SearchController. When the customer clicks the search button, it will redirect to the Search View that should take the string from the textbox, use it as the @param for my stored procedure, then return the Search View as a list (table).</p>
  
  <p><strong>Problem</strong></p>
  
  <p>I'm very new to MVC, and I can get something of the nature without using stored procedures, but the project I'm working on requires a stored procedure to be used.</p>
  
  <p>Also, currently the search button doesn't go to the Search View().</p>
  
  <p><strong>Code</strong></p>
  
  <p>My SearchController:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Entity;
  using System.Linq;
  using System.Threading.Tasks;
  using System.Net;
  using System.Web;
  using System.Web.Mvc;
  using StoreFront.Models;
  
  namespace StoreFront.Controllers
  {
      public class SearchController : Controller
      {
          private ProductContext Productdb = new ProductContext();
  
          public ActionResult Index()
          {
              return View();
          }
  
          public ActionResult Search(string search)
          {
             return View(Productdb.Products.Where(x =&gt; x.ProductName.Contains(search) &amp;&amp; x.IsPublished == true).ToList());
          }
      }
  }
  </code></pre>
  
  <p>Index() View:</p>
  
  <pre><code>@model IEnumerable&lt;StoreFront.Models.Products&gt;
  @{
      ViewBag.Title = "Search";
      Layout = "~/Views/Shared/_CustomerLayout.cshtml";
  }
  
  &lt;h2&gt;@ViewBag.Title&lt;/h2&gt;
  
  &lt;div class="panel"&gt;
      &lt;div class="panel-body"&gt;
          @using (Html.BeginForm("Index", "Search", FormMethod.Get))
          {
          &lt;div class="form-group"&gt;
              &lt;label&gt;Input product to search for: &lt;/label&gt;
              @Html.TextBox("search")
          &lt;/div&gt;
          &lt;div class="form-group"&gt;
              &lt;input id="SearchButton" type="submit" class="btn btn-primary" value="Search" /&gt;
          &lt;/div&gt;
          }
      &lt;/div&gt;
  &lt;/div&gt;
  </code></pre>
  
  <p>Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spSearchProducts] 
      @SearchText varchar(500)
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      SELECT ProductName,Price,ImageFile FROM Product WHERE ProductName = @SearchText AND IsPublished = 1
  
  END
  </code></pre>
  
  <p>I have created an spSearchProductsModel.edmx that has the stored procedure, whose function name is <code>GetProducts()</code> that returns a complex of <code>spSearchProductsModel_Result</code></p>
  
  <p>I have been stuck on this for a looong time, and I have tried browsing the extend of Youtube + earlier stack questions. Any help would be greatly appreciated.</p>
  
  <p>P.S. Let me know if you need me to post anymore code.</p>