Hi kas12345,
Check this example. Now please take its reference and correct your code.
Database
I have made use of the following table Customers with the schema as follows.
I have already inserted few records in the table.
You can download the database table SQL by clicking the download link below.
Download SQL file
Refer below article to use Stored Procedure in .Net Core.
Procedure
CREATE PROCEDURE [dbo].[Customers_SearchCustomers]
@Name NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 *
FROM Customers
WHERE Name LIKE '%' + @Name + '%'
END
GO
CREATE PROCEDURE [dbo].[Customers_InsertCustomer]
@CustomerId INT = NULL
,@Name VARCHAR(30)
,@Country VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM Customers WHERE CustomerID = @CustomerId)
BEGIN
UPDATE Customers
SET Name = @Name , Country = @Country
WHERE CustomerID = @CustomerId
END
ELSE
BEGIN
INSERT INTO Customers
VALUES(@Name,@Country)
END
END
GO
CREATE PROCEDURE [dbo].[Customers_DeleteCustomer]
@CustomerId INT
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Customers
WHERE CustomerId = @CustomerId
END
Model
public class CustomerModel
{
[System.ComponentModel.DataAnnotations.Key]
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Namespaces
using Microsoft.EntityFrameworkCore;
DBContext class
public class DBCtx : DbContext
{
public DBCtx(DbContextOptions<DBCtx> options) : base(options)
{
}
public DbSet<CustomerModel> Customers { get; set; }
public IQueryable<CustomerModel> SearchCustomers(string name)
{
SqlParameter pName = new SqlParameter("@Name", name);
return this.Customers.FromSql("EXECUTE Customers_SearchCustomers @Name", pName);
}
public void InsertCustomer(CustomerModel customer)
{
SqlParameter pId = new SqlParameter("@CustomerId", customer.CustomerId);
SqlParameter pName = new SqlParameter("@Name", customer.Name);
SqlParameter pCountry = new SqlParameter("@Country", customer.Country);
this.Database.ExecuteSqlCommand("EXECUTE Customers_InsertCustomer @CustomerId,@Name,@Country", pId, pName, pCountry);
}
public void DeleteCustomer(int id)
{
SqlParameter pId = new SqlParameter("@CustomerId", id);
this.Database.ExecuteSqlCommand("EXECUTE Customers_DeleteCustomer @CustomerId", pId);
}
}
Controller
public class HomeController : Controller
{
private DBCtx Context { get; }
public HomeController(DBCtx _context)
{
this.Context = _context;
}
public IActionResult Index()
{
List<CustomerModel> customers = this.Context.SearchCustomers("").ToList();
return View(customers);
}
[HttpPost]
public IActionResult Index(string name)
{
List<CustomerModel> customers = this.Context.SearchCustomers(!string.IsNullOrEmpty(name) ? name : "").ToList();
return View(customers);
}
[HttpPost]
public IActionResult Insert(string name, string country)
{
CustomerModel model = new CustomerModel()
{
Name = name,
Country = country
};
this.Context.InsertCustomer(model);
return RedirectToAction("Index");
}
public IActionResult Delete(int id)
{
this.Context.DeleteCustomer(id);
return RedirectToAction("Index");
}
}
View
@using StoredProcedure_CRUD_MVC_Core.Models;
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model List<CustomerModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<form method="post" asp-controller="Home">
<table>
<tr>
<td>Name</td>
<td><input type="text" name="Name" /></td>
</tr>
<tr>
<td>Country</td>
<td><input type="text" name="Country" /></td>
</tr>
<tr>
<td><input type="submit" value="Add" asp-action="Insert" /></td>
<td><input type="submit" value="Search" asp-action="Index" /></td>
</tr>
</table>
<br />
<br />
<table cellpadding="0" cellspacing="0">
<tr>
<th>Id</th>
<th>Name</th>
<th>Country</th>
<th></th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.Name</td>
<td>@customer.Country</td>
<td>@Html.ActionLink("Delete", "Delete", "Home", new { id = customer.CustomerId })</td>
</tr>
}
</table>
</form>
</body>
</html>
Screenshot
