In this article I will explain the difference between ExecuteNonQuery, ExecuteScalar and ExecuteReader functions of the SqlCommand class in ADO.Net.
All the three functions i.e. ExecuteNonQuery, ExecuteScalar and ExecuteReader are for different purposes. Newbies generally face difficulty in understanding these three methods.
ExecuteNonQuery
ExecuteNonQuery is basically used for operations where there is nothing returned from the SQL Query or Stored Procedure. Preferred use will be for INSERT, UPDATE and DELETE Operations.
It returns the count of the number of rows affected during the operation, for example, if one row was inserted, updated or deleted, it will return 1 and if no row was affected it will return 0.
INSERT
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Persons (Name, City) VALUES (@Name, @City)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
The screenshot below displays the rows affected.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
UPDATE
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
The screenshot below displays the rows affected.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
DELETE
string name = "Mudassar Khan";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM Persons WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
The screenshot below displays the rows affected.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
What happens when I use ExecuteNonQuery for SELECT statement?
ExecuteNonQuery will work flawlessly for SELECT SQL Query or Stored Procedure but that will simply execute the query and do nothing. Even if you use it you will not throw any error but the Rows Affected will be negative i.e. -1.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
The screenshot below displays the rows affected returned -1.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
Thus concluding it, we must use ExecuteNonQuery for INSERT, UPDATE and DELETE operations only.
ExecuteScalar
ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
For example, in case where I need to get the City of a person based on its Name.
string name = "Mudassar Khan";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT City FROM Persons WHERE Name=@Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string city = o.ToString();
}
con.Close();
}
}
The screenshot below displays the value of the column returned.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
This is a great question and the answer is yes you can use it but as its behavior it will return the very first cell i.e. first row and first column.
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons WHERE Name=@Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string city = o.ToString();
}
con.Close();
}
}
The screenshot below displays the value of the first cell i.e. first row and first column being returned.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
Can we use ExecuteScalar for INSERT, UPDATE and DELETE Statements?
Yes you can. But since INSERT, UPDATE and DELETE Statements return no value you will not get any value returned from the Query as well as you will not get the Rows Affected like you get in ExecuteNonQuery function.
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
object o = cmd.ExecuteScalar();
con.Close();
}
}
The screenshot below displays the returned value being returned as NULL since there’s nothing returned from the UPDATE query.
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
Another case would be when you have an Identity (Auto Increment) column and you need to get the value of the ID column newly added record.
If you want to return a value then you can combine an INSERT, UPDATE and DELETE Statement with a SELECT statement and then the ExecuteScalar function will return value. For example, in the below code, the ID of the new record that was inserted is fetched using SCOPE_IDENTITY.
int customerId;
string query = "INSERT INTO Customers(Name, Country) VALUES(@Name, @Country)";
query += " SELECT SCOPE_IDENTITY()";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedItem.Text);
cmd.Connection = con;
con.Open();
customerId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
Thus concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.
ExecuteReader
ExecuteReader is strictly used for fetching records from the SQL Query or Stored Procedure i.e. SELECT Operation.
Example would be fetching Name City for all records in the Person Table.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string name = dr["Name"].ToString();
string city = dr["City"].ToString();
Response.Write("Name: " + name);
Response.Write("City: " + city);
}
con.Close();
}
}
![Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery]()
Once the ExecuteReader method is executed, it returns an object belonging to IDataReader Interface. Since we are dealing with SQL Server, I have used SqlDataReader. Until the DataReader Read method is returning true, it means that it is fetching record.
Hence a while loop is executed and records are fetched one by one.
ExecuteReader can also be used to bind a GridView control, but do it only if the GridView does not need Paging to be implemented. This is necessary since if you set AllowPaging to True and bind GridView using DataReader then you will land into an Exception as DataReader fetches records in ForwardOnly Mode.
But I would recommend using DataReader for GridViews without paging since DataReader is more efficient that SqlDataAdapter.
Thus, I have tried to cover most of the aspects of this topic and tried to explain the difference between each of these three methods in a short and illustrious way.
