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": "xxx@gmail.com",
        "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;
                }
           }

What is the result you getting now? Is there any errors popping in your routine? What is the expected result? — MestreDosMagros, Dec 10 '20 at 16:32
@MestreDosMagros Right now it doesn't display anything, and no errors are popping up. The expected result is that UserName, HttpMethod and IpAddress should display the values currently shown in the JSON output. — aasonu, Dec 10 '20 at 16:49
Important question is, what VERSION of Sql Server? Newer versions of MSSQL have built-in JSON functions that will have MSSQL query the JSON for you and so you get back the string you want rather than pulling the whole JsonData down — McAden, Dec 10 '20 at 17:07

McAden · Accepted Answer · 2020-12-10T17:38:35.907

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

Compliments of the season. Is there a way to do this in SQL server 2014? Thanks — aasonu, Dec 29 '20 at 11:05
Not this approach, you'll need to pull it as a string and parse it. — McAden, Dec 29 '20 at 17:41

MestreDosMagros · Answer 2 · 2020-12-10T17:10:33.417

Ok so, you can get only the "Action" key from your JSON this way:


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

With this you can figure out what you might be doing

Query JSON in C# from a SQL Server

2 Answers2