更新表SQL查询:SqlConnection

Question

我想根据Id参数更新一条记录，我已经尝试了以下步骤，但这似乎不是正确的方法，因为它会产生编译错误：

public async Task CustomerUpdateAsync(string customerId)
{
await using var sqlConnection = new SqlConnection(_connectionString);
{
var sqlQuery = "UPDATE Customer(CustomerId,Name,Address,PostalCode,City)" +
"SET (@CustomerId,@Name,@Address,@PostalCode,@City)" +
$"WHERE CustomerId=@CustomerId", new {CustomerId = customerId};
await sqlConnection.ExecuteAsync(sqlQuery, customerId);
}
}

错误：

只有赋值调用递增递减等待和新对象表达式可以用作语句

Answer 1

您想要的是：

await sqlConnection.ExecuteAsync(@"
UPDATE Customer
SET    Name = @name, Address = @address,
PostalCode = @postalCode, City = @city
WHERE  CustomerId=@customerId",
new { customerId, name, address, postalCode, city });

然而，我不知道你打算从哪里获得name、address等——问题中没有显示它们。

Answer 2

谢谢大家，我通过调整一下解决了这个问题：-

public async Task CustomerUpdateAsync(Customer customer)
{
await using var sqlConnection = new SqlConnection(_connectionString);
{
var sql = "UPDATE Customer SET Name=@Name,Address=@Address, PostalCode=@PostalCode," +
"City=@City WHERE CustomerId=@CustomerId";
await sqlConnection.ExecuteAsync(sql, new
{
CustomerId = customer.CustomerId,
Name = customer.Name,
Address = customer.Address,
PostalCode = customer.PostalCode,
City = customer.City
});
}

Answer 3

我给出了一个基于Id字段的单值更新操作的示例。你可以自己尝试。我使用SqlDataAdapter和SqlCommand类执行此操作。

try
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand sc = new SqlCommand("UPDATE [dbo].[TableName] SET [ColumnName] = @ValueToBeUpdated WHERE [Id] = @IdField", con);
sc.Parameters.AddWithValue("@ValueToBeUpdated", valueToBeUpdated);
adapter.UpdateCommand = sc;
con.Open();
adapter.UpdateCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
logger.LogError($"Update failed! {ex.Message}");
throw ex;
}