How do you get a very long result of running a procedure in SQL in C#?

Question

I'm trying to get a long JSON from a procedure into a STRING in C#, but the JSON is cut off in the middle of the 255th byte. I would love to receive advice on how to receive the JSON perfectly.

I tried to get the result from running the SQL procedure that returns a long JSON into a STRING.

my code:

 var selectQuery = $"exec [dbo].[GetOrderStatus] '{order}' ";

                SAPbobsCOM.Recordset ss = SAPConnection.GetInstance().Recordset;
                ss.DoQuery(selectQuery);
                if (ss.EoF) { return new ResponseData() { Status = StatusE.GanericError, ErrorMessage = $"Error in exec proc, {selectQuery}" }; }
                dynamic  jsonResult = (ss.Fields.Item(0).Value);
if (!string.IsNullOrEmpty(jsonResult))
                {
                    return new ResponseData() { Status = StatusE.succsed, ErrorMessage = "" , ValueResult =jsonResult};
                }

the SP :

ALTER PROCEDURE [dbo].[GetOrderStatus]
@SiteRdr as nvarchar(50)
AS
BEGIN
declare @Output nvarchar(max)
set @Output=(
        SELECT
                    (SELECT DISTINCT
                        o.DocEntry AS SapEntry,
                        o.DocNum as DocNum,
                        o.U_SiteRdr as SiteEntry,
                        o.DocStatus AS [Status],
                        o.CreateDate as CreateDate,
                        o.U_DeliveryNo as DeliveryNo,
                        o.U_Site as SiteName,
                        --a.DocStatus AS OldStatus,
                        --a.UpdateDate,
                        o.DocTotal,
                        (
                            SELECT
                                o1.ItemCode,
                                s.U_SiteID,
                                o1.Quantity,
                                o1.Factor1,
                                o1.U_Flavor as flavor,
                              o1.LineTotal,
                                o1.CodeBars as Barcode
                            FROM rdr1 o1 join [@SITECODE]s on o1.ItemCode=s.U_ItemCode
                            WHERE o.DocEntry = o1.DocEntry
                            FOR JSON AUTO, ROOT('Items')
                        ) AS OrderItems
                    
                    FROM
                        ordr o
                    --LEFT JOIN adoc a ON o.DocEntry = a.DocEntry AND a.ObjType = '17'
                    WHERE
                        o.U_siterdr = @SiteRdr
                    FOR JSON AUTO, ROOT('Order')
                    )As [Order]
        
                    ,(SELECT DISTINCT
                        o.DocEntry AS SapEntry,
                        o.DocNum as DocNum,
                        o.U_SiteRdr as SiteEntry,
                        o.DocStatus AS [Status],
                        o.CreateDate as CreateDate,
                        o.U_DeliveryNo as DeliveryNo,
                        o.U_Site as SiteName,
                        o.U_Address as [Address],
                        o.u_city as city,
                     o.DocTotal,
                        (
                            SELECT
                                o1.ItemCode,
                                s.U_SiteID,
                                o1.Quantity,
                                o1.LineTotal,
                                o1.CodeBars as Barcode,
                                o1.U_BCCheck as BCCheck,
                                o1.U_QtyCheck as QtyCheck
                            FROM dln1 o1 join [@SITECODE]s on o1.ItemCode=s.U_ItemCode
                            WHERE o.DocEntry = o1.DocEntry
                            FOR JSON AUTO, ROOT('Items')
                        ) AS DeliveryItems
                    
                    FROM
                        odln o
                    WHERE
                        o.U_siterdr = @SiteRdr
                    FOR JSON AUTO, ROOT('DeliveryNote')
                    )As [DeliveryNote]
                        ,(SELECT DISTINCT
                        o.DocEntry AS SapEntry,
                        o.DocNum as DocNum,
                        o.U_SiteRdr as SiteEntry,
                        o.DocStatus AS [Status],
                        o.CreateDate as CreateDate,
                        o.U_DeliveryNo as DeliveryNo,
                        o.U_Site as SiteName,
                        o.U_Address as [Address],
                        o.u_city as city,
                      o.DocTotal,
                        (
                            SELECT
                                o1.ItemCode,
                                s.U_SiteID,
                                o1.Quantity,
                                o1.LineTotal,
                                o1.CodeBars as Barcode
                            FROM RDN1 o1 join [@SITECODE]s on o1.ItemCode=s.U_ItemCode
                            WHERE o.DocEntry = o1.DocEntry
                            FOR JSON AUTO, ROOT('Items')
                        ) AS ReturnCertificateItems
                    
                    FROM
                        ORDN o
                    WHERE
                        o.U_siterdr = @SiteRdr
                    FOR JSON AUTO, ROOT('ReturnCertificate')
                    )As [ReturnCertificate]
        from ordr 
        join rdr1 on ordr.docentry=rdr1.docentry
        left join dln1 on rdr1.DocEntry=dln1.BaseEntry
        left join rdn1 on dln1.DocEntry=rdn1.BaseEntry
        where ordr.U_SiteRdr=@SiteRdr
        
        FOR JSON AUTO, ROOT('OrderDetails')
        )
        select @Output
END

It is important to note that if I run the procedure in SQL, a complete and perfect result is obtained!

And so the problem is apparently in getting the result in a string in C#.

Answer 1

The error seems to be in your SP (stored procedure).

The reason of Json being truncated to 255 characters is probably caused by invalid type of a variable that you return from your SP, which is probably VARCHAR.

VARCHAR type has a default limit of 255 characters, which results in trucated json.

Try to use NVARCHAR(MAX) instead.