Run a SQL Server stored procedure directly from controller

Question

I have this stored procedure in SQL Server:

CREATE PROCEDURE current_call
    @call_id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        dbo.Call.call_id, dbo.Call.city, dbo.Call.call_received, 
        dbo.Call.call_transfer, dbo.Call.call_response, dbo.Call.call_depart, 
        dbo.Call.call_arrive, dbo.Call.call_return, dbo.Call.call_end, 
        dbo.Priority.name AS priorityName, dbo.Call_Status.name as Call_Status, 
        dbo.Station.station_name, dbo.City.city_name, dbo.Protocol.name AS protocolName
    FROM     
        dbo.City 
    INNER JOIN
        dbo.Call 
    INNER JOIN 
        dbo.Priority ON dbo.Call.priority = dbo.Priority.priority_id 
        ON dbo.City.city_id = dbo.Call.city 
    INNER JOIN
        dbo.Protocol ON dbo.Call.protocol_category = dbo.Protocol.id 
    LEFT OUTER JOIN
        dbo.Station 
    INNER JOIN
        dbo.Ambulance ON dbo.Station.station_id = dbo.Ambulance.ambulance_station 
        ON dbo.Call.amb_id = dbo.Ambulance.ambulance_id
    INNER JOIN
        dbo.Call_Status ON dbo.Call.call_status = dbo.Call_Status.call_status_id
    WHERE 
        dbo.Call.call_id = @call_id;
END

I'm trying to call it through a controller and print the retrieved fields as a json string with no luck!

This is my controller:

public System.Web.Http.Results.JsonResult<String> GetTwo(int c_id)
{
    using (EMSMVCEntities entities = new EMSMVCEntities())
    {
        entities.Configuration.ProxyCreationEnabled = false;

        var query = entities.Database.SqlQuery<ObjectResult>("exec [dbo].[current_call] @call_id", new SqlParameter("call_id", c_id)).ToList();

        return Json (query.ToString());
    }
}

However, I get the following error:

System.InvalidOperationException: 'The result type 'System.Data.Entity.Core.Objects.ObjectResult' may not be abstract and must include a default constructor.'

I also tried the following code and the result was "-1"

var query = entities.Database.ExecuteSqlCommand("exec [dbo].[current_call] @call_id", new SqlParameter("call_id", c_id)).toString();

Answer 1

SqlQuery does not work with dynamic types:

if you want to use the dynamic type or anonymous type as its [SqlQuery] return type, you will probably get your code compiled but receive exceptions during runtime.

Also note that, you can't just call ToString() to convert your List<object> to json, you need to serialize them.

Note: In this answer I am going to use Newtonsoft.Json for serialization, however there are other options.

---

I recommend creating a model for your SP result, this way you can project SqlQuery's result into your model... defining a model also has the benefit of using [JsonProperty] attribute, which is helpful for mapping your properties to a different name when serializing them to json.

public class CurrentCall
{
    // [JsonProperty(PropertyName = "Caller_Id")] <-- json property name (if different)
    public long call_id { get; set }
    public string city { get; set }
    public DateTime call_received { get; set }
    public string call_transfer { get; set }
    public string call_response { get; set }
    public DateTime call_depart { get; set }
    public DateTime call_arrive { get; set }
    public DateTime call_return { get; set }
    public DateTime call_end { get; set }
    public string priorityName { get; set }
    public bool Call_Status { get; set }
    public string station_name { get; set }
    public string city_name { get; set }
    public string protocolName { get; set }
}

Then you can get the result:

var currentCalls = entities.Database.SqlQuery<CurrentCall>("exec [dbo].[current_call] @call_id", new SqlParameter("call_id", c_id)).ToList();

If you want to return the result as serialized json, then try:

var jsonResult = JsonConvert.SerializeObject(currentCalls); 
return Json(jsonResult)