returning json path in controller MVC

Question

I have a stored procedure that returns a json path :

GO

alter PROCEDURE [dbo].[usp_getjsondata](


@type  INT                 

)
AS
BEGIN

SELECT
          [Time] as 'Time',
          cast([Value] as varchar(10)) as 'Value'
      FROM [dbo].[tbl_data] where type = @type
      for JSON PATH

END

the stored procedure is returning the following: Result

In the controller i have written the following code:

var json = entities.Database.SqlQuery("exec usp_getjsondata  @type",
               new SqlParameter("@type", type)
        ).ToList();

The json data is not being stored in the variable json. Am I missing something here?

Show us the full definition for the stored procedure, in particular how its parameters are defined. — user47589
– user47589, Commented Aug 26, 2017 at 1:33
Try removing the word "exec" from your code. Just speculation, but that may be the issue. — user47589
– user47589, Commented Aug 26, 2017 at 1:42
i need exec to execute the stored procedure. To return the result — TheChosenOne94
– TheChosenOne94, Commented Aug 26, 2017 at 1:43
It's not needed when used from .Net. entities.Database.SqlQuery("usp_getjsondata... — user47589
– user47589, Commented Aug 26, 2017 at 1:44

TheChosenOne94 · Accepted Answer · 2017-08-29 19:00:17Z

1

Posting my answer for anyone stuck on this,

FOR JSON will return a single record with a nvarchar(max) field containing the JSON-formatted results.

string json = string.Concat(entities.Database.SqlQuery("exec usp_getjsondata  @type",
    new SqlParameter("@type", type)
));

You can convert that back to a list of objects, then you'll need to parse the JSON:

var results = JsonConvert.DeserializeObject<IList<Class>>(json);

However, if want to do that, then it would be better to remove the FOR JSON from the stored procedure, and load the results directly into the list:

var results = entities.Database.SqlQuery<Class>("exec usp_getdata @type",
    new SqlParameter("@type", type)
).ToList();

answered Aug 29, 2017 at 19:00

TheChosenOne94

1132 silver badges18 bronze badges

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

Comments

Lucas Argate · Accepted Answer · 2021-04-17 18:59:01Z

I was looking for somiting to help beginers about this solution and help more people with this question. Here a completely solution to abstract a Factory to help use the FOR JSON PATH easily (i hope).

namespace DB {
    public class DbFactory
    {
        public static T ExecForJson<T>(string query, params object[] parans) where T : new()
        {
            var obj = new T();

            using (var db_ = new DbEntities())
            {

                obj = ExecForJson<T>(db_, query, parans);}
            
            
            return obj;
        }
        
        public static T ExecForJson<T>(DbEntities db_, string query, params object[] parans) where T : new()
        {
            var json = string.Concat(db_.Database.SqlQuery<string>(query, parans).ToList());

            if (string.IsNullOrEmpty(json)) return new T();

            return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(json);
        }

        public static System.Data.SqlClient.SqlParameter Param(string name, object value)
        {
            return new System.Data.SqlClient.SqlParameter(name, value);
        }
    }
}

To use like this:

var id = 123;
//like this (create new instance)
var itens = DB.DbFactory.ExecForJson<List<Item>>("SELECT  * FROM Items WHERE ID = @ID FOR JSON PATH;", DB.DbFactory.Param("@ID", id));

//or like this (with current instance)
var itens = DB.DbFactory.ExecForJson<List<Item>>(db, "SELECT  * FROM Items WHERE ID = @ID FOR JSON PATH;", DB.DbFactory.Param("@ID", id))

Collectives™ on Stack Overflow

returning json path in controller MVC

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related