column saving and serving without touching the column

Question

I wrote an ASP.NET Core 8.0 Web API for an Angular frontend web site with node express and Postgresql 2 years ago we had a habit from that project with frontend programmers and my Web API. I give some columns in the database that I never involved the frontenders use that column to save their kind of settings on it. I'm using Dapper because I love to have SQL in my hands instead of EF Core.

Now we are writing a new project with ASP.NET Core 8 Web API with Angular, but I have problems with json columns.

Let's set an example

Database:

create table users 
(
   user_id bigserial,
   username character varying[50],
   fullname character varying[200],
   password character varying[300],
   salt character varying[300],
   frontends_field jsonb,
   adress_data jsonb
)

public class User
{
    [Required]
    public long? User_Id { get; set; }
    
    [Required]
    [MaxLength(50)]
    public string UserName { get; set; } = string.Empty;
    
    [Required]
    [MaxLength(200)]
    public string FullName { get; set; } = string.Empty;

    [Required]
    [MaxLength(300)]
    public string Password { get; set; } = string.Empty;

    
    [MaxLength(300)]
    public string Salt { get; set; } = string.Empty;

    public ??WHICH_TYPE?? frontends_field { get; set; }
    public ??WHICH_TYPE?? adress_data { get; set; }
}

public class Adress_Data 
{
  public string? adress_name { get; set; }
  public string? streetinfo { get; set; }
  public string? buildinginfo { get; set; }
  public string? city { get; set; }
}

In a controller I save this data like this

[HttpPost]
public async Task<ActionResult<User>> PostUser([FromBody] User req)
{
    try
    {
        using var MyCnt = _db.GetConnection(); //Npgsqlconnection here
        {
            string MySql = """
     INSERT INTO public.users (username, fullname, password, salt, frontends_field, adress_data)
                VALUES (@username, @fullname, @password, @salt, @frontends_field, @adress_data)
                returning *;
                """;
            var result = await MyCnt.QueryFirstOrDefaultAsync<User>(MySql,req);

            return Ok(result);
        }
    }
    catch (Exception ex)
    {
        return BadRequest(ex);
    }
}

Sample JSON for req

{  
    "username" : "{username}",
    "fullname" : "John DOE",
    "password" : "{password}",
    "salt" "asdşfmi<sdmfpwefkpüpowefi",
    "frontends_field" : 
             { "somenumfield" : 15,
               "sometextfield" : "Cry me a river"
             },
    "adress_data" : 
             {
                 "adress_name"  : "home",
                 "streetinfo"   : "Susame Streeet No:21",
                 "buildinginfo" : "A Block",
                 "city" : "IZMIR"
             }
}

I am really stuck at the JSON side of the code...

Thanks all

Answer 1

You could try using the use the JsonDocument or JsonElement types from the System.Text.Json namespace for generic JSON fields or you could also create custom classes for structured JSON fields.

Below is the sample code you could refer:

User.cs:

using System.Text.Json;

namespace webapitest.Models
{
    public class User
    {
        public long? User_Id { get; set; }

        public string UserName { get; set; } = string.Empty;

        public string FullName { get; set; } = string.Empty;

        public string Password { get; set; } = string.Empty;

        public string Salt { get; set; } = string.Empty;

        public JsonDocument Frontends_Field { get; set; }

        public AddressData Adress_Data { get; set; }

    }
}

AddressData.cs:

namespace webapitest.Models
{
    public class AddressData
    {
        public string? Adress_Name { get; set; }
        public string? StreetInfo { get; set; }
        public string? BuildingInfo { get; set; }
        public string? City { get; set; }

    }
}

UsersController.cs

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Npgsql;
using System.Text.Json;
using webapitest.Models;

namespace webapitest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]

    public class UsersController : ControllerBase
    {
        private readonly NpgsqlConnection _dbConnection;

        public UsersController(NpgsqlConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        [HttpPost]
        public async Task<ActionResult<User>> PostUser([FromBody] User req)
        {
            try
            {
                string sql = @"
                    INSERT INTO public.users (username, fullname, password, salt, frontends_field, adress_data)
                    VALUES (@UserName, @FullName, @Password, @Salt, @Frontends_Field::jsonb, @Adress_Data::jsonb)
                    RETURNING *;
                    ";

                var parameters = new
                {
                    req.UserName,
                    req.FullName,
                    req.Password,
                    req.Salt,
                    Frontends_Field = req.Frontends_Field.RootElement.ToString(),
                    Adress_Data = JsonSerializer.Serialize(req.Adress_Data)
                };

                var result = await _dbConnection.QueryFirstOrDefaultAsync(sql, parameters);
                var user = MapUser(result);

                return Ok(user);
            }
            catch (Exception ex)
            {
                return BadRequest(ex);
            }
        }

        private static User MapUser(dynamic record)
        {
            return new User
            {
                User_Id = record.user_id,
                UserName = record.username,
                FullName = record.fullname,
                Password = record.password,
                Salt = record.salt,
                Frontends_Field = JsonDocument.Parse((string)record.frontends_field),
                Adress_Data = JsonSerializer.Deserialize<AddressData>((string)record.adress_data)
            };
        }

    }
}

sample request:

{
        "username" : "",
        "fullname" : "John DOE",
        "password" : "",
        "salt": "asdşfmi<sdmfpwefkpüpowefi",
        "frontends_field" : 
        {
            "somenumfield" : 15,
            "sometextfield" : "Cry me a river"
        },
        "adress_data" : 
        {
            "adress_name"  : "home",
            "streetinfo"   : "Susame Street No:21",
            "buildinginfo" : "A Block",
            "city" : "IZMIR"
        }
    }