使用C#从SQL Server查询JSON

Question

我使用Audit.Net对我的ASP.NET Razor应用程序进行审计，结果以JSON格式存储在SQL Server数据库中。表的名称为AuditTrail，JSON结果存储在名为JsonData的列中。下面是典型的JSON输出：

{
    "EventType": "GET /Account/Menu",
    "Environment": {
        "UserName": "xxx",
        "MachineName": "xxx-MacBook-Pro",
        "DomainName": "xxx-MacBook-Pro",
        "CallingMethodName": "xxx.Areas.Identity.Pages.Account.MenuModel.OnGet()",
        "AssemblyName": "xxx, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null",
        "Culture": ""
    },
    "StartDate": "2020-12-10T14:44:47.067556Z",
    "EndDate": "2020-12-10T14:44:47.067788Z",
    "Duration": 0,
    "Action": {
        "TraceId": "xxxx",
        "HttpMethod": "GET",
        "ControllerName": "Identity",
        "ActionName": "/Account/Menu",
        "ViewPath": "/Account/Menu",
        "ActionParameters": {},
        "RequestBody": {},
        "ResponseBody": {
            "Type": "PageResult"
        },
        "UserName": "[e-mail]",
        "RequestUrl": "https://localhost:5001/Identity/Account/Menu",
        "IpAddress": "::1",
        "ResponseStatusCode": 200
    }
}

我该如何从JSON输出中选择特定的参数，例如“Action.UserName”，而无需输出整个JSON输出，目前的情况是我需要将整个JSON输出传递给视图。目前我的做法是将JSON输出表示为一个类并连接到数据库，但它不起作用。

    public class Action
    {
        public string TraceId { get; set; }
        public string HttpMethod { get; set; }
        public string ControllerName { get; set; }
        public string ActionName { get; set; }
        public string ViewPath { get; set; }
        public string UserName { get; set; }
        public string RequestUrl { get; set; }
        public string IpAddress { get; set; }
        public int ResponseStatusCode { get; set; }
    }

    public class Audits
    {
        public List<Action> Actions { get; set; }
    }

         string connectionString = "Data Source=localhost;User ID=sa;Password=xxx;initial 
         catalog=xxx.db;integrated security=false;";

        public string UserName { get; set; }
        public string IpAddress { get; set; }
        public string HttpMethod { get; set; }

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT JsonData FROM AuditTrail";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                List<AuditLog> auditLogs1 = new List<AuditLog>();
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        AuditLog audit1 = new AuditLog();
                        audit1.User = reader["JsonData"].ToString();
                        auditLogs1.Add(audit1);
                    }
                    connection.Close();
                }
                var JsonResult = JsonConvert.SerializeObject(auditLogs1);
                var JsonResult1 = JsonConvert.DeserializeObject<Audits>(JsonResult);

                foreach (var x in JsonResult1.Actions)
                {
                    UserName = x.UserName;
                    HttpMethod = x.HttpMethod;
                    IpAddress = x.IpAddress;
                }
           }

- aasonu

---

你现在得到的结果是什么？你的程序有任何错误弹出吗？期望的结果是什么？ - MestreDosMagros

@MestreDosMagros 目前它没有显示任何内容，也没有弹出错误。预期结果是UserName、HttpMethod和IpAddress应该显示JSON输出中当前显示的值。 - aasonu

重要的问题是，使用哪个版本的 Sql Server？较新版本的 MSSQL 具有内置的 JSON 函数，可以让 MSSQL 查询 JSON 数据，并返回您想要的字符串，而无需将整个 JsonData 下载下来。 - McAden

@McAden 我正在运行 SQL Server 2019。 - aasonu

Answer 1

在 Sql Server 2016 及更新版本中，您可以通过更新查询来简化此操作:

SELECT
    JSON_VALUE(JsonData, '$.Action.UserName') as UserName
   ,JSON_VALUE(JsonData, '$.Action.HttpMethod') as HttpMethod
   ,JSON_VALUE(JsonData, '$.Action.IpAddress') as IpAddress
FROM
    [dbo].[AuditTrail]

将此调整为您的代码，您将得到：

List<AuditLog> logs = new List<AuditLog>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = @"SELECT
        JSON_VALUE(JsonData, '$.Action.UserName') as UserName
       ,JSON_VALUE(JsonData, '$.Action.HttpMethod') as HttpMethod
       ,JSON_VALUE(JsonData, '$.Action.IpAddress') as IpAddress
      FROM
        [dbo].[AuditTrail]";

    using (SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();
        var reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                logs.Add(new AuditLog
                {
                    UserName = reader["UserName"].ToString(),
                    HttpMethod = reader["HttpMethod"].ToString(),
                    IpAddress = reader["IpAddress"].ToString()
                });
            }
        }
    }
}

- McAden

---

非常感谢，我在SQL方面还是很新手。 - aasonu

节日快乐。在SQL Server 2014中有没有实现这个的方法？谢谢。 - aasonu

不要采用这种方法，你需要将其作为字符串提取并解析。 - McAden

Answer 2

好的，那么你可以这样从你的JSON中仅获取“Action”键：

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                List<AuditLog> auditLogs1 = new List<AuditLog>();
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        AuditLog audit1 = new AuditLog();
                        audit1.User = reader["JsonData"].ToString();
                        auditLogs1.Add(audit1);
                    }
                    connection.Close();
                }

                var root = JObject.Parse(auditLogs1);
                var audit = new Audits() { Actions = new List<Action>() };
                var action = root["Action"].ToObject<Action>();
                
                audit.Actions.Add(action);

                foreach (var x in audit.Actions)
                {
                    UserName = x.UserName;
                    HttpMethod = x.HttpMethod;
                    IpAddress = x.IpAddress;
                }
           }

通过这个，你可以弄清楚自己可能正在做什么。

- MestreDosMagros