Update oracle via c#

Question

I created a method that updates to oracle. However, it is absurdly slow when it does. When I do other operations like "INSERT and SELECT" in this same table, the response time is very fast. Could you help me, please?

Follow the code below:

    public void UpdateUsuario(string usuario, string novosetor)
    {

        string sql = @"UPDATE cda_f_usuasetor USUASETOR SET CDA_N_SETOR = '" + novosetor + "' WHERE CDA_X_USUARIO = '" + usuario + "'";

        OpenConnection();
        using (Cmd = new OracleCommand(sql, Con))
        {
            Cmd.CommandType = System.Data.CommandType.Text;
            OracleDataReader dr = Cmd.ExecuteReader();
            dr.Close();
        }
        CloseConnection();



    }

Thank you!

Answer 1

Create an index in your column where you are doing Where (CDA_X_USUARIO), that will improve the performance of your update.

CREATE INDEX IX_CDA_X_USUARIO
    ON cda_f_usuasetor (CDA_X_USUARIO)

Your code has a serious security problem, because the way you are mounting your query (concatenating the statement with the variables) it is subject to SQL Injection.

To avoid this, it is recommended that you do not mount the parameters directly, but pass through the "Addwithvalue" method. So your code will stay this way:

public void UpdateUsuario(string usuario, string novosetor)
{
    string sql = "UPDATE cda_f_usuasetor USUASETOR SET CDA_N_SETOR = :P_NOVO_SETOR WHERE CDA_X_USUARIO = :P_USUARIO";

    OpenConnection();
    using (Cmd = new OracleCommand(sql, Con))
    {
        Cmd.CommandType = System.Data.CommandType.Text;
        Cmd.Parameters.AddWithValue("P_NOVO_SETOR", novosetor);
        Cmd.Parameters.AddWithValue("P_USUARIO", usuario);
        Cmd.ExecuteNonQuery()
    }
    CloseConnection();
}

Finally, you don’t need to use the OracleDataReader in this case, as you are not using any returns. Note that I have replaced the Reader method to Cmd.ExecuteNonQuery, that returns only the number of affected lines.