SqlDataReader only reads partial data from JSON column

Question

I have the following code but it only reads the last part of the JSON value:

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 I use if instead I get the first part.

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

According to the SqlDataReader Class documentation a while (reader.Read()) should be used.

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-7.0#examples

Adapting the code to look more like the MS example also gives the same result:

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

Answer 1

Using a standard concatenate strings with += easily solved it. I hope this can help someone else since this information is missing in the examples I have seen.

https://learn.microsoft.com/en-us/dotnet/csharp/how-to/concatenate-multiple-strings#-and--operators

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