实体框架MVC 5和存储过程没有返回数据 [英] Entity framework MVC 5 and stored procedures without return data
问题描述
我是MVC和实体框架的新手。我在SQL Server 2012中有以下存储过程：
------------------------------- ------------------------------------------------ <无线电通信/>
I am new to MVC and Entity Framework. I have the following stored procedure in SQL Server 2012:
-------------------------------------------------------------------------------
ALTER PROCEDURE [Elite].[AppInsertMrwdUpgrade]
(
@UpgradeGrpID INT
,@MrwdID VARCHAR(9)
,@EID VARCHAR(10)
,@ResultMsg VARCHAR(255) = NULL OUTPUT
)
AS
SET NOCOUNT ON;
SELECT DISTINCT TOP 1
@ResultMsg = CONCAT('Marriott Rewards Number : ' ,@MrwdID, ' has already been succesfully upgraded to ', IIF(A.UpgradeLvlCd = 'P', 'Platinum' , 'Gold') , '.')
FROM Elite.MrwdUpgradeTranHist AS A
JOIN Elite.UpgradeGrpRef AS B
ON A.UpgradeGrpID = B.UpgradeGrpID
WHERE A.MrwdID = @MrwdID
AND A.ResponseDt IS NOT NULL
AND A.ResponseCd IN ('NO UPDATE LEVEL - LEV, LEV TYP & DT SAME','UPGRADE LEVEL LOWER THAN CURRENT LEVEL','NNIGN')
AND A.UpgradeLvlCd >= B.MaxLvl
ORDER BY 1 DESC
;
IF @ResultMsg IS NOT NULL
BEGIN
RETURN
END
;
DECLARE @Lvl CHAR(1)
SELECT
@Lvl = R.MaxLvl
FROM Elite.UpgradeGrpRef AS R
WHERE R.UpgradeGrpID = @UpgradeGrpID
;
INSERT INTO Elite.MrwdUpgradeTranHist
(
UpgradeGrpID
, MrwdID
, UpgradeLvlCd
, InputDt
, InputSource
, UserID
)
VALUES
(
@UpgradeGrpID
,@MrwdID
,@Lvl
,GETDATE()
,'Manual_Assoc'
,@EID
)
;
SET @ResultMsg = CONCAT('Success! Marriott Rewards Number : ' ,@MrwdID, ' has been submitted for upgrade to ', IIF(@Lvl = 'P', 'Platinum' , 'Gold') , '.')
;
RETURN
---- -------------------------------------------------- -------------------------
我希望能够使用MVC 5和Entity Framework来调用这个存储过程如果记录不重复，则插入记录。我将使用GroupID，MrwdID和EID进行文本框输入，并使用提交按钮激活存储过程的调用。我希望收到@ResultMsg并能够在成功时清除表单，或者将信息保存在文本框中以便用弹出消息编辑给用户。 （我意识到其中一些可能是不好的做法。我们只在使用IIS的Intranet上使用它）。我希望通过我提供的解决方案，找到解决方案。
我的尝试：
我已经能够首先创建一个数据实体模型作为数据库，该模型已经识别出没有返回集合的存储过程和函数。从那里当我尝试创建一个带有视图的控制器时，错误表明没有对象。
我尝试过关联的表，它确实创建了视图。我使用了Create View并将其修改如下：
------------------------------- ------------------------------------------------ <无线电通信/>
-------------------------------------------------------------------------------
I want to be able to use MVC 5 and Entity Framework to call this stored procedure to insert a record if it is not a duplicate. I will use GroupID, MrwdID, and EID for textbox input with a submit button to activate the call of the stored procedure. I would expect to receive back the @ResultMsg and be able to either clear the form on success or keep the information in the text boxes for editing with a pop-up message to the user. (I realize some of this may be bad practices. We are using this on our Intranet only using IIS). I hope with what I have provided, a solution can be found.
What I have tried:
I have been able to create as Database first a Data Entity model which has identified both the stored procedure and function identified without a return collection. From there when I try to create a controller with view it errors saying there isn't an object.
I have tried with the associated table and it did create views. I used the Create View and modified it as follows:
-------------------------------------------------------------------------------
@model EliteMrwd.Models.MrwdUpgradeTranHist
@{
ViewBag.Title = "Create";
}
<h2>Submit Upgrade</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<hr>
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.Label("Group ID: ", htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
<input type="text" id="UpgradeGrpID" name="UpgradeGrpID" value= @ViewBag.idName />
@Html.ValidationMessageFor(model => model.UpgradeGrpID, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.Label("Marriott Rewards #: ", htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.MrwdID, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.MrwdID, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.Label("Submitting Person: ", htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => @User.Identity.Name.Split('\\')[1], new { htmlAttributes = new {@class = "form-control", @disabled="true" } })
@Html.ValidationMessageFor(model => model.UserID, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
</div>
</div>
</div>
}
@*<div>
@Html.ActionLink("Back to List", "Index")
</div>*@
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
---- ------------------------------------- -------------------------------------
我的控制器有以下代码部分：
------------------------------ ------------------------------------------------ <无线电通信/>
------------------------------------------------------------------------------
My controller has the following sections of code:
------------------------------------------------------------------------------
public ActionResult Create(string id)
{
if (id == null)
{
ViewBag.idName = "";
}
else
{
ViewBag.idName = id;
}
return View();
}
and
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "TranID,UpgradeGrpID,MrwdID,UpgradeLvlCd,InputDt,InputSource,UserID,ExportDt,ExportCd,ResponseDt,ResponseCd")] MrwdUpgradeTranHist mrwdUpgradeTranHist)
{
//[Bind(Include = "TranID,UpgradeGrpID,MrwdID,UpgradeLvlCd,InputDt,InputSource,UserID,ExportDt,ExportCd,ResponseDt,ResponseCd"
if (ModelState.IsValid)
{
db.MrwdUpgradeTranHists.Add(mrwdUpgradeTranHist);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(mrwdUpgradeTranHist);
}
推荐答案
对不起，我收到了一个注释掉的重复绑定声明。
I'm sorry I included a commented out duplicate bind statement.
我的解决方案是将存储过程更改为没有输出变量。相反，它现在有一个select语句将数据推送给我。然后我将数据传递给 results.cshtml 页面。如果有任何建议，我仍然愿意学习能够通过输出参数传递。
My solution was to change the stored procedure to not have an output variable. Instead it now has a select statement to push the data to me. I then pass the data to a results.cshtml page. I am still open to learning being able to pass via an output parameter if there are any suggestions.
这篇关于实体框架MVC 5和存储过程没有返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
