How to use multiple SQL commands (in the case delete on ORACLE) in one line only on C#?

Question

I made a command because of Foreign Keys, only to plug hole, but it is not working for an invalid character error, I believe it is the ";".

I know I’m doing this the wrong way, so I accept suggestions for more appropriate code. At first I just need to make it work, because it’s just a demonstration of the application.

Follows the code:

    public string ExcluirIndicador(int codigo)
    {
        try
        {
            comand = connectionFactory.CreateCommand();
            comand.CommandText = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = " + codigo + "";
            comand.Connection = connection;
            comand.ExecuteReader();

            return "Sucesso";
        }
        catch (Exception e)
        {
            var erro = e.Message;
            return "Erro";
        }
    }

The mistake:

ORA-00911: invalid character\n

Answer 1

Try to put the querys inside an Anonymous Blocks BEGIN..END

What are Anonymous Blocks?

• They are declared in an application at the place where they are to be executed and passed in run-time at the PL/SQL interpreter, are usually built dynamically and executed only once.

Example

BEGIN DELETE FROM; DELETE FROM...; DELETE FROM...; END; 

    public string ExcluirIndicador(int codigo)
    {
        try
        {
            comand = connectionFactory.CreateCommand();
            comand.CommandText = "BEGIN DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = " + codigo + " END";
            comand.Connection = connection;
            comand.ExecuteReader();

            return "Sucesso";
        }
        catch (Exception e)
        {
            var erro = e.Message;
            return "Erro";
        }
    }

Answer 2

A more robust solution and complete what I suggest. Knowing that everything has a quick, easy and wrong solution, better waste time on implementation for quality gain!

#region Controle de Transação
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["Oracle"].ConnectionString))
using (OracleCommand command = connection.CreateCommand())
{
                try
                {
                    connection.Open();

                    using (var transaction = connection.BeginTransaction())
                    {
                        #region excluindo registro da tabela 1'
                        command.CommandText = Constantes.sql1; //Constantes é uma classe estatica com declarações de variaveis ou constantes estaticas onde centraliza os scripts.
                        command.CommandType = System.Data.CommandType.Text;
                        command.Parameters.Add(new OracleParameter("p_codigo ", OracleDbType.int, ParameterDirection.Input)).Value = codigo;
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        #endregion

                        #region excluindo registro da tabela 2'
                        command.CommandText = Constantes.sql2;
                        command.CommandType = System.Data.CommandType.Text;
                        command.Parameters.Add(new OracleParameter("p_Numreq", OracleDbType.int, ParameterDirection.Input)).Value = codigo;
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        #endregion

                        #region excluindo registro da tabela n'
                        command.CommandText = Constantes.sql3;
                        command.CommandType = System.Data.CommandType.Text;
                        command.Parameters.Add(new OracleParameter("p_Numreq", OracleDbType.int, ParameterDirection.Input)).Value = codigo;
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        #endregion

                        transaction.Commit();
                    }
                    #endregion
                }
                //Nestes casos não devolve o requisitorio então não deve atualizar a situação do mesmo!
                catch (OracleException oe) 
                {
                    var retornoAmigavel = Constantes.Error;
                    switch (oe.ErrorCode)
                    {
                        case 12170:
                            retornoAmigavel = oe.ErrorCode +"-"+ Constantes.SemConexao;
                            break;

                        case 28000:
                            retornoAmigavel = oe.ErrorCode + "-" + Constantes.ContaBloqueada;
                            break;

                        case 1073:
                            retornoAmigavel = oe.ErrorCode + "-" + Constantes.ErroDeConexao;
                            break;

                        case 1:
                            retornoAmigavel = oe.ErrorCode + "-" + Constantes.ChavePrimariaViolada;
                            break;

                        default:
                            retornoAmigavel = Constantes.ErroGenerico + oe.Message;
                            break;
                    }
                    Console.WriteLine(retornoAmigavel, oe);
                }
                catch (Exception e)
                {
                    var EretornoAmigavel = "ERROR REQ.Nº: " + requ.numero;
                    EretornoAmigavel += "\nConstantes.ErroGenerico " + e.Message;
                    Console.WriteLine(EretornoAmigavel);
                }
            }
            #endregion

That’s it! I hope I contributed...

Answer 3

Below is a suggested improvement for your project, save the querys in a separate class in case you need to reuse elsewhere, if you need to delete a TB_INDICADOR using another parameter, you can use methods overload.

public string ExcluirIndicador(int codigo)
{
    try
    {
        comand = connectionFactory.CreateCommand();
        comand.CommandText = "BEGIN "+QueryFactory.Exclui_TB_CR_Indicador(codigo) + 
                             QueryFactory.Exclui_TB_CR_Indicador_Periodo(codigo) + 
                             QueryFactory.Exclui_TB_INDICADOR(codigo) + " END;";

        comand.Connection = connection;
        comand.ExecuteReader();

        return "Sucesso";
    }
    catch (Exception e)
    {
        var erro = e.Message;
        return "Erro";
    }
}

public static class QueryFactory
{
    public static string Exclui_TB_CR_Indicador(int codigo)
    {
        return "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " +codigo+ ";  ";
    }

    public static string Exclui_TB_CR_Indicador_Periodo(int codigo)
    {
        return "DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = " +codigo +"; ";
    }

    public static string Exclui_TB_INDICADOR (int codigo)
    {
        return "DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = " +codigo+"; ";
    }

}

Answer 4

Try something like that:

string script = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = " + codigo + "";    
Server server = new Server(connection);
server.ConnectionContext.ExecuteNonQuery(script);

Answer 5

UPDATE*

Why You Don’t Run Individual Commands?

public string ExcluirIndicador(int codigo)
{
    try
    {
        comand = connectionFactory.CreateCommand();
        comand.Connection = connection; 

        comand.CommandType = System.Data.CommandType.Text;
        comand.CommandText = string.Concat("DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = @codigo;",
                                           "DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = @codigo;",
                                           "DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = @codigo;");
        comand.Parameters.AddWithValue("@codigo", codigo);
        comand.ExecuteReader();

        return "Sucesso";
    }
    catch (Exception e)
    {
        var erro = e.Message;
        return "Erro";
    }
}

Caution when concatenating parameters directly into commandText

If the code can be manipulated by the client, the application is vulnerable to SQL Injection.

Observing

If your command has the ExecuteScalar() or ExecuteNonQuery() prefer to use them.

• ExecuteReader() - for more than 1 record/column
• ExecuteScalar() - 1 single record and column
• ExecuteNonQuery()- no return, just execution

Answer 6

I believe that for your code to work just you replace the

comand.ExecuteReader();

by the call of

comand.ExecuteNonQuery();

Because the execution of your SQL will have no return.

Answer 7

Use the boolean OR on your Where

comand.CommandText = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_CR_INDICADOR_PERIODO WHERE COD_INDICADOR = " + codigo + "; DELETE FROM TB_INDICADOR WHERE COD_INDICADOR = " + codigo + "";

would simply

comand.CommandText = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR = " + codigo1 + " || COD_INDICADOR = " + codigo2 + "|| COD_INDICADOR = " + codigo3 + "";

I can’t remember if oracle has an in, in MS Sql would look like this:

comand.CommandText = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR in (" + codigo1 + "," + codigo2 + "," + codigo3 + ")";