How to use multiple SQL statements (in the delete case in ORACLE) on a single line in C #?

6

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

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

Follow 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
    
asked by Joao Paulo 05.02.2014 в 14:11
source

7 answers

6

Try the querys inside an Anonymous Blocks BEGIN..END

What are Anonymous Blocks?

  • They are declared in an in-place application where they must be executed, being passed in run-time to the PL / SQL interpreter , 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";
        }
    }
    
answered by 05.02.2014 в 14:17
2

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

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

}
    
answered by 05.02.2014 в 14:49
2

A more robust solution and complete what I suggest. Knowing that everything has a quick, easy and wrong solution, better spend 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 ...

    
answered by 28.08.2014 в 22:41
1

UPDATE *

Why do not 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";
    }
}


Beware of 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() - to more than 1 record / column
  • ExecuteScalar() - 1 single record and column
  • ExecuteNonQuery() - no return, just execution
answered by 05.02.2014 в 14:21
1

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);
    
answered by 07.02.2014 в 12:49
0

I think for your code to work, you just need to replace

comand.ExecuteReader();

by calling

comand.ExecuteNonQuery();

Because the execution of your SQL will have no return.

    
answered by 14.05.2015 в 13:52
-2

Use the 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 do not 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 + ")";
    
answered by 06.02.2014 в 20:06