This asp .net tutorial describes about differences between ExecuteNonQuery, ExecuteScalar and ExecuteReader methods in asp .net with example and sample code. ExecuteNonQuery, ExecuteScalar and ExecuteReader are most commonly used methods of command object in ADO .Net. All these methods have one common feature: They all used to executes SQL statements but still they have some differences.
ExecuteNonQuery
ExecuteNonQuery is used to execute SQL Statement and it returns number of rows affected. We cannot use ExecuteNonQuery while we are expecting some data from the SQL query. We can use ExecuteNonQuery in INSERT, UPDATE AND DELETE queries where they are not returning any result.
ExecuteNonQuery Single Row effected example:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age)";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@Name", "Test");
cmd.Parameters.AddWithValue("@Email", "[email protected]");
cmd.Parameters.AddWithValue("@Age", 30);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int result = cmd.ExecuteNonQuery();
lblResult.Text = "Total rows effected: " + result.ToString();
con.Close();
In this case result is 1 because only single row is inserted into database.
ExecuteNonQuery Multiple Rows effected example:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
string cmdText = "DELETE FROM employee where iD IN(SELECT TOP 3 ID FROM employee)";
SqlCommand cmd = new SqlCommand(cmdText, con);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int result = cmd.ExecuteNonQuery();
lblResult.Text = "Total rows effected: " + result.ToString();
con.Close();
In this case result is 3 because 3 rows are effected in database.
ExecuteScalar
ExecuteScalar is used to execute SQL statement and it returns first column of the first row in the result returned by the query. All the other columns are ignored. We can use ExecuteScalar in INSERT, UPDATE AND DELETE queries but it is best used where we are expecting some result/value from the query.
Only difference between ExecuteNonQuery and ExecuteScalar scalar is that we use ExecuteNonQuery when when query is returning nothing and we use ExecuteScalar when query is returning some result.
ExecuteScalar example:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age); SELECT SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@Name", "Test");
cmd.Parameters.AddWithValue("@Email", "[email protected]");
cmd.Parameters.AddWithValue("@Age", 30);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int result = Convert.ToInt32(cmd.ExecuteScalar());
lblResult.Text = "Id of the newely created record is: " + result.ToString();
con.Close();
In this case result is 20 because, Primary key "Id" of the newly inserted record is 20. Here we have used "Select SCOPE_IDENTITY()" which returns primary key value of newly created record.
ExecuteDataReader
ExecuteDataReader is used to execute where we want to get some records from the database. It provides us an option to read data from database in forward-only direction. Generally ExecuteDataReader is used with SELECT queries.
ExecuteDataReader example:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
string cmdText = "SELECT * FROM employee";
SqlCommand cmd = new SqlCommand(cmdText, con);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string Name = dr["Name"].ToString();
string Email = dr["Email"].ToString();
int Age = Convert.ToInt32(dr["Age"]);
}
con.Close();
dr.Close();
Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net Demo
