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

I would like to suggest you could remove the sensitive information from your post for next time — Jalpa Panchal
– Jalpa Panchal, Commented Jul 31, 2024 at 3:07
Please do not duplicate the tag information in the title. The tagging system here works extremely well, and adding the same information to the title is just noise and clutter. Thanks. — Ken White
– Ken White, Commented Jul 31, 2024 at 3:25

Jalpa Panchal · Accepted Answer · 2024-07-31 03:19:06Z

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

Collectives™ on Stack Overflow

column saving and serving without touching the column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related