gpt4 book ai didi

c# - 从 SQL Server 查询 C# 中的 JSON

转载 作者:行者123 更新时间:2023-12-04 02:29:30 29 4
gpt4 key购买 nike

我使用 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": "xxx@gmail.com",
"RequestUrl": "https://localhost:5001/Identity/Account/Menu",
"IpAddress": "::1",
"ResponseStatusCode": 200
}
}
我的问题是如何从 JSON 输出中选择特定参数,即“Action.UserName”,而不必输出整个 JSON 输出,这是它当前所做的,然后将其传递给 View 。到目前为止我所做的是将 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;
}
}

最佳答案

在 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()
});
}
}
}
}

关于c# - 从 SQL Server 查询 C# 中的 JSON,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/65238165/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com