How do I call a database 'select' stored procedure with parameters, from a MVC Controller?

Question

I currently have a controller which populates a partial view with all rows from a database view.

public ActionResult SearchResults()
{
    return PartialView("~/Views/TransactionHistory/_SearchResults.cshtml", db.TRANSACTION_HISTORY.ToList());
}

TRANSACTION_HISTORY is the name of the model class. and also the database view from which I am displaying the rows. db is my database context object.

This is my model class:

namespace TransactionHistory.Models
{
    using System;
    using System.Collections.Generic;

    public partial class TRANSACTION_HISTORY
    {
        public int INTERFACE_RECORD_ID { get; set; }
        public string COMPANY { get; set; }
        public string Status { get; set; }
        public string Carrier { get; set; }
        public string Service { get; set; }
        public string Connote { get; set; }
        public string Order_Type { get; set; }
        public Nullable<decimal> Volume { get; set; }
        public Nullable<decimal> Weight { get; set; }
        public string State { get; set; }
        public string Post_Code { get; set; }
        public string Suburb { get; set; }
        public string Zone { get; set; }
        public string Book_In { get; set; }
        public string Deliver_From { get; set; }
        public string Deliver_To { get; set; }
        public string Trpt_Special_Instructions { get; set; }
        public Nullable<System.DateTime> Date_Created { get; set; }
        public Nullable<System.DateTime> From_Date { get; set; }
        public Nullable<System.DateTime> To_Date { get; set; }
        public string Picklist { get; set; }
    }
}

Now, I have a stored procedure which operates on this view, named TRANSACTION_HISTORY_SEARCH. Now this accepts 2 parameters (FROM_DATE and TO_DATE) in it's where clause, and returns the exact same number of rows as the view (which means I do not want to use a different model for storing the rows returned by the stored procedure).

So how do I make use of the controller's ActionResult method to actually get the results returned by the stored procedure, rather than all rows returned by the database view?

I do understand that I need to use [HttpPost] for this action, since I'll be passing those parameters for the stored procedure from my view (textbox entries).

(1) You do not need a POST (it can be a GET since your not changing data). (2) Why not a simple a linq `.Where()` clause e.g. `db.TRANSACTION_HISTORY.Where(x => FROM_DATE >= fromDate && x => x.TO_DATE <= toDate);`? — , Jan 22 '17 at 22:37
Thank you. It's just that I am working on legacy databases, which already have stored procedures and my employers prefer that I use them. I have a meeting later on to try and convince them to allow me to use LINQ instead. So do I just pass this LINQ query as a parameter to the 'return PartialView' function? — doodles, Jan 22 '17 at 23:44
Yes, (where `fromDate` and `toDate` are parameters in your method (the values of the textboxes). But the fact that your returning `PartialView` also suggest your using ajax. But if you want to do this using a SP, refer [this answer](http://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first) for an example — , Jan 22 '17 at 23:52
I tried your LINQ expression, but there is an error for FROM_DATE and TO_DATE, which says these names do not exist in the current context. Do I have to pass the model as a parameer to my SearchResults() function? Looks to be that way. Thanks in advance! — doodles, Jan 23 '17 at 01:29
I have no idea what you model/table fields are (you have not shown them!) Just replace then with the actual property names — , Jan 23 '17 at 01:31
@StephenMuecke Yes, the names are the same (I have also used the same case). I just updated my question with the code of my model. The model was generated directly from a database view which already existed. — doodles, Jan 23 '17 at 03:46
Got it resolved by reformatting the LINQ query in a different way. But my employers insist on using stored procedures, so I am digging into the link your provided. Thanks anyway! — doodles, Jan 23 '17 at 06:01

score 0 · Accepted Answer · answered Jan 23 '17 at 17:40

You can use the Database property and call the stored procedure using SqlQuery.
Something like this:

 List<TRANSACTION_HISTORY> res;

 res = db.TRANSACTION_HISTORY
         .Database
         .SqlQuery<TRANSACTION_HISTORY>("TRANSACTION_HISTORY_SEARCH @FROM_DATE, @TO_DATE",
                                        new SqlParameter("@FROM_DATE", fromDate),
                                        new SqlParameter("@TO_DATE",   toDate))
         .ToList();

How do I call a database 'select' stored procedure with parameters, from a MVC Controller?

1 Answers1