A single value from the first row and first column of the ResultSet will be returned by ExecuteScalar() method on query execution.
If the ResultSet is having multiple rows or columns, all those rows and columns will be ignored except the first row and first column. If the ResultSet is empty, this function will return NULL.
The best situation to use ExecuteScalar() method is when we are using functions such as COUNT(), SUM(), etc., as it uses only a few resources compared to the ExecuteReader() method.
Example :
public void ExecuteScalarExample()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
try
{
SqlCommand cd = new SqlCommand();
cd.Connection = con;
cd.CommandText = "SELECT SUM(SALARY) FROM EMPLOYEE";
cd.CommandType = CommandType.Text;
con.Open();
Int32 SalaryTotal = Convert.ToInt32(cd.ExecuteScalar());
MessageBox.Show("Total Salary of the employee is : " + SalaryTotal.ToString());
cd.Dispose();
con.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}​
Here, we create an object of the class SqlConnection and SqlCommand. We pass SQL Statement to the object of SqlCommand class, which returns a single value. When ExecuteScalar() function gets executed, a single value will be returned, i.e, the total salary of employees. This value will be displayed using a message box.
