CODEWITHSUNDEEP
c#sqlsql-servermax
Meena
Meena

Reputation: 13

Getting MAX value returned SQL command c#

I'm trying to get the MAX value returned but it keeps returning 0.

string stateValue = "CA";
SqlCommand cmd = new SqlCommand("SELECT MAX(Population) FROM TestData WHERE State=" + stateValue);
cmd.Parameters.Add("@Population", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
DBConnection.Instance.execNonQuery(cmd);

int population = (int)cmd.Parameters["@Population"].Value;

In DBConnection class this is the execNonQuery function:

public int execNonQuery(string sCmd, CommandType cmdType)
{
    SqlCommand cmd = new SqlCommand(sCmd, m_sqlDataBase);
    cmd.CommandType = cmdType;

    try
    {
        m_sqlDataBase.Open();
    }
    catch { }

    return cmd.ExecuteNonQuery();
}

Upvotes: 0

Views: 13466

Answers (3)

James Curran
James Curran

Reputation: 103525

How 'bout

  var sql = string.Format("SELECT MAX(Popluation) FROM TestData WHERE State='{0}'", stateValue);
  SqlCommand cmd = new SqlCommand(sql);

(The important part was adding the single quotes. The string.Format was to make it look pretty)

Upvotes: 0

Habib
Habib

Reputation: 223307

Parameter direction is used with Stored Procedures. Here you are simply executing a single query. You need to use SqlCommand.ExecuteScalar method, since you will only get back one result. It returns an object so you have to convert it to int before using it.

Also your code is using string concatenation to create SQL Query and it is prone to SQL Injection. Also consider using using statement with your Command and Connection. 

using (SqlCommand cmd = new SqlCommand("SELECT MAX(Popluation) FROM TestData WHERE State=@state"))
{
    //Associate connection with your command an open it
    cmd.Parameters.AddWithValue("@state", stateValue);
    int populuation = (int)cmd.ExecuteScalar();
}

Upvotes: 6

Mike Dinescu
Mike Dinescu

Reputation: 55740

The ExecuteNonQuery call does not return the result of executing your query.

You can use ExecuteScalar or Execute to get the value back.

    public int execQuery(string sCmd, CommandType cmdType)
    {
        SqlCommand cmd = new SqlCommand(sCmd, m_sqlDataBase);
        cmd.CommandType = cmdType;
        try
        {
            m_sqlDataBase.Open();


            return Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch { 
             // handle your error but don't trap it here..
             throw;
        }
    }

ExecuteScalar is a short-circuit for getting the first value, or the first result set. You can use it to return a single value out of a query, such as yours.

Another option is to use the Execute method to obtain a result set and then use that to get the value you're after:

    public int execQuery(string sCmd, CommandType cmdType)
    {
        SqlCommand cmd = new SqlCommand(sCmd, m_sqlDataBase);
        cmd.CommandType = cmdType;
        try
        {
            m_sqlDataBase.Open();

            using(var dataReader = cmd.Execute())
            {
                if (dataReader.Read())
                {
                      return Convert.ToInt32(dataReader[0]);
                }
            }
        }
        catch { 
             // handle your error but don't trap it here..
             throw;
        }
    }

Upvotes: 3

Related Questions