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