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

I made a command because of foreign keys, just to plug hole, but it is not working by an invalid character error, I believe it is ";".

I know I'm doing this in the wrong way, so I accept suggestions for more suitable code. At first I just need to make it work, because it's just a demo 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 error:

ORA-00911: invalid character\n
Author: Maniero, 2014-02-05

7 answers

Try putting the queries inside an Anonymous Blocks BEGIN..END

What are Anonymous Blocks?

  • are declared in an application at the place where they are to be run, being passed in run-time to the PL/SQL interpreter, are usually dynamically built and run 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";
        }
    }
 6
Author: Laerte, 2020-06-11 14:45:34

Below is an improvement suggestion for your project, Save the queries in a separate class in case you need to reuse elsewhere, in case you need to delete a TB_INDICADOR using another parameter, you can use method overhead.

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

}
 2
Author: Ivan Ricardo Lopes, 2014-02-07 10:11:06

A more robust and complete solution 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...

 2
Author: SantanaFire, 2014-08-28 20:41:36

UPDATE *

Why don't you 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 in commandText

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

Note

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

  • ExecuteReader() - for more than 1 Record/column
  • ExecuteScalar() - 1 single record and column
  • ExecuteNonQuery() - no return, only execution
 1
Author: Maicon Carraro, 2014-02-05 13:46:05

Try something like this:

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);
 1
Author: Latrova, 2014-02-07 11:49:00

I believe that for your code to work you just replace

comand.ExecuteReader();

By the call of

comand.ExecuteNonQuery();

Because the execution of your SQL will have no return.

 0
Author: Raimundo Norberto, 2015-05-14 11:52:46

Use Boolean or in 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 be

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

I don't remember if oracle has an in, in MS Sql it would look like this:

comand.CommandText = "DELETE FROM TB_CR_INDICADOR WHERE COD_INDICADOR in (" + codigo1 + "," + codigo2 + "," + codigo3 + ")";
 -2
Author: jean, 2014-02-06 19:06:11