如何在ASP.NET Web API控制器中获取SQL Server存储过程返回的JSON数据

问题场景 #

我在SQL Server中创建了返回JSON格式的存储过程GetAllRoles：

ALTER PROCEDURE [dbo].[GetAllRoles]
AS
BEGIN
    SELECT * 
    FROM dbo.roles 
    FOR JSON PATH;
END

该存储过程的预期输出是标准JSON数组：

[
    { "role_id": 101, "role_description": "Trainee" },      
    { "role_id": 102, "role_description": "Lead" },
    { "role_id": 103, "role_description": "Manager" },
    { "role_id": 104, "role_description": "Senior Tester Engineer" },
    { "role_id": 105, "role_description": "Junior Tester Engineer" },
    { "role_id": 106, "role_description": "Senior Developer AEM" },
    { "role_id": 107, "role_description": "Junior Developer AEM" }
]

但在ASP.NET API控制器中调用该存储过程后，Postman请求得到的响应不符合预期，并非上述JSON数组：

public StringBuilder Get()
{
    var jsonResult = new StringBuilder();
    var connect = new SqlConnection(ConfigurationManager.ConnectionStrings["SupplyCon"].ConnectionString);

    connect.Open();

    SqlCommand cmd = connect.CreateCommand();
    cmd.CommandText = "GetAllRoles";
    cmd.CommandType = CommandType.StoredProcedure;

    var reader = cmd.ExecuteReader();

    if (!reader.HasRows)
    {
        jsonResult.Append("[]");
    }
    else
    {
        while (reader.Read())
        {
            jsonResult.Append(reader.GetString(0).ToString());
        }
    }

    return jsonResult;
}

问题原因 #

1. 返回类型错误：API方法返回StringBuilder时，ASP.NET会将其序列化为字符串（比如包裹额外双引号），导致响应不是纯JSON格式。
2. 资源未正确释放：未使用using语句管理数据库连接和命令对象，存在资源泄漏风险。
3. 读取方式低效：FOR JSON PATH返回的是单行单列的结果，用ExecuteReader循环读取完全没必要，反而增加复杂度。

修复方案 #

方案1：直接返回JSON字符串（快速修复） #

使用ExecuteScalar直接获取存储过程返回的JSON字符串，通过ContentResult指定正确的内容类型：

public IActionResult Get()
{
    string jsonResult = "[]";
    // using语句自动释放连接资源
    using (var connect = new SqlConnection(ConfigurationManager.ConnectionStrings["SupplyCon"].ConnectionString))
    {
        connect.Open();
        // using语句自动释放命令资源
        using (SqlCommand cmd = connect.CreateCommand())
        {
            cmd.CommandText = "GetAllRoles";
            cmd.CommandType = CommandType.StoredProcedure;

            var result = cmd.ExecuteScalar();
            if (result != DBNull.Value)
            {
                jsonResult = result.ToString();
            }
        }
    }
    // 指定内容类型为application/json，确保响应格式正确
    return Content(jsonResult, "application/json");
}

方案2：返回强类型对象列表（推荐，符合REST API规范） #

先定义与JSON结构匹配的实体类，再将存储过程返回的JSON反序列化为对象列表，最后通过Ok()返回：

1. 定义Role实体类：

public class Role
{
    public int role_id { get; set; }
    public string role_description { get; set; }
}

2. 修改API方法（需引用Newtonsoft.Json NuGet包）：

using Newtonsoft.Json;

public IActionResult Get()
{
    List<Role> roles = new List<Role>();
    using (var connect = new SqlConnection(ConfigurationManager.ConnectionStrings["SupplyCon"].ConnectionString))
    {
        connect.Open();
        using (SqlCommand cmd = connect.CreateCommand())
        {
            cmd.CommandText = "GetAllRoles";
            cmd.CommandType = CommandType.StoredProcedure;

            var result = cmd.ExecuteScalar();
            if (result != DBNull.Value)
            {
                string json = result.ToString();
                roles = JsonConvert.DeserializeObject<List<Role>>(json);
            }
        }
    }
    // Ok()自动将对象序列化为JSON，并设置正确的Content-Type
    return Ok(roles);
}

说明 #

• 两种方案都解决了原代码的格式问题，方案2更利于API的可维护性和扩展性，推荐在生产环境使用。
• 必须使用using语句管理数据库连接和命令对象，避免资源泄漏。

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