JsonUpdateRequest

public class JsonUpdateRequest

{

    public string PrimaryKeyName { get; } // Name of the property containing the primary key value

    public object PrimaryKeyValue { get; } // Primary key value

    public EditMode EditMode { get; } // Enum with values of Insert, Update or Delete

    public Dictionary<string, object> Changes { get; } // Dictionary of changes keyed on property name

}

public async Task<IActionResult> OnPost([FromBody] JsonUpdateRequest jsonUpdateRequest)

{

    string message = "";

    bool success = true;

    using (var conn = new SqliteConnection(_connectionString))

    {

        string sql = string.Empty;

        var paramValues = new DynamicParameters();

        switch (jsonUpdateRequest.EditMode)

        {

            case EditMode.Update:

                string set = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"{k} = @{k}"));

                paramValues.Add($"@{jsonUpdateRequest.PrimaryKeyName}", jsonUpdateRequest.PrimaryKeyValue);

                foreach (string key in jsonUpdateRequest.Changes.Keys)

                {

                    paramValues.Add($"@{key}", jsonUpdateRequest.Changes[key]);

                }

                sql = $"update products set {set} where {jsonUpdateRequest.PrimaryKeyName} = @{jsonUpdateRequest.PrimaryKeyName}";

                message = "Product updated";

                break;

            case EditMode.Insert:

                string columns = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"{k}"));

                string values = string.Join(",", jsonUpdateRequest.Changes.Keys.Select(k => $"@{k}"));

                foreach (string key in jsonUpdateRequest.Changes.Keys)

                {

                    paramValues.Add($"@{key}", jsonUpdateRequest.Changes[key]);

                }

                sql = $"insert into products({columns}) values({values})";

                message = "Product added";

                break;

            case EditMode.Delete:

                paramValues.Add($"@{jsonUpdateRequest.PrimaryKeyName}", jsonUpdateRequest.PrimaryKeyValue);

                sql = $"delete from products where {jsonUpdateRequest.PrimaryKeyName} = @{jsonUpdateRequest.PrimaryKeyName}";

                message = "Product deleted";

                break;

        }

        try

        {

            conn.Execute(sql, paramValues);

        }

        catch (Exception ex)

        {

            message = ex.Message;

            success = false;

        }

        var products = await conn.QueryAsync<Product>("select * from products;");

        return new JsonResult(new JsonUpdateResponse { Success = success, Message = message, DataSet = products });

    }