Query not being executed on database when using DbCommand.ExecuteNonQueryAsync() > 0;

Question

I am trying to execute a query on our database, I've created it in text because I cannot make it efficiently enough in Entity Framework Core.

This is the code i am using but it seems like it is not being executed on the database (i see no record of this transaction on the database) but i also get no errors. What am I doing wrong?

    public async Task<CleanupObservationsResponse> Handle(CleanupObservationsCommand request, CancellationToken cancellationToken)
    {
        var removalDate = DateTime.Now.AddMonths(-3);
        _logger.LogInformation($"Started cleaning up observations for all observations before {removalDate.Date.ToString("yyyy-MM-dd")}");
        await _observationRepository.CleanupObservations(removalDate);
    }

    public Task<bool> CleanupObservations(DateTime removalDate)
    {
        var sql = $"START TRANSACTION;" +
        $"SET @RemovalDate := \"{removalDate.ToString("yyyy-MM-dd")}\";" +
        $"# MySql Variables can only store 1 row and thus the results of this query cannot be saved in a variable." +
        $"# SET @observationsToDelete := (SELECT Identification FROM cpp.Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM PropertyValues WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM HoldReasonObservation WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM ObservationDeviation WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM ObservationQRTokens WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM ObservationTarra WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM Alibi WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM PackageTrackingIdentifications WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM QuestionAnswer WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);" +
        $"DELETE FROM Observation WHERE StartedUtc < @RemovalDate;" +
        $"COMMIT;";

        return this.ExecuteSQL(sql);
    }

    private async Task<bool> ExecuteSQL(string sql)
    {
        var connection = Context.Database.GetDbConnection();
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            return await cmd.ExecuteNonQueryAsync() > 0;
        }
    }

@Ayoub_B Yes i realized i did not inlcude that code, i do that 1 method up the callstack (have added that now to the question as well) — Vincent
– Vincent, Commented May 27, 2020 at 9:38
Can you try wrapping it in a try-catch, maybe an exception is thrown but it's swallowed — Ayoub_B
– Ayoub_B, Commented May 27, 2020 at 9:44
There is a try catch higher up in the stack which will always logg it, but i can try — Vincent
– Vincent, Commented May 27, 2020 at 9:51

Bradley Grainger · Accepted Answer · 2020-05-27 11:48:08Z

In MySQL, a comment that begins with # extends to the end of the line. (See https://dev.mysql.com/doc/refman/8.0/en/comments.html.)

Your raw SQL code has no newlines in it, thus the comment that begins with # MySql Variables can only store includes all the remaining text, and nothing is executed.

I would rewrite that string as using a verbatim C# string (not an interpolated one):

var sql = @"START TRANSACTION;
        SET @RemovalDate := ""{removalDate.ToString("yyyy-MM-dd")}"";
        # MySql Variables can only store 1 row and thus the results of this query cannot be saved in a variable.
        # SET @observationsToDelete := (SELECT Identification FROM cpp.Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM PropertyValues WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM HoldReasonObservation WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM ObservationDeviation WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM ObservationQRTokens WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM ObservationTarra WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM Alibi WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM PackageTrackingIdentifications WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM QuestionAnswer WHERE ObservationIdentification IN(SELECT Identification FROM Observation WHERE StartedUtc < @RemovalDate);
        DELETE FROM Observation WHERE StartedUtc < @RemovalDate;
        COMMIT;";

It would also be better to remove your SQL variable (SET @RemovalDate := ""{removalDate.ToString("yyyy-MM-dd")}"";) and use real command parameters:

cmd.Parameters.AddWithValue("@RemovalDate", removalDate);

This will allow the MySQL connector to format the date correctly for you, and helps avoid SQL injection.

this was indeed the problem, i would never have realized this, thank you. Also made it a parameter as you suggested, i did not do that because i wanted to recycle the execute SQL method but in the end you're right it's a better way

DaleCooper · Accepted Answer · 2020-05-27 09:40:01Z

1

Maybe the connecion is not Open? You call connection.Open(); and then cmd.ExecuteNonQueryAsync()

Maybe you try OpenAsync instead. If it's available, depending on your connector.

answered May 27, 2020 at 9:40

DaleCooper

211 bronze badge

2 Comments

Vincent Over a year ago

using the DbCommand class from the System.Data.Common namespace. It has an async method but i have been using the normal open method like this for a few other queries (which are just 1 single select statement where i use the DbCommand.reader instead of ExecuteNonQuery and those work (but i cannot use the reader for this since it does not return any values).

DaleCooper Over a year ago

Give OpenAsyc a chance or execute a sync. Query. If the Query throws an exception you might don't get it because the async operation and you did not await 'return this.ExecuteSQL(sql);'

Collectives™ on Stack Overflow

Query not being executed on database when using DbCommand.ExecuteNonQueryAsync() > 0;

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related