Creating more dynamic queries with objects

Question

I have an API with a Post endpoint where the body is quite flexible. Basically I allow anything to be posted there and then I will serialize this body to an object, like this:

  public class PostBody
    {
        public string Id{ get; set; }
        public string SomeOtherValue{ get; set; }
        public string AnotherValue{ get; set; }
    }


    string body = 
   "{
            "id": "335",
            "someOtherValue": "fsdfds"
    }"

PostBody body = JsonConvert.DeserializeObject<PostBody>(body);

As you can see here Postbody can be populated with "AnotherValue" but it also might not depending on what the client wants to insert/update.

And lets say that my database table contains all three, "Id", "SomeOtherValue" and "AnotherValue" as columns.

Now, how can I use dapper to dynamically insert/update this table, depending if the properties in the PostBody object are null or not? I mean if "AnotherValue" is null, the query should only be like this:

INSERT INTO table
(
[Id], [SomeOtherValue]
)
VALUES
( 
@Id, @SomeOtherValue
)

But if "AnotherValue" is not null, the query should be like this:

INSERT INTO table
    (
    [Id], [SomeOtherValue], [AnotherValue]
    )
    VALUES
    ( 
    @Id, @SomeOtherValue, @AnotherValue
    )

Is there a nice way to do this automatically like with a mapper or similar? Or else I would have to code a very long if statement, concatenating a query together. It would me messy and difficult to maintain.

Edit:

This is how my insert looks today:

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace Api.Actions.Commands
{
    class InsertStuffRequest
    {
        public InsertStuffRequest( string id, string SomeOtherValue, string AnotherValue)
        {
            Id=id;
            SomeOtherValue = someOtherValue;
            AnotherValue = ;notherValue;
        }


        public string Id { get; }
        
        public string SomeOtherValue { get; }
        
        public string AnotherValue { get; }

        public interface IInsertStuffCommand : IQuery<InsertStuffRequest, bool>
        {
        }

        public class InsertStuffCommand : IInsertStuffCommand
        {
            private const string Command =
                @"INSERT INTO table
    (
    [Id], [SomeOtherValue], [AnotherValue]
    )
    VALUES
    ( 
    @Id, @SomeOtherValue, @AnotherValue
    )";

            public async Task<Result<bool>> ExecuteAsync(InsertStuffRequest request,
                IApplicationContext context)
            {
                using (SqlConnection connection = new SqlConnection(context.StuffConnectionString))
                {
                    await connection.OpenAsync();

                    var rowsAffected = await connection
                        .ExecuteAsyncWithRetry
                        (
                            Command, request
                        );

                    return Result.From(rowsAffected > 0);
                }
            }
        }
    }
}

This will of course work fine for inserts but its more difficult for updates. Also my Post endpoint will work with different tables that have different columns.

Answer 1

If your classes match your tables you could use Dapper.Contrib. The library will create the appropriate INSERT or UPDATE queries based on the objects passed to it.

From the examples, for this class :

public class Car
{
    public int Id { get; set; } // Works by convention
    public string Name { get; set; }
}

You can insert new rows with :

connection.Insert(new Car { Name = "Volvo" });

or insert a list with

connection.Insert(cars);

You can update the same way:

connection.Update(new Car() { Id = 1, Name = "Saab" });

Answer 2

I think your question is just a mistake Use your second insert:

INSERT INTO table
    (
    [Id], [SomeOtherValue], [AnotherValue]
    )
    VALUES
    ( 
    @Id, @SomeOtherValue, @AnotherValue
    )

and pass a null value (or empty string) to @AnotherValue when you haven't got value.

For the update query, you must generate a dynamic string by sqlbuilder: (this code not tested and wrote as sample only)

SqlBuilder builder = new SqlBuilder();
    var update = builder.AddTemplate("UPDATE table /**set**/ OUTPUT inserted.id /**where**/");    
    builder.Set("SomeOtherValue= SomeOtherValue", new { StrSomeOtherValue});
    if (!string.IsNullOrEmpty(StrAnotherValue))
    {
        builder.Set("AnotherValue= @AnotherValue", new { StrAnotherValue });
    }
    
    builder.Where("id = @id", new { id = entityId});
    
    var result = new Result<long?>(sqlConn.ExecuteScalar<long?>(update.RawSql, update.Parameters));