Recover MAX value from an SQL column

Question

    string var = "";
    SqlConnection con = new SqlConnection(Banco._strCon);
    string sql = "select max(end_id) from endereco";
    SqlCommand cmd = new SqlCommand(sql, con);
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.Read())
        var = (dr["max"]).ToString();

    dr.Close();
    con.Close();
    return var;

I need that amount of max, however, I don’t know what to do inside the [""]. If I leave it like this, the following error appears:

dr["max"] 'dr["max"]' threw an Exception of type 'System.Indexoutofrangeexception' Object {System.Indexoutofrangeexception}

Answer 1

Use the ExecuteScalar(). Something like that:

string sql = "select max(end_id) from endereco";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
return ((int)cmd.ExecuteScalar()).ToString();

I put in the Github for future reference.

The ExecuteReader() should be used to query the data as a whole, when the query will generate only a single data, the correct is the ExecuteScalar().

Answer 2

For your case do not need to search by index String:

    if (dr.Read())
        var = (((IDataRecord)dr)[0]).ToString();

Answer 3

In case you don’t know what to put inside [""] enough in the select whose field uses a function of SQL put the field name, example:

...
string sql = "select max(end_id) max from endereco";
...
if (dr.Read())
   var = (dr["max"]).ToString();
...

But the best option even to implement in this case is that of Maniero.