using stored procedure with Telerik UI for ASP.NET MVC autocomplete control

Hi experts,

I'm using Visual Studio 2013.
I have a ASP.NET mvc application using C# and razor syntax.
I'm using MVC 5 and Entity Framework 6 database first model.

For my example I'm using the Products table of the Northwind sql server database.

For my example I'm using the Telerik UI for ASP.NET MVC autocomplete control

AutoComplete demo
http://demos.telerik.com/aspnet-mvc/autocomplete/index

AutoComplete reference
http://docs.telerik.com/kendo-ui/aspnet-mvc/helpers/autocomplete/overview

In the AutoComplete reference link above it basically says that there are 2 ways to bind data to the autocomplete control.
One ways is to use server binding and the other way is to use ajax binding.

    server - the data will be serialized to the client. No Ajax requests will be made.
    ajax - the autocomplete will make ajax requests to get the data.

In my example I'm using server binding.

So my Visual Studio project directory looks like this.:
my directory.
I created and empty MVC 4 Application and then upgraded it to MVC 5 and Entity Framework 6.  
Then I added my model called Northwind.edmx

My model looks like this:

entity model for the Northwind Products table
If you noticed in my model I added the Products table as well as a Stored Procedure called GetProducts.

The code for the stored procedure called GetProducts looks like this:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetProducts] 
AS
SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
  FROM [Northwind].[dbo].[Products]

GO

Open in new window



Controller

So then my code for my controller called HomeController.cs looks like this:

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

namespace NorthwindApp.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {

            NorthwindEntities db = new NorthwindEntities();

            // pass list of Product entity objects to the view
            return View(db.Products);
        }

    }
}

Open in new window


View

The code for my view looks like this:

@model IEnumerable<NorthwindApp.Models.Product>

<link href="~/Content/web/kendo.common.min.css" rel="stylesheet" type="text/css" />
<link href="~/Content/web/kendo.default.min.css" rel="stylesheet" type="text/css" />
<link href="~/Content/shared/examples.css" rel="stylesheet" type="text/css" />
<script src="~/Scripts/jquery.min.js" type="text/javascript"></script>
<script src="~/Scripts/kendo.web.min.js" type="text/javascript"></script>
<script src="~/Scripts/kendo.aspnetmvc.min.js" type="text/javascript"></script>
<script src="~/Scripts/kendo.autocomplete.min.js" type="text/javascript"></script>


<div class="demo-section">
    <h2>Products</h2>

    @(Html.Kendo().AutoComplete()
          .Name("products") //The name of the autocomplete is mandatory. It specifies the "id" attribute of the widget.
          .DataTextField("ProductName") //Specifies which property of the Product to be used by the autocomplete.
          .BindTo(Model)   //Pass the list of Products to the autocomplete.
          .Filter("contains") //Define the type of the filter, which autocomplete will use.
    )    

</div>
<style scoped>
    .demo-section {
        width: 250px;
        margin: 35px auto 50px;
        padding: 30px;
    }

        .demo-section h2 {
            text-transform: uppercase;
            font-size: 1.2em;
            margin-bottom: 10px;
        }
</style>

Open in new window



So when I run my page my AutoComplete works just fine.
It looks like this:

when I run my page
The autocomplete is looking at the ProductName column of the Products table.

So it does a look up by what i type.

If you noticed in my example, in the controller I called the Products table from the entity model and passed that to the view.

So here is my question.

I want to revise my example to instead use the stored procedure called GetProducts for my autocomplete.

When I added the GetProducts stored procedure to Northwind.edmx I see that a function called GetProducts and a Complete Type called GetProducts_Result got created.  
I'm confused on the syntax to call the stored procedure from my entity model and then pass that to the view and bind it to the autocomplete.


Anyone know how I revise my controller and view code to use the GetProducts stored procedure?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
I, too, recently built an MVC 5 app using Entity framework and razor.  I'm not calling any SPs from the SQL db though.  I found this post http://stackoverflow.com/questions/26374592/mvc-5-entity-framework-6-execute-stored-procedure on how to call SP from MVC 5 Entity framework.  It provides a simple solution as well as a more robust extension method.  Give it a look.
OM Gang

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.