Dapper (for .NET) is not substituting a param when trying to Execute a DB query-CodePudding

When I Execute the following SQL, Dapper is -not- substituting the param .. but just writing that raw to the database.

(or for some obfuscated exact sql)

INSERT INTO
    Blah
SELECT
    Id,
    GETUTCDATE(),
    'Updated Status to WITHDRAWN. JIRA: @JiraTicket. By: admin maintenance task'
FROM
    Blahs
WHERE
     Id = @Id

Instead, this is the sameple data inserted to the DB:

    Updated Status to WITHDRAWN. JIRA: @JiraTicket. By: admin maintenance task

The value of jiraTicket == AAA123

The code is:

using (TransactionScope scope = new())
{
    using (var db = new SqlConnection(_connectionString))
    {
        var rowsaffected = db.Execute(command, new
        {
            Id = id,
            JiraTicket = jiraTicket
        });

        // snipped some UI displaying logic
    }

    if (!isTestRun)
    {
        scope.Complete();
    }
}

I just don't understand? Why is not substituting?

Update 1:

I've also tried this, which gives the same output:

DECLARE @Jira VARCHA(20) = @JiraTicket;

INSERT INTO
    Blah
SELECT
    Id,
    GETUTCDATE(),
    'Updated Status to WITHDRAWN. JIRA: @Jira. By: admin maintenance task'
FROM
    Blahs
WHERE
     Id = @Id

Update 2:

This does work, but I feel dirty. Is there a better way, than this? NOTE: the string substitution as I generate the params....

using (TransactionScope scope = new())
{
    using (var db = new SqlConnection(_connectionString))
    {
        var rowsaffected = db.Execute(command, new
        {
            Id = id,
            JiraTicket = $"Updated Status to WITHDRAWN. JIRA: {jiraTicket} . By: admin maintenance task"
        });

        // snipped some UI displaying logic
    }

    if (!isTestRun)
    {
        scope.Complete();
    }
}

CodePudding user response：

The parameter marker can't be inside a varchar literal. If you want the parameter value to be appended to a string, use CONCAT.

So it should be:

INSERT INTO
    Blah
SELECT
    Id,
    GETUTCDATE(),
    CONCAT('Updated Status to WITHDRAWN. JIRA: ', @JiraTicket, '. By: admin maintenance task')
FROM
    Blahs
WHERE
     Id = @Id