asp.net-mvcstored-proceduresasp.net-mvc-controller

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


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).


Solution

  • 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();