Pass (Send) Array (List) parameters to Stored Procedure in ASP.Net Core MVC

rani
 
on Oct 21, 2020 10:54 AM
Sample_936288.zip
2376 Views

How to pass array of parameter to stored procedure in asp.net core mvc

Example i have to pass 15 parameter to stored procedure. So what is the best way.

Return Output parameter from Stored Procedure using Entity Framework in ASP.Net Core MVC

I have to pass each parameter with comma or any other way i can pass an array of parameter to it.

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

Hi rani,

The Context.Database.ExecuteSqlCommand accepts as IEnumerable<object>. So you need to create a Collection of parameters and pass to the method.

Check this example. Now please take its reference and correct your code.

Model

public class Fruit
{
    public string FruitId { get; set; }
    public string FruitName { get; set; }
}

Controller

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

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

    [HttpPost]
    public JsonResult GetFruitName(string id)
    {
        string procedureName = "dbo.GetFruitName @FruitId, @FruitName OUT";
        SqlParameter parm1 = new SqlParameter("@FruitId", id);
        SqlParameter parm2 = new SqlParameter
        {
            ParameterName = "@FruitName",
            DbType = DbType.String,
            Size = 30,
            Direction = ParameterDirection.Output
        };
        List<SqlParameter> sqlParameters = new List<SqlParameter>();
        sqlParameters.Add(parm1);
        sqlParameters.Add(parm2);

        var fruit = Context.Database.ExecuteSqlCommand(procedureName, sqlParameters);            
        string name = Convert.ToString(parm2.Value);
        return Json(name);
    }

    [HttpPost]
    public ActionResult Index(Fruit fruit)
    {
        if (!string.IsNullOrEmpty(fruit.FruitName))
        {
            ViewBag.Message = "Name: " + fruit.FruitName + " Id: " + fruit.FruitId;
        }
        return View();
    }
}

View

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

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $("#txtId").on('keyup', function () {
                if ($(this).val() != '') {
                    $('#hfId').val($(this).val());
                    $.ajax({
                        url: '/Home/GetFruitName/',
                        data: { "id": $(this).val() },
                        type: "POST",
                        success: function (data) {
                            $('#hfName').val(data);
                            $('#lblName').html(data);
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                }
            });
        });
    </script>
    <form asp-action="Index" asp-controller="Home" method="post">
        Id: <input type="text" id="txtId" name="fruitId" />
        <br />
        Name: <span type="text" id="lblName" name="fruitId"></span>
        <input type="hidden" id="hfId" asp-for="FruitId" />
        <input type="hidden" id="hfName" asp-for="FruitName" />
        <br /><br />
        <input type="submit" id="btnSubmit" value="Submit" />
        <br /><br />
        @ViewBag.Message
    </form>
</body>
</html>

Screenshot