C# SqlKata engine update query doesn't seem to execute

Question

I have a C# function which is designed to perform an update on a SQL Server 2016 table. I'm leveraging the SqlKata engine for this purpose.

I'm probably doing something very simple / stupid, I have the feeling I'm constructing the query, but not executing it? The documentation simply shows the following for an UPDATE statement:

var query = new Query("Posts").WhereNull("AuthorId").AsUpdate(new {
    AuthorId = 10
});

My update is slightly more complex, but essentially constructed the same. I am using a dynamic object for some properties, hence me forcing ToString and Convert.ToInt32 as a part of testing whether there's issues with the properties themselves. Logging indicates that these values are correct and present however.

using (var connection = new SqlConnection(await RetrieveParameterStoreValue(ParameterStoreStrings.Database, true, context, environmentLogLevel)))
{
    var db = new QueryFactory(connection, new SqlServerCompiler())
    {
        // Log the compiled query to the console
        Logger = compiled =>
                        {
                            CreateCloudWatchLog($"Query = {compiled.ToString()}", context, LogLevel.Trace, environmentLogLevel);
                        }
    };

    string tableName = updateRequest.insite_request.objectReference.ToString();

    foreach (dynamic stockUpdate in updateRequest.insite_request.payload.items)
    {
        CreateCloudWatchLog($"Servername = {updateRequest.insite_request.payload.store_reference.ToString()} sku={stockUpdate.sku.ToString()} new soh={Convert.ToInt32(stockUpdate.committed_count)}", context, LogLevel.Error, environmentLogLevel);

        var query = db.Query(tableName)
                      .Where(new {
                            sku = stockUpdate.sku.ToString(),
                            ServerName = updateRequest.insite_request.payload.store_reference.ToString()
                            })
                      .AsUpdate(new
                            {
                                stock_on_hand = Convert.ToInt32(stockUpdate.committed_count)
                            });

        CreateCloudWatchLog($"stock update for {tableName}", context, LogLevel.Error, environmentLogLevel);
    }
}

I have a feeling I need to invoke the constructed query somehow, but I'm not clear on how. I have some SqlKata code that does a SELECT statement, and in order to invoke it I use

 var results = query.Get();

But I can't find any documentation around a similar invocation (along the lines of query.Update or similar?) Apologies for the stupid question.

For reference, the logged output doesn't show any signs of execution, and the data in the table remains unchanged.

[Error] Servername = P777S001 sku=13643 new soh=10
[Error] stock update for InSiteClickCollect
[Error] Servername = P777S001 sku=13644 new soh=10
[Error] stock update for InSiteClickCollect
END RequestId: 2fec77e5-6a4e-4990-85e8-f541c9df6eef

Are you sure you don't find any documentation ? https://sqlkata.com/docs/execution/update#update-existing-data — amd, Feb 15 '19 at 09:40

score 4 · Accepted Answer · answered Feb 15 '19 at 09:45

the AsUpdate method does not execute the query, and it's used to build the sql update string only.

To execute your query you have to do the following.

use db.Query() instead of new Query() where db is the QueryFactory instance (it returns an instance of XQuery hence executable Query)
use Update, Insert, Count etc... instead of AsUpdate, AsInsert and AsCount

I strongly recommend you to read the execution section in the documentation https://sqlkata.com/docs/execution/

C# SqlKata engine update query doesn't seem to execute

1 Answers1