如何在C#中完整获取SQL存储过程返回的超长JSON结果

问题场景 #

调用SQL存储过程返回超长JSON时，C#端通过SAPbobsCOM.Recordset读取结果，JSON在255字节处被截断。直接在SQL执行存储过程可得到完整结果，问题出在C#的结果读取环节。

现有C#代码：

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 };
}

对应的SQL存储过程：

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,
                        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
                    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

解决方案 #

1. 使用GetString方法读取完整字段值 #

SAPbobsCOM.Recordset的Fields.Item(0).Value默认会将长文本截断为255字符，改用GetString方法可以读取完整内容：

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}" }; }

// 读取完整字段内容
string jsonResult = ss.Fields.Item(0).GetString(0, (int)ss.Fields.Item(0).ActualSize);

if (!string.IsNullOrEmpty(jsonResult))
{
    return new ResponseData() { Status = StatusE.succsed, ErrorMessage = "", ValueResult = jsonResult };
}

GetString参数说明：第一个参数是读取起始位置（从0开始），第二个参数是读取的字节数，通过ActualSize获取字段实际长度，确保读取全部内容。

2. 验证存储过程返回字段类型（可选） #

虽然存储过程中@Output是nvarchar(max)，但可以在最后显式指定返回类型，避免隐式转换：

select CAST(@Output as nvarchar(max)) as OutputJSON

这一步作为兜底验证，确保SQL返回的是最大长度的文本类型。

3. 修复动态SQL注入风险（建议） #

当前代码通过字符串拼接生成执行语句，存在SQL注入风险，建议改用参数化调用：

SAPbobsCOM.Recordset ss = SAPConnection.GetInstance().Recordset;
// 使用参数化方式传递参数
ss.Parameters.Add("@SiteRdr", SAPbobsCOM.BoFieldTypes.db_NVarChar, 50);
ss.Parameters.Item("@SiteRdr").Value = order;
ss.DoQuery("exec [dbo].[GetOrderStatus] @SiteRdr");

if (ss.EoF) { return new ResponseData() { Status = StatusE.GanericError, ErrorMessage = $"Error in exec proc" }; }

string jsonResult = ss.Fields.Item(0).GetString(0, (int)ss.Fields.Item(0).ActualSize);
if (!string.IsNullOrEmpty(jsonResult))
{
    return new ResponseData() { Status = StatusE.succsed, ErrorMessage = "", ValueResult = jsonResult };
}

内容的提问来源于stack exchange，提问作者Esther