Call Stored Procedure with parameters using Entity Framework in ASP.Net Core MVC

Richa

SOLVED CLOSED ACTIVE ANSWERED
User: Richa
Posted: on Oct 16, 2020 04:44 PM
Forum: ASP.Net MVC
Answer: 2
Views: 15713
Sample Code: Download

How to call Stored Procedure and pass parameters in Asp.Net core MVC

I am working on Asp.Net Core MVC 3.1 application and I am very new to it. I am using Repository Pattern approach in it.

I have to call Stored Procedure and have to pass 4 parameters (from ViewModel) to that SP. How ?? I am using Repository Pattern approach.

Thank you in advance

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Oct 17, 2020 04:42 AM
Report

Hi Richa,

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.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;

Controller

public class HomeController : Controller
{
    private DBCtx Context { get; }
    public HomeController(DBCtx _context)
    {
        this.Context = _context;
    }

    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public JsonResult AutoComplete(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 Json(customers);
    }

    [HttpPost]
    public ActionResult Index(Customer customer)
    {
        ViewBag.Message = "CustomerName: " + customer.ContactName + " CustomerId: " + customer.CustomerID;
        return View();
    }
}

View

@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model Procedure_Parameter_Core_MVC.Models.Customer
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <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: '/Home/AutoComplete/',
                        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) {
                    $("#hfId").val(i.item.val);
                    $("#hfName").val(i.item.label);
                },
                minLength: 1
            });
        });
    </script>
    <form asp-action="Index" asp-controller="Home" method="post">
        <input type="text" id="txtCustomer" name="CustomerName" />
        <input type="hidden" id="hfId" asp-for="CustomerID" />
        <input type="hidden" id="hfName" asp-for="ContactName" />
        <br /><br />
        <input type="submit" id="btnSubmit" value="Submit" />
        <br /><br />
        @ViewBag.Message
    </form>
</body>
</html>

Screenshot

Richa

0 4

Reply
ANSWER
User: Richa
Replied: on Oct 19, 2020 03:39 AM
Report

Modified my code by taking reference to your suggestion because I have nothing to return from SP.

Repository class:

public void ExecuteWithoutReturn() //7 params are passed here
{
    using (MySqlConnection con = new MySqlConnection(ConnectionString))
    {
        using (MySqlCommand cmd = new MySqlCommand(procedureName, con))
        {
            con.Open();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@studyNameSource", _studyNameSource);
            cmd.Parameters.AddWithValue("@scenarioNameSource", _scenarioNameSource);
            cmd.Parameters.AddWithValue("@studyNameDestiny", _studyNameDestiny);
            cmd.Parameters.AddWithValue("@scenarioNameDestiny", _scenarioNameDestiny);
            cmd.Parameters.AddWithValue("@projectNameDestinyArray", _projectNameDestinyArray);
            cmd.Parameters.AddWithValue("@mappingType", _mappingType);
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}

Controller Method:

[HttpPost]
public ActionResult ExecuteDuplicateMapping(ViewModel dmViewMode)
{
    try
    {                
         string procedureName = "DuplicateMapping";
         _repository.ExecuteWithoutReturn(procedureName, dmViewMode.SourceStudyName, dmViewMode.SourceScenarioName, dmViewMode.TargetStudyName, dmViewMode.TargetScenarioName, dmViewModel.newPrj, dmViewModel.mappingType);

         return Json(new { success = true, responseText = "Executed successfuly!" });
     }
     catch (Exception ex)
     {
         throw ex;
     }
}

Index View:

        function DuplicateMapping(sourceStudy, sourceScenario, targetStudy, targetScenario, selectedT) {
            var actionUrl = "@Url.Action("ExecuteDuplicateMapping", "Map")";
            $.ajax({
                type: 'POST',
                url: actionUrl,
                dataType: 'json', 
                data: {
                    SourceStudyName: sourceStudy,
                    SourceScenarioName: sourceScenario,
                    TargetStudyName: targetStudy,
                    TargetScenarioName: targetScenario,
                    TargetProjectName: selectedT
                },
                success: function (response) {
                    if (response.success) {
                        alert(response.responseText);
                    }
                },
                error: function (xhr, ajaxOptions, thrownError) {
                    alert("Error!");
                }
            });
        }

thank you so much

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Call Stored Procedure with parameters using Entity Framework in ASP.Net Core MVC

jQuery AutoComplete TextBox in ASP.Net Core MVC

Company

Explore

Follow us

Contact Us