Hi rani,
Using the below article i have created the example.
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
