Return last record from table

0

I'm trying to make a method that returns the last record of the table but gives error:

  

"Coditem" operation failed

   public int UltimoItem()
    {
        con = conexao.obterConexao();
        try
        {
            cmd = new SqlCommand("SELECT MAX(Cod_Item) FROM Pedidos_Itens", con);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            int Ultimo = 0;
            while (dr.Read())
            {
                Ultimo = Convert.ToInt32(dr["Cod_Item"]);

            }
            return Ultimo;
        }
        catch (Exception ex)
        {
            throw new Exception("Falha na operação: " + ex.Message);
        }
        finally
        {
            con.Close();
        }
    }
    
asked by anonymous 04.10.2017 / 21:48

2 answers

1

As @Leando said, you're here: Ultimo = Convert.ToInt32(dr["Cod_Item"]); When using MAX you should name this field to be able to retrieve it.

In this case I believe you could access positionally using the syntax Ultimo = Convert.ToInt32(dr[0]); , but it is not recommended that when changing your SELECT, you can make a mistake by changing the column position when inserting a new one or changing the SELECT structure.

Try:

public int UltimoItem()
    {
        con = conexao.obterConexao();
        try
        {
            cmd = new SqlCommand("SELECT MAX(Cod_Item) AS 'CodItem' FROM Pedidos_Itens", con);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            int Ultimo = 0;
            while (dr.Read())
            {
                Ultimo = Convert.ToInt32(dr["CodItem"]);

            }
            return Ultimo;
        }
        catch (Exception ex)
        {
            throw new Exception("Falha na operação: " + ex.Message);
        }
        finally
        {
            con.Close();
        }
    }
    
04.10.2017 / 22:37
4

It's much simpler and more efficient than this, just read through the ExecuteScalar() " which is correct:

public int UltimoItem() {
    using (var con = conexao.obterConexao())
    using (var cmd = new SqlCommand("SELECT MAX(Cod_Item) FROM Pedidos_Itens", con) {
        return Convert.ToInt32(cmd.ExecuteScalar());
    }
}

I placed GitHub for future reference.

I have taken care to handle resource releases correctly.

I removed the exception because it is not doing anything useful. First it is a mistake to capture Exception , it is worse to throw another Exception . If you did nothing to recover from the exception then leave it to handle the exception somewhere else. But do only in a more specific, at least a SqlException , maybe even more specific. You do not have to treat all exceptions, just do it if you can do something useful.

    
05.10.2017 / 12:24