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

I did that, didn't work. I did post what I have tried. Maybe tell me what I should change in there ? — user4857063
– user4857063, Commented Jul 22, 2017 at 14:21
What didn't work? Did it throw any exception? If so, please post the stack trace. If not, please explain what did not work. — Am_I_Helpful
– Am_I_Helpful, Commented Jul 22, 2017 at 14:22
What do you mean by it didn't work? Did you see any error? Unexpected output? — Chetan
– Chetan, Commented Jul 22, 2017 at 14:23
Error :Server Error in '/' Application. The resource cannot be found. Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly. Requested URL: /Cards/GetBalance — user4857063
– user4857063, Commented Jul 22, 2017 at 14:26
That's not sql server error. When do you get this error? Have you configured proper routes for that? — Chetan
– Chetan, Commented Jul 22, 2017 at 14:40

Rob · Accepted Answer · 2017-07-22 14:54:12Z

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.

answered Jul 22, 2017 at 14:54

Rob

45.9k24 gold badges126 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4857063 Over a year ago

Gosh, don't know how I missed that. This got rid of that error. Thanks :)

user4857063 Over a year ago

I am now getting this error: The data reader is incompatible with the specified 'GiftworxModel.Card'. A member of the type, 'CardID', does not have a corresponding column in the data reader with the same name. Any idea how to solve this

Rob Over a year ago

@Janine, that's an entirely separate question which will have a very different answer. On stackoverflow a "one question per question" model is followed, so please go ahead and ask another question with the issue you're now encountering, along with the minimum of code to recreate it =) Glad my answer helped with your first issue! =)

Collectives™ on Stack Overflow

Using stored procedure in ASP.NET MVC

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related