实体框架MVC 5和存储过程没有返回数据

本文介绍了实体框架MVC 5和存储过程没有返回数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是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.