Drop Null Values On Update Operations And Preserve Current Value

[moxeed]

Hi

Thank you for the great library; it has solved many problems for us. We are using this library to run server-side updates on SQL tables. Our data is large, and bulk operations are the only solution. However, about half of the values are null and belong to different columns, so we cannot split them into multiple queries because it results in an additional roundtrip with the SQL server.

I want to add the functionality to ignore null values and not update the original value in the database if it is specified in the bulk configs by the user, which will have the false default value.

Because most of the methods are static, I could not add this feature by extending the library, thus this feature should be implemented by adding ISNULL checks on GetCommaSeparatedColumnsMethod for Update Operations. I would be glad to submit a pull request for this feature if you approve.

The code looks like this

public static string GetCommaSeparatedColumnsForUpdate(List<string> columnsNames, string? prefixTable = null, string? equalsTable = null,
                                              Dictionary<string, string>? propertColumnsNamesDict = null)
{
    prefixTable += (prefixTable != null && prefixTable != "@") ? "." : "";
    equalsTable += (equalsTable != null && equalsTable != "@") ? "." : "";

    string commaSeparatedColumns = "";
    foreach (var columnName in columnsNames)
    {
        var equalsParameter = propertColumnsNamesDict == null ? columnName : propertColumnsNamesDict.SingleOrDefault(a => a.Value == columnName).Key;
        commaSeparatedColumns += prefixTable != "" ? $"{prefixTable}[{columnName}]" : $"[{columnName}]";
        if (config.PreserveOriginalValueIfNullOnUpdate)
        {
            commaSeparatedColumns += equalsTable != "" ? $" = ISNULL({equalsTable}[{equalsParameter}], {prefixTable}[{columnName}])" : "";
        }
        else
        {
            commaSeparatedColumns += equalsTable != "" ? $" = {equalsTable}[{equalsParameter}]" : "";
        }
        commaSeparatedColumns += ", ";
    }
    if (commaSeparatedColumns != "")
    {
        commaSeparatedColumns = commaSeparatedColumns.Remove(commaSeparatedColumns.Length - 2, 2); // removes last excess comma and space: ", "
    }
    return commaSeparatedColumns;
}

[borisdj]

Sure, make a PR a will do the review.