How to use SQL result as JSON in .Net

Question

With this query I able to select result as JSON.

string strQuery = "declare @json varchar(max) = (select field1 from table1 FOR JSON AUTO)print @json";

How could to use this result on .Net and what type of parameter should use instead of Newtonsoft.Json.Linq.JObject

using (TestEntities objEntities = new TestEntities())
        {
            Newtonsoft.Json.Linq.JObject strJson = objEntities.Database.SqlQuery<Newtonsoft.Json.Linq.JObject>(strQuery).FirstOrDefault();
            Response.Write(strJson.ToString());
        }

print and raiserror output are accessed via the SqlConnection.InfoMessage event hook. Have you tried just selecting it? e.g.: select (select field1 from table1 for json auto) as MyColumnName — AlwaysLearning
– AlwaysLearning, Commented Dec 19, 2021 at 11:39
@AlwaysLearning So thank you. Please post it as answer. And if it's possible add varchar(max), so there is not limitation for result. Thanks — مهدی نبوی
– مهدی نبوی, Commented Dec 19, 2021 at 11:47
Why would you use PRINT rather than SELECT? PRINT is designed for informational debugging messages, not for normal data transfer, the max length is 4000 as documented — Charlieface
– Charlieface, Commented Dec 19, 2021 at 12:30

Charlieface · Accepted Answer · 2021-12-19 12:38:00Z

1

You should read this as a string, then deserialize it afterwards if you need to. But you are in any case ToString-ing it, so there is no point deserializing it at all. Just keep it as a string.

Furthermore, PRINT has a maximum of 8000 bytes (4000 Unicode characters), and is intended for informational messages, it is not designed for data transfer. Use SELECT instead.

You should also pass JSON as nvarchar(max)

using (TestEntities objEntities = new TestEntities())
{
    const string strQuery = @"
declare @json nvarchar(max) = (
    select field1
    from table1
    FOR JSON AUTO
);
select @json;
";
    var result = objEntities.Database.SqlQuery<string>(strQuery).FirstOrDefault();
    Response.Write(result);
    // alternatively
    var strJson = Newtonsoft.Json.Linq.JObject.Parse(result);
}

Don't be tempted to just do a bare SELECT with FOR JSON, as it has issues when the result is over 2000 characters. You must put it in a variable first.

answered Dec 19, 2021 at 12:38

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

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

1 Comment

مهدی نبوی Over a year ago

So thank you, fantastic answer and description.

Collectives™ on Stack Overflow

How to use SQL result as JSON in .Net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related