EN VI

C# - Use UPDATE query inside SELECT query?

How to C# - Use UPDATE query inside SELECT query

I am trying to get shareholders current balance from table, then add for example, 10 percent to their current balance and then update the table back with the new balance, but i am getting error that says there is a connection not closed.

UPDATE: profit_percentage is a variable provided by the user.

try
{
    var con = new SqlConnection(constr);
    con.Open();
    string stm = "SELECT * From shareholders_term";

    var cmd = new SqlCommand(stm, con);

    SqlDataReader rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        if (rdr["current_balance"] != null)
        {
            num++;

            int current_shareholder = Convert.ToInt32(rdr["shareholder_id"]);
            current_balance = Convert.ToDecimal(rdr["current_balance"]);

            decimal percentage = current_balance * profitpercentage / 100;
            decimal new_balance = percentage + current_balance;

            var cmdcurrentbalance = new SqlCommand(
                "UPDATE shareholders_term SET profit_percentage = @percentage, current_balance = @new_balance WHERE shareholder_id = @current_shareholder",
                con
            );

            cmdcurrentbalance.Parameters.AddWithValue("@percentage", profitpercentage);
            cmdcurrentbalance.Parameters.AddWithValue("@new_balance", new_balance);
            cmdcurrentbalance.Parameters.AddWithValue("@current_shareholder", current_shareholder);

            cmdcurrentbalance.ExecuteNonQuery();

            lblupdatecounter.Text = num + " - Shareholders updated.";
        }
    }
    con.Close();

    MessageBox.Show("Profit added to shareholders");
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Solution:

This will easily fit as a single UPDATE query, with no need to ever pull any data back to C# for processing.

I don't see a source for the profitpercentage variable in C#. I expect this was intended to load from the table in the same way as current_balance. If that's true, the result will look like this:

try
{
    string sql = @"
     UPDATE shareholders_term 
     SET current_balance = current_balance * (100.0 + profit_percentage) / 100.0,
         profit_percentage = current_balance * profit_percentage / 100.0
     WHERE current_balance IS NOT NULL;
    ";
    
    using var con = new SqlConnection(constr);
    using var cmd = new SqlCommand(sql, con);
    
    con.Open();
    var num = cmd.ExecuteNonQuery();
    lblupdatecounter.Text = $"{num} - Shareholders updated."
    MessageBox.Show("Profit added to shareholders");
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Though I also question the logic here: it seems to go back and forth between treating profit_percentage as a value or a percentage.