0

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

1 Answer 1

0

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;
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.