.net SqlDataReader仅从JSON列读取部分数据

eh57zj3b  于 2023-02-14  发布在  .NET
关注(0)|答案(1)|浏览(121)

我有以下代码,但它只读取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代替我得到第一部分。

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

根据SqlDataReader类文档,应使用while (reader.Read())
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-7.0#examples
修改代码使其看起来更像MS示例也会得到相同的结果:

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

ct3nt3jp1#

使用+=的标准连接字符串很容易就解决了这个问题。我希望这能帮助其他人,因为我看到的例子中缺少这些信息。
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;
}

相关问题