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
-------------------------------------------------------------------------------
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)
{
if (ModelState.IsValid)
{
db.MrwdUpgradeTranHists.Add(mrwdUpgradeTranHist);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(mrwdUpgradeTranHist);
}