npgsql C# 更新参数化语句 [duplicate]-解网

问：

带有 Dapper 和 npgsql 的 Postgres 间隔不起作用（2 个答案）

上个月关闭。

我打算在下面用 C# 执行参数化更新语句。我的解决方案不使用 Dapper，因此以下帖子（带有 Dapper 和 npgsql 的 Postgres Interval 不起作用）不适用。

            NpgsqlConnection  _command = _dataSource.CreateCommand(
                "UPDATE \"BulkProposal\" SET \"UploadDataJson\" = NULL WHERE \"CreatedDate\" <= NOW() - INTERVAL  @deleteInterval 'days' AND \"UploadDataJson\" is not NULL;");

            _command.Parameters.AddWithValue("deleteInterval", _deleteIntervalInDays);
            _npgsqlConnection.Open();
            recordsDeleted = await _command.ExecuteNonQueryAsync();

执行上述语句时，我收到以下错误：

Unexpected Exception: 42601: syntax error at or near "$1"

POSITION: 92 | Exception Details: Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$1"

POSITION: 92
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataSourceCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataSourceCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at BulkProposalDataPurge.BulkProposalDataPurge.FunctionHandler() in /home/abell/dev/proposal-service-lambdas/BulkProposalDataPurge.cs:line 53
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$1"
    Position: 92
    File: scan.l
    Line: 1188
    Routine: scanner_yyerror

问题似乎与

@deleteInterval 'days'

我还尝试了

'@deleteInterval days'

不确定如何解决此问题。

C# SQL .NET PostgreSQL NPGSQL

NpgsqlConnection _npgsqlConnection = _dataSource.CreateCommand(
    "UPDATE \"BulkProposal\" SET \"UploadDataJson\" = NULL WHERE \"CreatedDate\" <= NOW() - INTERVAL @deleteInterval days AND \"UploadDataJson\" is not NULL;");

_npgsqlConnection.Parameters.AddWithValue("deleteInterval", _deleteIntervalInDays);
_npgsqlConnection.Open();
recordsDeleted = await _npgsqlConnection.ExecuteNonQueryAsync();

在运行此代码之前，请确保_deleteIntervalInDays设置为正确的数字;它应该是您打算用于间隔的天数。希望它能:)

上一个：FastReport：另存为 Excel 2007 文件（.xlsx） VS XML （Excel）表 .xls

下一个：Oracle Select 与数组绑定

npgsql C# 更新参数化语句 [duplicate]

Npgsql C# Update Parameterized Statement [duplicate]

评论