Using stored procedure in ASP.NET MVC

Question

I am quite new to using stored procedures in my mvc application so will appreciate any guidance regarding the question below.

The Purpose: I have a stored procedure that returns the balance amount of a gift card. Basically when the user inputs a card number, it returns all relevant details for that card. Thing is I dont know how to go about accomplishing this on my MVC application.

What I tried so far: The context class:

public virtual ObjectResult<string> GetBalance(string cardNumber)
    {
        var cardNumberParameter = cardNumber != null ?
            new ObjectParameter("CardNumber", cardNumber) :
            new ObjectParameter("CardNumber", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("GetBalance", cardNumberParameter);
    }

The controller:

    [HttpPost]
    public ActionResult GetBalance(string cardNumber)
    {

        var balance = db.GetBalance(cardNumber).ToList();

        return View("GetBalance");

    }

The View:

@using (Html.BeginForm("GetBalance", "Cards", FormMethod.Post))
                        {
                            @Html.AntiForgeryToken()
                            <div class="panel-body">
                                <div class="row">
                                    <div class="col-lg-12">
                                        <form role="form">
                                            @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                                           
                                               
                                           
                                            <div class="form-group">
                                                Number of Cards: <input type="text" name="cardNumber"/>
                                            </div>
                                            

                                            <input type="submit" value="Create" class="btn btn-default" style="background-color: #0a9dbd; color: white;" />
                                            <button type="reset" class="btn btn-default" style="background-color: #0a9dbd; color: white;">Reset</button>

                                            <div>
                                                @Html.ActionLink("Back to List", "Index")
                                            </div>
                                        </form>
                                    </div>

                                </div>
                            </div>
                        }

The stored procedure

    GO

ALTER PROCEDURE  [dbo].[GetBalance]
-- Add the parameters for the stored procedure here
@CardNumber nvarchar(20)
AS
BEGIN

SET NOCOUNT ON;
If Not Exists(Select CardID From Cards With (NOLOCK) Where CardNumber = @CardNumber OR (Cards.CardNumber2 = @CardNumber))
Begin
Select 'Invalid Card Number' as Error
END

Select Sum(TotalAmount) as TotalAmount, StoreCustomerName, StoreCustomerISurname, StoreCustomerEmail, StoreCustomerDOB, StoreCustomerAddress, StoreCustomerCity, 
                         StoreCustomerRegion, StoreCustomerCountry, StoreCustomerCel, StoreCustomerDate, StoreCustomerToken, '' as Error, StoreCustomerID, CardNumber, CardStatus,
                         LoyaltyLevelName, LoyaltyLevel, DiscountLevelAmount, DiscountLevelName, GiftCardEnabled, LoyaltyEnabled, StoreCustomerNumber From (
SELECT        0 AS TotalAmount, StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
FROM            StoreCustomers WITH (NOLOCK) INNER JOIN
                         Cards WITH (NOLOCK) ON StoreCustomers.StoreCustomerID = Cards.StoreCustomerID INNER JOIN
                         DiscountLevels WITH (NOLOCK) ON Cards.DiscountLevelID = DiscountLevels.DiscountLevelID AND Cards.CustomerID = DiscountLevels.CustomerID  INNER JOIN 
                         LoyaltyLevels WITH (NOLOCK) ON Cards.LoyaltyLevelID = LoyaltyLevels.LoyaltyLevelID AND Cards.CustomerID = LoyaltyLevels.CustomerID
WHERE         (Cards.CardNumber = @CardNumber) OR (Cards.CardNumber2 = @CardNumber) --WHERE        (Cards.CardID = @CardID)
GROUP BY StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName,Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
Union ALL
SELECT        SUM(Sales.SaleTotalAmount) AS TotalAmount, StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress,
                         StoreCustomers.StoreCustomerCity, StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, 
                         StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, 
                         DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
FROM            StoreCustomers WITH (NOLOCK) INNER JOIN
                         Cards WITH (NOLOCK) ON StoreCustomers.StoreCustomerID = Cards.StoreCustomerID INNER JOIN
                         Sales WITH (NOLOCK) ON Cards.CardID = Sales.CardID INNER JOIN
                         LoyaltyLevels WITH (NOLOCK) ON Cards.LoyaltyLevelID = LoyaltyLevels.LoyaltyLevelID AND Cards.CustomerID = LoyaltyLevels.CustomerID  INNER JOIN
                         DiscountLevels WITH (NOLOCK) ON Cards.DiscountLevelID = DiscountLevels.DiscountLevelID AND Cards.CustomerID = DiscountLevels.CustomerID
WHERE         (Cards.CardNumber = @CardNumber) OR (Cards.CardNumber2 = @CardNumber)--WHERE        (Cards.CardID = @CardID)
GROUP BY StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber

) as x
GROUP BY StoreCustomerName, StoreCustomerISurname, StoreCustomerEmail, StoreCustomerDOB, StoreCustomerAddress, StoreCustomerCity, StoreCustomerRegion, StoreCustomerCountry, StoreCustomerCel, StoreCustomerDate, StoreCustomerToken, StoreCustomerID, 
CardNumber, CardStatus, LoyaltyLevelName, LoyaltyLevel, DiscountLevelAmount, DiscountLevelName, GiftCardEnabled, LoyaltyEnabled, StoreCustomerNumber

END

Answer 1

The only method that you've shown in your controller for GetBalance is decorated with the HttpPost attribute, meaning that it will only respond to requests that are made via POST, i.e. ones that are made by submitting the form that you've shown in the GetBalance view, that you can't get to at the moment.

One thing you can do is provide two separate controller methods:

public ActionResult GetBalance()
{
    return View();
}

[HttpPost]
public ActionResult GetBalance(string cardNumber)
{
    // Do stuff to retrieve the balance here
    return View("GetBalance");
}

Note the fact that the first GetBalance method has:

• No attribute decorating it, so it responds to GET requests (like the one from the link/button on your /Index page)
• Doesn't explicitly name the view that it wants to return - this is because the name of the view is implied by the name of the Action, allowing for marginally cleaner code to be written.