Update Table SQL Query: SqlConnection

Question

I would like to update a record based on Id parameter, I have tried following step but that does not seem correct way to do because it generates compilation error:

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);
            }
}

Error:

only assignment call increment decrement await and new object expressions can be used as a statement

Please tag your question with the database that you are using: mysql, sql-server, oracle...? — GMB, Apr 21 '20 at 16:28

score 0 · Answer 1 · answered Apr 21 '20 at 16:32

You're after something like:

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

However, I don't know where you intend to get name, address, etc from - they aren't shown in the question.

score 0 · Answer 2 · answered Apr 22 '20 at 09:05

Thanks guys I got it resolved by tweaking little :-

 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
                    });
                }

score 0 · Answer 3 · answered Dec 28 '22 at 04:36

I am giving an example of single value update operation based on Id filed. Your may try as your own. I used SqlDataAdapter and SqlCommand class for this operation.

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;
}

Update Table SQL Query: SqlConnection

3 Answers3