在 C# 中使用存储过程并 ASP.NET MVC

Question

我有一个使用 EF 模型(数据库优先方法(的 ASP.NET MVC 应用程序。我在 SQL Server 中定义了需要在应用程序中使用的存储过程。我以前从未这样做过，任何有关此的指导将不胜感激。我确实添加了存储过程并为其创建了函数，但不知道如何将其添加到我的控制器和视图中。对于下面提供的代码，我只想添加一张卡。

存储过程：

CREATE PROCEDURE  [dbo].[CreateLoyaltyCards]
@NumberOfCards AS integer, 
@CustomerID  AS integer 
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NumberOfRows as integer = 0;
DECLARE @LoopNumber as integer = 0
WHILE @LoopNumber < @NumberOfCards
BEGIN
INSERT INTO Cards (CustomerID, CardDate)
VALUES (@CustomerID, GetDate())
SET @LoopNumber = @LoopNumber + 1
END
SELECT        
Customers.CustomerCompanyName, Cards.CardNumber
FROM            
Customers 
INNER JOIN
Cards ON Customers.CustomerID = Cards.CustomerID
WHERE 
Customers.CustomerID = @CustomerID
ORDER BY 
CardID ASC
OFFSET @NumberOfRows ROWS
FETCH NEXT @NumberOfCards ROWS ONLY
END

卡控制器//这是我需要使用存储过程的地方

public ActionResult Create()
{
ViewBag.CustomerID = new SelectList(db.Customers, "CustomerID", "CustomerCompanyName");
return View();
}
// POST: Cards/Create
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "CardID,CardNumber,CustomerID,StoreCustomerID,CardDate,CardStatus,CardNumber2,DiscountLevelID,LoyaltyLevelID,GiftCardEnabled,LoyaltyEnabled")] Card card)
{
if (ModelState.IsValid)
{
db.Cards.Add(card);
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.CustomerID = new SelectList(db.Customers, "CustomerID", "CustomerCompanyName", card.CustomerID);
return View(card);
}

这是上下文类中的存储过程

public virtual ObjectResult<Card> CreateCards(Nullable<int> numberOfCards, Nullable<int> customerID)
{
var numberOfCardsParameter = numberOfCards.HasValue ?
new ObjectParameter("NumberOfCards", numberOfCards) :
new ObjectParameter("NumberOfCards", typeof(int));
var customerIDParameter = customerID.HasValue ?
new ObjectParameter("CustomerID", customerID) :
new ObjectParameter("CustomerID", typeof(int));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Card>("CreateCards", numberOfCardsParameter, customerIDParameter);
}

Answer 1

你只需要创建EF类的对象并直接调用函数(从SP创建(。

例如，假设上下文是你的对象。

context.CreateCards(numberOfCards, customerID);