The ExecuteNonQuery, ExecuteScalar, and ExecuteReader functions of the SqlCommand class in ADO.Net will all be explained in this article.
ExecuteNonQuery, ExecuteScalar, and ExecuteReader are the three functions, and each has a distinct function. The three strategies are typically difficult for beginners to comprehend.
ExecuteNonQuery:
ExecuteNonQuery is mostly utilized for activities where no results from the SQL query or stored procedure are returned. The INSERT, UPDATE, and DELETE operations will be the preferred use.
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();
}
}
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 affected rows are shown in the screenshot below.
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 rows affected are seen in the screenshot below.
What happens when I use ExecuteNonQuery for SELECT statement?
For SELECT SQL queries or stored procedures, ExecuteNonQuery will function properly, but it will do nothing other than execute the query. Although using it won’t result in any errors, the Rows Affected value will be negative, or -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 affected rows returned -1 are shown in the screenshot below.
Thus concluding it, we must use ExecuteNonQuery for INSERT, UPDATE and DELETE operations only.
ExecuteScalar:
For instance, let’s say I need to determine a person’s city from their 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 value of the column returned is seen in the screenshot below.
Another scenario is when you need to obtain the value of a newly added record’s ID column value and you have an Identity (Auto Increment) column. You can use my article as a further resource.
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
Yes, you can use it, but keep in mind that it will always return the exact first cell—first row and first column—as that is how it operates.
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 shows the first cell’s value, or the first row’s and column’s, being returned.
Can we use ExecuteScalar for INSERT, UPDATE and DELETE Statements?
You can, indeed. But because INSERT, UPDATE, and DELETE Statements don’t produce any results, you won’t receive any value from the query or the rows affected like you would with ExecuteNonQuery.
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();
}
}
Since the UPDATE query returned nothing, the returned value is shown as NULL in the screenshot below.
As a result, we must use ExecuteScalar whenever we want to retrieve a single cell’s (Scalar) value.
ExecuteReader:
ExecuteReader is only used for the SELECT Operation, or fetching records from a SQL Query or Stored Procedure.
As an illustration, retrieve Name City for each record 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();
}
}
After the ExecuteReader method has been used, an object with the IDataReader Interface returns. I used SqlDataReader because we are working with SQL Server. The DataReader Read method is fetching records up until the point at which it returns true.
As a result, a while loop is run and records are retrieved one at a time.
Additionally, ExecuteReader can be used to attach a GridView control, but only if the GridView does not require the implementation of Paging. This is required because if AllowPaging is set to True and a GridView is bound using a DataReader, an exception will occur because the DataReader fetches records in ForwardOnly Mode.
