SQL Server string issue

Question

This INSERT statement works fine when run in SSMS:

INSERT INTO GenInfo
    (ParameterKey, Value, Description, Secured, BranchID, GroupID)
    SELECT 
        'companyURLStem', 'http://dev-sql2019all/company/rdPage.aspx?rdSecureKey={0}', 
        'Main URL to access company reports', -1, BranchID, 4
    FROM Branch

However, when I try to execute this as a string in C# using EF like this:

dbServices.ApplyChangeToTarget(sql);

I get this error:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list

The culprit is the {0} in the string. How can I avoid this error?

I'm doing this as part of a programmatic data migration from a source db to a target so I don't have much flexibility to do custom work. I would have thought that the string would have executed exactly as it did in SSMS.

Any ideas?

Thanks

Carl

Answer 1

Here's the solution:

foreach (var item in newItems)
{
    //Escape the value here:
    item.Value = item.Value.Replace("{", "{{");
    item.Value = item.Value.Replace("}", "}}");

    string sql = @$"INSERT INTO GenInfo
                   (ParameterKey, Value, Description, Secured, BranchID, GroupID)
                        SELECT '{item.ParameterKey}', '{item.Value}', '{item.Description}', {item.Secured}, BranchID, {item.GroupID}
                        FROM Branch";

    dbServices.ApplyChangeToTarget(sql);
}