SqlDataReader读取JSON列仅返回部分数据的技术问题

问题重现 #

我编写了如下代码，但在读取SQL返回的JSON值时，仅能获取到最后一部分内容：

public string GetUsersJson(long systemOrgId)
{
    var query = @"DECLARE @OrgId bigint = @systemOrgId
     SELECT e.OrgId, e.Id, e.FirstName, e.LastName
     FROM [Internal].[Employee] e
     WHERE OrgId = @OrgId and IsActive=1
     FOR JSON PATH, ROOT('Users');";

    var json = ExecuteSqlCommandWithJsonResponse(query, systemOrgId);

    return json;
}

private string ExecuteSqlCommandWithJsonResponse(string queryString, long systemOrgId)
{
    var result = "";

    using (SqlConnection connection = new SqlConnection(_systemConnectionString))
    {
        using (var cmd = connection.CreateCommand())
        {
            connection.Open();
            cmd.CommandText =queryString;
            cmd.Parameters.AddWithValue("@systemOrgId", systemOrgId);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    result = reader.GetString(reader.GetOrdinal("JSON_F52E2B61-18A1-11d1-B105-00805F49916B"));
                }
            }
        }
    }

    return result;
}

如果改用if (reader.Read())，则只能获取到第一部分内容：

if (reader.Read())
{
    result = reader.GetString(reader.GetOrdinal("JSON_F52E2B61-18A1-11d1-B105-00805F49916B"));
}

调整代码贴合官方示例后，问题仍然存在：

private string ExecuteSqlCommandWithJsonResponse(string queryString, long systemOrgId)
{
    var result = "";

    using (SqlConnection connection = new SqlConnection(_systemConnectionString))
    {
        var cmd = connection.CreateCommand();
        connection.Open();
        cmd.CommandText = queryString;
        cmd.Parameters.AddWithValue("@systemOrgId", systemOrgId);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            result = reader.GetString(reader.GetOrdinal("JSON_F52E2B61-18A1-11d1-B105-00805F49916B"));
        }
        reader.Close();
    }

    return result;
}

问题原因 #

SQL Server返回较大JSON结果时，会将JSON拆分为多个片段，每个片段对应SqlDataReader的一行。当前代码每次循环都用新片段覆盖之前的结果，所以最终只保留最后一个片段；用if则仅读取第一个片段，导致结果不完整。

解决方案 #

需要将所有JSON片段拼接起来，而非覆盖。推荐使用StringBuilder提升拼接性能（尤其适合大JSON场景），修改后的代码如下：

private string ExecuteSqlCommandWithJsonResponse(string queryString, long systemOrgId)
{
    var resultBuilder = new StringBuilder();

    using (SqlConnection connection = new SqlConnection(_systemConnectionString))
    {
        using (var cmd = connection.CreateCommand())
        {
            connection.Open();
            cmd.CommandText = queryString;
            cmd.Parameters.AddWithValue("@systemOrgId", systemOrgId);
            using (var reader = cmd.ExecuteReader())
            {
                var jsonColumnOrdinal = reader.GetOrdinal("JSON_F52E2B61-18A1-11d1-B105-00805F49916B");
                while (reader.Read())
                {
                    resultBuilder.Append(reader.GetString(jsonColumnOrdinal));
                }
            }
        }
    }

    return resultBuilder.ToString();
}

如果JSON结果较小，也可以直接用字符串拼接（但性能不如StringBuilder）：

private string ExecuteSqlCommandWithJsonResponse(string queryString, long systemOrgId)
{
    var result = "";

    using (SqlConnection connection = new SqlConnection(_systemConnectionString))
    {
        using (var cmd = connection.CreateCommand())
        {
            connection.Open();
            cmd.CommandText = queryString;
            cmd.Parameters.AddWithValue("@systemOrgId", systemOrgId);
            using (var reader = cmd.ExecuteReader())
            {
                var jsonColumnOrdinal = reader.GetOrdinal("JSON_F52E2B61-18A1-11d1-B105-00805F49916B");
                while (reader.Read())
                {
                    result += reader.GetString(jsonColumnOrdinal);
                }
            }
        }
    }

    return result;
}

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