ExecuteNonQuery没有解析传递给它的参数

Question

我试图将变量newRetentionLimit添加到Microsoft SQL Server中的表。我传递我想要插入到参数中的值，然后运行ExecuteNonQuery。我没有得到任何错误，但newRetentionLimit不放入表中。我已经调试以确保newRetentionLimit不是空的，并且是一个实际的整数。

问题似乎是ExecuteNonQuery没有根据我在脚本中放入的名称检索参数值。它似乎只是试图运行脚本与参数名称。有人知道为什么吗?

if (request.SystemSettings.Any(s => s.SettingName.Equals("HISTORYRETENTIONDAYS")))
{
var entities = entityRepo.GetList();
var newRetentionLimit = request.SystemSettings.Find(setting => setting.SettingName.Equals("HISTORYRETENTIONDAYS")).SettingValue.ToInt();
var requestContext = new RequestContext();
var sqlParameter = new List<SqlParameter> {
SqlParameterMaker.MakeTypedValueParameter("@retentionValue",newRetentionLimit, SqlDbType.Int)
};
foreach (var entity in entities)
{
var sql = $@"ALTER TABLE [data].[t{entity.Name}] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [hist].[t{entity.Name}], HISTORY_RETENTION_PERIOD = @retentionValue DAYS));";
requestContext.DatabaseContext.ExecuteNonQuery(sql, sqlParameter);
}
}

Answer 1

我最终找到一个解决方案,还能让我保持使用SqlParameter

if (request.SystemSettings.Any(s => s.SettingName.Equals("HISTORYRETENTIONDAYS")))
{
var entities = entityRepo.GetList();
var newRetentionLimit = request.SystemSettings.Find(setting => setting.SettingName.Equals("HISTORYRETENTIONDAYS")).SettingValue.ToInt();
var requestContext = new RequestContext();
foreach (var entity in entities)
{
var sqlParameters = new List<SqlParameter>{
new SqlParameter("@entityName", entity.Name),
new SqlParameter("@retentionPeriod", newRetentionLimit)
};
var sql = "EXEC('ALTER TABLE [data].[t' + @entityName + '] SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = ' + @retentionPeriod + ' DAYS))');";
requestContext.DatabaseContext.ExecuteNonQuery(sql, sqlParameters.ToArray());
}
}