Query JSON in C# from a SQL Server

Question

I used Audit.Net to audit my ASP.NET Razor Application and the results are stored in JSON format in a SQL Server Database. The name of the table is called AuditTrail and the JSON results are stored in a column called JsonData A typical JSON output is shown below:

{
    "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": "[email protected]",
        "RequestUrl": "https://localhost:5001/Identity/Account/Menu",
        "IpAddress": "::1",
        "ResponseStatusCode": 200
    }
}

My question is how can I select a particular parameter from the JSON output i.e "Action.UserName" instead of having to output the entire JSON output, which is what it currently does and then pass it on to the View. What I've done so far is to represent the JSON output as a class and then connect to the database, but it doesn't work

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

Answer 1

In Sql Server 2016 and newer you can simplify this by updating your 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]

Adapting this into your code you get:

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