Call Stored Procedure using Entity Framework in ASP.Net Core Razor Page

rani
 
on Oct 16, 2020 11:24 PM
Sample_177545.zip
2418 Views

How to call stored procedure in asp.net core razor page

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 17, 2020 04:45 AM

Hi rani,

Using the below article i have created the example.

jQuery AutoComplete in ASP.Net Core Razor Pages

You can execute Stored Procedure using FromSql method in Entity Framework Core.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

--[dbo].[Customer_GetCustomerDetail]  'm'
CREATE PROCEDURE [dbo].[Customer_GetCustomerDetail]  
   @SearchText nvarchar(30)  
AS   
BEGIN  
SET NOCOUNT ON  
	SELECT   
		CustomerID   
		,ContactName  
		,City  
		,Country   
	FROM   Customers   
	WHERE  ContactName like @SearchText+'%'
END

Model

public class Customer
{
    public string CustomerID { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

Namespaces

using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Linq;

Controller

public class IndexModel : PageModel
{
    public string Message { get; set; }

    private DBCtx Context { get; }
    public IndexModel(DBCtx _context)
    {
        this.Context = _context;
    }

    public List<Customer> Customers { get; set; }

    public IActionResult OnPostAutoComplete(string prefix)
    {
        string procedureName = "dbo.Customer_GetCustomerDetail @SearchText";
        SqlParameter sqlParameter = new SqlParameter("@SearchText", prefix);
        var customers = Context.Customers.FromSql(procedureName, sqlParameter).ToList().
            Select(x => new
            {
                label = x.ContactName,
                val = x.CustomerID
            });

        return new JsonResult(customers);
    }

    public void OnPostSubmit()
    {
        this.Message = "CustomerName: " + Request.Form["CustomerName"] + " CustomerId: " + Request.Form["CustomerId"];
    }
}

View

@page
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model Procedure_Parameter_Core_Razor.Pages.IndexModel

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post">
        @Html.AntiForgeryToken()
        <input type="text" id="txtCustomer" name="CustomerName" />
        <input type="hidden" id="hfCustomer" name="CustomerId" />
        <br /><br />
        <input type="submit" value="Submit" asp-page-handler="Submit" />
        <br /><br />
        @Model.Message
    </form>

    <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>
    <script src="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
    <link href="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"
          rel="Stylesheet" type="text/css" />
    <script type="text/javascript">
        $(function () {
            $("#txtCustomer").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '/Index?handler=AutoComplete',
                        beforeSend: function (xhr) {
                            xhr.setRequestHeader("XSRF-TOKEN",
                                $('input:hidden[name="__RequestVerificationToken"]').val());
                        },
                        data: { "prefix": request.term },
                        type: "POST",
                        success: function (data) {
                            response($.map(data, function (item) {
                                return item;
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("#hfCustomer").val(i.item.val);
                },
                minLength: 1
            });
        });
    </script>
</body>
</html>

Screenshot